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:

<?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:

Brandon Savage is the author of Mastering Object Oriented PHP and Practical Design Patterns in PHP

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.

Copyright © 2023 by Brandon Savage. All rights reserved.