Setting up a DB Class With PDO

I have had the opportunity to use PDO to connect to a MySQL DB at work recently. One requirement of the project was to provide a once only static (singleton) connection class that would be available to all classes. Also, I had created some stored procedures in the DB – I knew that this may present some interesting challenges!

First of all I created the class itself.

And here it is…

/**
* Class db_pdo
*
* This class is designed to provide a singleton connection to
* a database using the PDO data access layer provided in PHP5
*
*/

Class db_pdo {

private static $dsn;
private static $user;
private static $pass;
private static $instance;

/**
* Class constructor
*
*/
private function __construct() {
/** empty */
}

In the first part of the class I declare a few properties including the static property $instance which will hold the instance of the class. They are all declared private, as I do not want any access to them from the outside. The interest here lies in the constructor – it is declared private and contains nothing. This also means that it cannot be called from the outside, hence the class cannot be used directly.

/**
* loadConnectionParams
*
* This method sets up the db connection parameters
*
* @param void
* @return void
*
*/
private static function loadConnectionParams() {

self::$dsn      = 'mysql:host=localhost;dbname=MyDB;port=3306';
self::$user     = 'user';
self::$pass     = 'password';

}

This part is rather simple really and some may deem it an needless step, but if for instance you want to pass connection parameters to this class as constants and keep a bit of seperation… well, whatever!

/**
* getInstance
*
* This method returns DB instance or creates an intitial connection
*
* @param void
* @return self::$instance  static instance of db connection
*
*/
public static function getInstance() {

if (!self::$instance) {

self::loadConnectionParams();
self::$instance = new PDO(self::$dsn, self::$user, self::$pass);
self::$instance->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

}

return self::$instance;

}

This method provides the connection to the DB. It first checks to see if an instance of itself does not already exist and loads the connection parameters using the loadConnectionParams() method. It then sets about loading an instance of the PDO connection with the new operator, passing along those connection parameters.

/**
* unsetInstance
*
* This method unsets the DB instance
*
* @param void
* @return void
*
*/
public static function unsetInstance() {
self::$instance = null;
}

}

The final method in this class is an important one – the unsetting of the connection instance once work with it is completed. Closing the db connection when at the end of DB usage is vital for good housekeeping, saving memory resources and a must when using stored procedures (especially when you want to avoid those strange errors).