Get your FREE 30 page Developing SOLID Applications guide!

Accessing Databases with PDO: A Primer

Out Of Date Warning

Languages change. Perspectives are different. Ideas move on. This article was published on October 5, 2009 which is more than two years ago. It may be out of date. You should verify that technical information in this article is still current before relying upon it for your own purposes.

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:

<?php

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

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

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 prepared statements. This is a bit strange at first, but when you think about it, it’s not so odd. Prepared statements offer many advantages: you can prepare the SQL once and reuse it over and over again, and PDO automatically escapes content for you, meaning you reduce the risk of SQL injection. Finally, we pass the insert statement an array of values to insert into the prepared SQL, and it executes that statement. Following successful completion of all operations, we commit the transaction, or roll it back on failure.

That may seem like a lot, but it’s not. Take, for example, the typical way of doing that with MySQL’s libraries in PHP:

<?php

$con = mysql_connect('localhost', 'user', 'pass');
mysql_select_db('bank', $con);

$sql = 'SELECT SUM(amount) FROM accounts';
$resource = mysql_query($sql, $con);
$result = mysql_fetch_row($resource);
$sql = 'INSERT INTO total SET total = ' . $result[0];
mysql_query($sql, $con);

That’s all we did (with the exception of adding the transaction, because the mysql_* library doesn’t support them). That’s it! Not too hard at all, right? No; PDO is just a different way of executing SQL queries and transactions.

PDO makes it easy to switch from database to database, and the syntax for PDO is the same regardless of the database you use. PDO takes care of escaping for your particular database, as well as allows you to prepare statements, even for databases that don’t allow them. PDO isn’t without bugs, but does provide a fantastic abstraction layer that aids in code development and makes portability easier (at least when doing simpler queries that don’t rely on lots of database-specific SQL functions).

Write better object oriented PHP today.

Object oriented programming always leaves you with a headache. What if you could master it instead?

Get the book now! »

sapphirecat wrote at 10/5/2009 10:24 am:

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.

Les wrote at 10/5/2009 10:50 am:

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.

Dennis wrote at 10/5/2009 2:26 pm:

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: http://www.packtpub.com/Learning-PHP-Data-Objects-Open-Source/book

Symen (@symentimmermans) wrote at 10/7/2009 5:40 am:

Hi Brandon,

Nice post.
I recently discovered Doctrine (http://www.doctrine-project.org/), 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.