PDO (PHP Data Objects) shipped with PHP 5.1 in November 2005. That's right, almost 10 years ago! (It's also available as a PECL extension for PHP 5.0 but we're all running much newer versions now, right?)

Before PDO, most of us used the standard MySQL library to work with our MySQL databases. MySQLi (MySQL improved) was released with PHP 5 in July 2004, but I never saw it adopted in the way that the original MySQL library was used, or PDO since.

So what's the difference between the three? They're all ways to work with your MySQL database, right?

MySQL

This extension was bundled with PHP 4, so it's been around for a LONG time (May 2000). It doesn't support many new features that came with MySQL 4.1+ (released in 2003) and defaults to using the libmysqlclient driver, which has poor support for non-latin character sets unless compiled specifically for that purpose.

The MySQL extension is completely procedural; that is, it has no object oriented interface. You see lots of examples around the web that look something like this:

<?php
$conn  = mysql_connect('localhost', 'username', 'password') or die('Unable to connect to server');
mysql_select_db('dbname') or die('Unable to access database');
$sql   = "SELECT * FROM user_table WHERE user_id = 1";
$query = mysql_query($sql) or die('Unable to query database');

while ($row = mysql_fetch_array($query)) {
    echo $row['user_name']."\n";
}

It's very simple, and has served to get a lot of PHP developers into using MySQL to get data from their database and into their websites. For this I'm very grateful, personally. It's not the best interface for many reasons more than the limitations I mentioned above, but it made getting into MySQL and PHP so easy, and I'm sure an entire generation (or more) of PHP devs are only where they are now because of the simple introduction that it provided.

However, the original MySQL interface is deprecated as of PHP 5.5. That means that it's earmarked for removal and that you should not depend upon it. I've seen tutorials written THIS YEAR that still advocate it. It WILL be removed in PHP 7, which is due out before Christmas 2015 so if you upgrade (and you really should, it's awesome!) then your code will completely stop working.

We'll talk about migrating in another article later (I'm building up to that!) so for now, let's move on to the next extension in the list;

MySQLi

The "MySQL improved" extension brought lots of great new features to PHP. Whereas the old MySQL extension tended to return boolean (true/false) results or resources, MySQLi is object oriented. But the nice thing for people trying to get to grips with it after moving from the original MySQL API, is that you don't HAVE to use it in an OO context; there are procedural alternatives.

But what are the differences? What do I mean when I say procedural, or object oriented? Here's a little example based on our previous MySQL sample code from above:

<?php
$conn = mysqli_connect('localhost', 'username', 'password');
if (!$conn) {
    die ('Unable to connect to server');
}
if (!mysqli_select_db($conn, 'dbname')){
    die('Unable to access database');
}

$sql   = "SELECT * FROM user_table WHERE user_id = 1";
$query = mysqli_query($conn, $sql);
if (!$query) {
    die ('Unable to query database');
}

while ($row = mysqli_fetch_array($query)) {
    echo $row['user_name']."\n";
}

So actually, apart from the fact that the way that the average tutorial handles errors has switched from an or die() (which would still work, incidentally), these tutorials would look much the same. In fact, in most cases, to migrate from the MySQL API to the MySQLi API, you pretty much just add an i after mysql in the function name. There are a couple of exceptions, like mysqli_query() which now requires that you pass in the connection as the first argument (mysql_connection() allows you to pass it in but it's an optional second argument) but otherwise it's much of the same, right?

So there's not a huge reason to NOT use the MySQLi extension, especially since it's not going anywhere any time soon and, as I mentioned earlier, the MySQL extension is going away permanently in PHP 7 later this year. Happy Christmas!

So that's the procedural style in MySQLi. Now let's do the same thing in an object oriented style and see how it differs, and see how terrifying it really is:

<?php
$conn = new mysqli('localhost', 'username', 'password');
if ($conn->connect_errno) {
    die ('Unable to connect to server');
}
if (!$conn->select_db('dbname')) {
    die('Unable to access database');
}

$sql = "SELECT * FROM user_table WHERE user_id = 1";
$query = $conn->query($sql);
if ($conn->errno) {
    die ('Unable to query database');
}

while ($row = $query->fetch_array()) {
    echo $row['user_name']."\n";
}

This is not massively different to the procedural version, so apart from having some slightly different syntax with the -> object operator it should be pretty self explanatory when you compare it to the previous example. Again, we'll tackle these concepts in a migration article later, but we need to move on to...

PDO

Now things do get different, but don't be scared!

Unlike MySQL and MySQLi, PDO is NOT a MySQL extension. Instead, it's an interface to all manner of different database engines, including (but not limited to) MySQL, SQL Server, PostgreSQL and Oracle.

The way that you use PDO is exactly the same, regardless of which database engine you need, because it is defined by an interface.

Now, it's important to note that PDO does not mean that your MySQL code can simply transfer to PostgreSQL without rewriting, but you won't need to go through all of your code looking for every mysql_query() and switch them to pg_query(). If you have had the forsight to put all of your actual SQL into a MySQL file and you can swap it out for a PostgreSQL version, you shouldn't need to change any of the actual PHP code in your application at all if you want to switch database engines.

Let's revisit our example though and see how it would translate to PDO:

<?php
try {
    $conn = new PDO('mysql:dbname;host=localhost', 'username', 'password');
    $sql  = "SELECT * FROM user_table WHERE user_id = 1";
    foreach ($conn->query($sql) as $row) {
        echo $row['user_name']."\n";
    }
} catch (PDOException $e) {
    die('Something went wrong: '.$e->getMessage());
}

Wait.... it can't be that simple, can it? My original MySQL example took 7 lines of code, this one took 9, but the actual number of things going on is very little, right? My MySQLi version took 15 lines, whether we're looking at the procedural or the OO versions. 9 lines? Really? To do the same thing with the object oriented PDO extension?

Yup.

Admittedly this is a VERY basic example, but there's nothing to be scared of here. As I mentioned in the MySQL and MySQLi examples above I'll explain more about what is occuring here when we get on to the migration articles, but maybe with the exception of the try/catch (exception pun intended) code that you may not be familiar with, the rest should be obvious.

The only real weird thing is in the PDO constructor; that's where you're creating a new PDO(). That first argument is called the Data Source Name, or DSN, and it tells PDO a little more than we provided our various MySQL extensions previously. Because PDO isn't a MySQL extension, it needs to be told which driver to load, hence why we're writing mysql in there. We specify the database name straight away (so no mysql_select_db() or similar later) and it's actually capable of taking all manner of driver appropriate instructions if you need it. You simply swap this out for an alternative for your chosen DB engine and you're connected without actually changing any code.

As I say, I'll write up some migration guides to help you guys who want to port easily from MySQL or MySQLi to PDO, and even if you don't want to port your existing code, hopefully they'll help you understand PDO so that in your next project you'll consider using PDO over the other extensions.

And as always, I'm keen to hear what you think. Am I doing a good job of explaining things to you? Is there something that you think that I could do better? Please let me know in the comments; feedback is always appreciated.