PHP Database Access Accessing a Database Connection Anywhere in Your Program

PHP Database Access

Accessing a Database Connection Anywhere in Your Program

Problem

You’ve got a program with lots of functions and classes in it, and you want to maintain a single database connection that’s easily accessible from anywhere in the program.

Solution

Example  Creating a database connection in a static class method

        class DBCxn {

               // What DSN to connect to?

               public static $dsn = 'sqlite:c:/data/zodiac.db';

               public static $user = null;

               public static $pass = null;

               public static $driverOpts = null;

               // Internal variable to hold the connection

               private static $db;

               // No cloning or instantiating allowed

               final private function __construct() { }

               final private function __clone() { }

               public static function get() {

                      // Connect if not already connected

                      if (is_null(self::$db)) {

                            self::$db = new PDO(self::$dsn, self::$user, self::$pass,

                                                                    self::$driverOpts);

                      }

                      // Return the connection

                      return self::$db;

               }

        }

Discussion

The DBCxn::get() method defined in Example 10-40 accomplishes two things: you can call it from anywhere in your program without worrying about variable scope and it prevents more than one connection from being created in a program.

To change what kind of connection DBCxn::get() provides, alter the $dsn, $user, $pass, and $driverOpts properties of the class. If you need to manage multiple different database connections during the same script execution, change $dsn and $db to an array and have get() accept an argument identifying which connection to use. 

Example  Handling connections to multiple databases

        class DBCxn {

               // What DSNs to connect to?

               public static $dsn =

                      array('zodiac' => 'sqlite:c:/data/zodiac.db',

                                 'users' => array('mysql:host=db.example.com','monty','7f2iuh'),

                                 'stats' => array('oci:statistics', 'statsuser','statspass'));

               // Internal variable to hold the connection

               private static $db = array();

               // No cloning or instantiating allowed

               final private function __construct() { }

               final private function __clone() { }

               public static function get($key) {

                      if (! isset(self::$dsn[$key])) {

                           throw new Exception("Unknown DSN: $key");

                      }

                      // Connect if not already connected

                      if (! isset(self::$db[$key])) {

                           if (is_array(self::$dsn[$key])) {

                                $c = new ReflectionClass('PDO');

                                self::$db[$key] = $c->newInstanceArgs(self::$dsn[$key]);

                           } else {

                                self::$db[$key] = new PDO(self::$dsn[$key]);

                           }

                      }

                      // Return the connection

                      return self::$db[$key];

               }

        }

In Example, you must pass a key to DBCxn::get() that identifies which entry in $dsn to use. The code inside get() is a little more complicated, too, because it has to handle variable numbers of arguments to the PDO constructor. Some databases, such as SQLite, just need one argument. Others may provide two, three, or four arguments.Example uses the ReflectionClass::newInstanceArgs() method to concisely call a constructor and provide arguments in an array.