My PDO (PHP Database Object) Singleton Class

To start with, we need to instantiate the class of course.  This only needs to be done once in the application.  I generally define some constant variables that hold the database connection details in a config file, and then either in that same file or in an init script I create the object.  I also take the same opportunity to get hold of the object handle.

Maybe I ought to back up and explain a little bit here first.  There are two static functions that will be commonly used, depending on whether you want to use the functions in this class or the PDO functions.  obtain() will give you the PDO handle, as if you had just created a PDO object; pass() will give you the pdos object so you can use the functions in this class.

Now, on to the instantiation. While doing this, we’re going to grab a handle to both PDO and the pdos object.

//Fill in database connection details here
define('DB_HOST','localhost');
define('DB_NAME','database_table');
define('DB_PASS','XGb78dfhnasfDJK89');
define('DB_USER','database_user');

$dbh = pdos::obtain(DB_HOST, DB_USER, DB_PASS, DB_NAME);

$db = pdos::pass();

Wasn’t that easy?  Let’s make use of it!

You may have noticed that when I created the PDO object in the constructor I set the attributes PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION; this means that all MySQL errors will be thrown as exceptions.  I prefer to handle all errors in this way; although please note that at this point in time there isn’t much built-in error checking in this class.  I do intent to rectify that in the future.  It just means that we need to wrap everything in a try/catch statement.

So let’s say we are writing a basic function to do a database select query.  Here is an example using the PDO handle:

function getUser($username) {
     try {
          $dbh = pdos::obtain();
          $sth = $dbh->prepare("SELECT * FROM users WHERE username=:username LIMIT 1");
          $sth->bindValue(":username", $username");
          $sth->execute();
          $res = $sth->fetch(PDO::FETCH_ASSOC);
          $sth->closeCursor();
     } catch (PDOException $e) {
          echo ($e->getMessage(), __LINE__);
          return false;
     }
     return $res;
}

Very straight-forward.  Really the singleton class has given you two benefits here:

  1. There was no need to use a global variable to pass your database connection info.  If you are like me, you despise the use of globals.
  2. You have only used a single database connection.

Obviously with pdos::obtain() you can use any PDO function; the object handle that it returns is absolutely no different than any other PDO handle.  As such, I’m not going to get into any further detail on that here.  As I said before, there are plenty of other tutorials and writings on that.

Let’s jump into my custom functions.  They seem pretty long, and that’s because I have focused on making the usage of them simple.  The goal has been the ability to feed into them only the essential data needed to accomplish what you need done.  I don’t care to spend my time writing queries that are essentially the same over and over and over again, replacing only the “where” clause for each one.  So these functions were designed to do that for you.

First we have the insert() function.  It should be pretty self-explanatory, the function is to insert a new row.  The required parameters are the table name and an associative array containing the column name and the value for that value.  The return value is the auto-increment value (if it exists) of the new row.

$table = "users";
$data = array("username" => "Joe", "email" => "joe@domain.com", "active" => true);
$id = $db->insert($table, $data);

Next is update(), which is very similar to insert() except that it includes the “where” array parameter.

$table = "users";
$data = array("active" = false);
$where = array("username" = "Joe");
$id = $db->update($table, $data, $where);

After that we get to the more complex functions, starting with query().  Like the previous two, the first parameter is the table.  Second is the columns that are being returned (can be either a single column as a string, or an array of multiple columns.)  The next three optional parameters are the “where” array, the “like” array, and a “misc” string.  The “misc” string contains those things such as ORDER BY, GROUP BY, LIMIT, etc.

Note that since the query is built using string functions, you can get quite complex in the building of these parameters.  I have made some rather complex queries joining multiple tables; it’s just a matter of being very careful in placing the right column and variable name in the right place in the right parameter.

$col = "users AS u ";
$where = array("u.active" => true);
$like = array("u.username" => "%oe%"); //need to include wildcard here
$misc = "ORDER BY u.datetime DESC";
$result = $db->query($col, "u.*", $where, $like, $misc);

After query() is the select_single() function.  This is very similar to query() except that you use it when you only expect a single row to be returned, so it is a little bit faster in processing.  All of the parameters are the same, the only difference is that instead of returning a result array, you get the row array back:

$col = "users AS u ";
$where = array("u.username" => "Joe", "u.active" => true);
$misc = "ORDER BY u.datetime DESC";
$row = $db->query($col, "u.*", $where, $like=false, $misc);

And finally we have delete().  All this one takes is the table name and the where array: 

$table = "users";
$where = array("username" = "Joe");
$ret = $db->delete($table, $where);

So that’s it.  Hopefully it all is fairly straight-forward.  But most of all, I hope it helps make your application development just a little bit easier!  I’ve certainly been using this lately, and it’s made a big difference for me!

Cody (17 Posts)