Automatically connect to PDO only when necessary

Question

I have a section of code that, depending on the requested URL, will contain one of fourteen other files. Some of these fourteen files require a connection to one of three different databases, and additional files can be added at any time.

I do not want to open default PDO connections for all three databases, as this is a waste of resources and will slow down runtime. So my thought is to wrap all SQL queries inside a function. The first time the request is executed on an insecure PDO connection, the try {} error handler can catch it, find out what the problem is (in this case the connection does not exist), then open the connection and run the request again. Thus, the database is connected only as necessary, if only the connection string (host, database, username, password) is defined in advance, I do not see any problems with its operation.

However, I need to continue with this and not have access to the dev block for about 7 days, so can anyone see any problems with this script? Also, can someone tell me the error message that the handler-> errorInfo () will return if the connection does not open?

Answer 1

This is the right idea, but not its best implementation.

SQL operations wrapper is good. But why don't you do this:

class Wrapper { private static $db; public static function someQuery() { $db = self::getDatabase(); // now go on to execute the query } private static function getDatabase() { if (self::$db === null) { self::$db = // connect here } return self::$db; } } 

This has many advantages:

• Allows you to logically group SQL operations into one (or several!) Classes
• Does not connect to the database if not required
• It does not depend on the correct operation of (fragile) errors.

In your particular case, you should probably go with 3 separate Wrapper classes. Including just one class is doable (three different $db variables), but probably more confusing than it costs.

Answer 2

Use this class exactly as you would use the PDO class.

 class DB extends PDO { protected $_config = array(); protected $_connected = false; public function __construct($dsn, $user = null, $pass = null, $options = null) { //Save connection details for later $this->_config = array( 'dsn' => $dsn, 'user' => $user, 'pass' => $pass, 'options' => $options ); } public function checkConnection() { if (!$this->_connected) { extract($this->_config); parent::__construct($dsn, $user, $pass, $options) $this->_connected = true; } } public function query($query) { $this->checkConnection(); return parent::query($query); } public function exec($query) { $this->checkConnection(); return parent::exec($query); } //etc. } 

Answer 3

I used a different method using the __call magic method, so you do not need to create separate wrappers for each method.

 class PDOLazyConnector { private $dsn; private $username; private $password; private $driver_options; private $dbh; public function __construct ($dsn, $username, $password, $driver_options = array ()) { $this->dsn = $dsn; $this->username = $username; $this->password = $password; $this->driver_options = $driver_options; } public function __call ($function, $args) { // connect to db (first time only) $this->__init_dbh (); // invoke the original method return call_user_func_array (array($this->dbh, $function), $args); } public function __get ($property) { return $this->dbh->$property; } private function __init_dbh () { // If db handler is not open yet, do it now if (empty ($this->dbh)) { $this->dbh = new PDO ($this->dsn, $this->username, $this->password, $this->driver_options); } } } 

You just need to replace your PDO instance with PDOLazyConnector, therefore:

 $dbh = new PDO($dsn, $user, $password, $driver_options); 

with:

 $dbh = new PDOLazyConnector($dsn, $user, $password, $driver_options); 

Answer 4

PDO has an option for persistent PDO :: ATTR_PERSISTENT connections .

See comments at http://php.net/manual/en/book.pdo.php

Answer 5

 function &get_pdo() { static $_PDO = null; if ($_PDO === null) { $_PDO = new PDO('your DSN', 'username', 'password'); } return $_PDO; } 

Then you just do

 $stmt = get_pdo()->prepare('...'); 

You can do the same by extending the PDO class and adding a static singleton function to it. I find this approach simpler. It also gives you the ability to call it from anywhere on the stack without having to bind your connection with arguments (which may be good or bad, depending on the situation).