How to connect to MySQL using PDO using PHP function

Question

I'm using this method to connect to my MySQL db to SELECT, INSERT, UPDATE and DELETE data. This is the cnn() function:

function cnn() {
    static $pdo;
    if(!isset($pdo)) {
        $settings = [
            PDO::ATTR_TIMEOUT => 30,
            PDO::ATTR_PERSISTENT => false,
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
        ];
        try {
            # settings
            $config['db']['host'] = 'example.com';
            $config['db']['name'] = 'db';
            $config['db']['user'] = 'username';
            $config['db']['pass'] = '****************';
            $pdo = new PDO('mysql:host='.$config['db']['host'].';dbname='.$config['db']['name'], $config['db']['user'], $config['db']['pass'], $settings);
            return $pdo;
        } catch(PDOException $e) {
            http_response_code(503);
            echo $e->getCode().': '.$e->getMessage();
        }
    } else {
        return $pdo;
    }
}

And then i can just do this to re-use the same pdo object each time i need it on the same request.

1st query

$sql = 'INSERT INTO user (name, lastname) VALUES (:name, :lastname)';
$stmt = cnn()->prepare($sql);
$stmt->bindValue(':name', "John", PDO::PARAM_STR);
$stmt->bindValue(':name', "Wayne", PDO::PARAM_STR);
$stmt->execute();

2nd query

$sql = 'SELECT * FROM user WHERE id_user = :id_user';
$stmt = cnn()->prepare($sql);
$stmt->bindValue(':id_user', 4641, PDO::PARAM_INT);
$stmt->execute();
$user = $stmt->fetch();

I was wondering if there could be any performance issues by using this approach. Thanks.

Answer 1

1)

As referenced by HTMHell, your $config array is local here and I believe this should be deleted after use so password variables are not leakable on a later get_defined_vars() call, or similar cross reference.

2)

It seems far more logical to me to put your function in a class. Run the cnn() function from the __construct. There are a lot of positive aspects to wrapping this function in a full class.

3)

Do NOT set any variable to being static unless you have a specific requirement in the context of putting your cnn() function into a Class.

4)

Correctly use your try{ ... } catch { ... } blocks, do not wrap other code in the try block except code that throws exceptions.

    // settings
    $config['db']['host'] = 'example.com';
    $config['db']['name'] = 'db';
    $config['db']['user'] = 'username';
    $config['db']['pass'] = '****************';
    try {
        $pdo = new PDO('mysql:host='.$config['db']['host'].';dbname='.$config['db']['name'], $config['db']['user'], $config['db']['pass'], $settings);
    } catch(PDOException $e) {
        /***
         * Do you really need this, here?  
         * //http_response_code(503);
         **/
        echo $e->getCode().': '.$e->getMessage();
    }
    return $pdo;

5)

DO NOT EVER OUTPUT ERRORS DIRECTLY TO THE SCREEN

Errors should be logged so only server folks and developers rather than the general public can see them. This also means you have a log of errors rather than a single, temporary instance of any that occur.

echo $e->getCode().': '.$e->getMessage(); Should be:

error_log($e->getCode().': '.$e->getMessage());

Or similar. Stay consistent; always return a PDO object and not echo outputs.

6)

Remove your final else wrapper it is merely clutter.

if(!isset($pdo)) {
     ...
} 
return $pdo; 

Also remove the return $pdo; inside your try { ... } block. Don't Repeat Yourself.

7)

Properly document your code:

/***
 * For generating or asserting a PDO Database Object.
 * @return Returns the PDO object 
 ***/
 function cnn() {

---

To answer the question you actually asked:

I was wondering if there could be any performance issues by using this approach.

This question is far too broad and can be solved by yourself using timer testings or other methods (detailed in the link) to establish on your system if it is particularly efficient or inefficient, or what SQL you choose to give it...