Reusing the database connection object

Question

I use this function to connect to my MySQL db when necessary, and also to reuse the same connection object for any subsequent request that I may need, in the same php script.

function cnn() { static $pdo; if(!isset($pdo)) { try { $pdo = new PDO('mysql:host='.DB_HOST.';dbname='.DB_NAME, DB_USER, DB_PASS); $pdo->setAttribute(PDO::ATTR_TIMEOUT, 30); $pdo->setAttribute(PDO::ATTR_PERSISTENT, true); $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ); return $pdo; } catch(PDOException $e) { http_response_code(503); echo $e->getCode.': '.$e->getMessage(); die(); //or whatever error handler you use } } else { return $pdo; } } 

First request (object created)

 echo cnn()->query('SELECT firstname FROM user WHERE id=4;')->fetch(PDO::FETCH_COLUMN) 

Second request (object reused)

 echo cnn()->query('SELECT title FROM news WHERE id=516;')->fetch(PDO::FETCH_COLUMN) 

Do you agree with this approach? Do you think it can be optimized? Thank you for your opinion.

Answer 1

I agree with this method, although many people will tell you that this singleton approach is bad, bad.

However, I do not agree with your implementation. It should be:

 function cnn() { static $pdo; if(!$pdo) { $conf = array(PDO::ATTR_TIMEOUT => 30, PDO::ATTR_PERSISTENT => true, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ, ); $dsn = 'mysql:host='.DB_HOST.';dbname='.DB_NAME; $pdo = new PDO($dsn, DB_USER, DB_PASS, $conf); } return $pdo; } 

In addition, it seems reasonable to move the handler code to the handler (and , of course, without an unconditional echo error!)

 function my_exceptionHandler($exception) { http_response_code(503); if (ini_get('display_errors')) { echo $e->getMessage().$e->getTrace(); } else { log_error($e->getMessage().$e->getTrace()); } die(); //or whatever error handler you use } set_exception_handler("my_exceptionHandler"); 

In addition, I would expand it to accept a parameter to make several connections possible.