in Best Practices, PHP 5, System Architecture

Accessing Databases with PDO: A Primer

With the introduction of PHP 5, the PHP Data Object was introduced as core functionality. PHP 5.1 turned on a minimum level of support for SQLite, by default, and PDO supports most of the major database engines. PDO offers a number of enhancements and improvements over the various database libraries (e.g. mysql_*, mysqli_*, pg_*), the biggest one being consistency. Still, the high level of code that involves direct use of the various database libraries means that PDO still isn’t as widely accepted as it should be.

This primer will show the various uses of PDO, and outline some of the benefits.

The following is a sample PDO transaction with a MySQL database:


$con = new PDO('mysql:host=localhost;dbname=bank', 'user', 'pass');

try {
    $stmt = $con->query('SELECT SUM(amount) FROM accounts');
    $result = $stmt->fetch(PDO::);
    $insert = $con->prepare('INSERT INTO total SET total = ?');
} catch (PDOException $e) {

Before going too far, with the exception of the very first line (the new PDO statement), this is the exact same syntax for PostgreSQL, SQLite, etc. Exactly the same. Why? Because PDO makes it easy to port from one database to another without too much headache. Now, let’s look at what we’ve done.

First, we create a PDO connection. This is pretty standard stuff. We use a DSN, which you can read about for the various database drivers. The next thing we do is we initiate a transaction – a boundary that makes all of our changes happen, or none of our changes happen. In our transaction, we’re not doing anything exciting, but if we were working on three or four dependent tables, we’d want to roll back and not have our changes applied if one table failed to work for some reason.

The next thing we do is we get back a PDO statement object. The statement object contains the information about the query we just executed. We get the result out of the statement object, and proceed to

  1. I used PDO in the 5.1.x series for a former employer, and I would rank it as one of the biggest technical mistakes I ever made. It had a tendency to segfault if you tried to subclass it, or if you didn’t close the statements before the connection. This last one was extra fun because PHP would apparently free objects at request shutdown in the order they were created, guaranteeing a crash if any statements were still open.

    I made it work then, with delegates and careful use of closeCursor(), but I would never do it again. Life’s too short for rushing headlong into bugs.

  2. Was looking forward to using PDO in the early days but it never did catch on for me.

    Rolled my own and nothing wrong with that, just as secure in my opinion.

  3. It’s not quite right to state that mysql_ functions do not support transactions; you can always do mysql_query(“BEGIN”) and mysql_query(“COMMIT”) or mysql_query(“ROLLBACK”);

    Also, it should be noted, that fro non-supporting engines, the transaction statements will be ignored.

    There is a book published on PDO:

  4. Hi Brandon,

    Nice post.
    I recently discovered Doctrine (, which is an object relational mapper (ORM) for PHP 5.2.3+ that sits on top of a powerful database abstraction layer (DBAL), and it looks promising. Your post reminded me of it. You should check it out.

Comments are closed.