Why You Should Use PDO Instead of MySQLi

Databases, what would web applications be without them, especially when you’re working with PHP. OK, that’s not a completely fair statement to make, as there’s a wide variety of data sources available for modern applications.

There’s flat files, like CSV or text files. There’s databases, like MySQL, PostgreSQL, Oracle, Firebase, Sybase, and MS SQLServer. Then there’s the newer NoSQL data sources, including Hadoop, Redis, Cassandra, and MongoDb, amongst a range of other options. But today I want to consider the database question.

If you’ve been around PHP for some time you’ll know that right from the early days that PHP was in-effect wedded to MySQL as the default distribution came with a MySQL driver. As time progressed however this extension started showing its age and was deprecated in favour of MySQLi (i for improved).

But then PHP continued maturing at a very rapid rate and became (depending on your perspective) either fully database-aware or database-agnostic with the arrival of the exceptional PDO (PHP Data Objects) extension. So in this post, I want to look at why you should consider migrating away from MySQLi to PDO in your PHP applications.

What is PDO?

If you’re not familiar with PDO, or this is your first time hearing about it, here’s the definition from the PHP manual:

The PHP Data Objects (PDO) extension defines a lightweight, consistent interface for accessing databases in PHP. Each database driver that implements the PDO interface can expose database-specific features as regular extension functions.

PDO provides a data-access abstraction layer, which means that, regardless of which database you’re using, you use the same functions to issue queries and fetch data. PDO does not provide a database abstraction; it doesn’t rewrite SQL or emulate missing features. You should use a full-blown abstraction layer if you need that facility.

From these two paragraphs, hopefully, you can begin to see just why PDO is the future, and why, in time, the MySQLi extension, might go the way of MySQL extension. Ask yourself: Why use a vendor-specific extension, when you can use one which supports multiple vendors? Why invest the time learning all about an extension, when it only supports one vendor; when you can learn an extension which supports up to 12?

I don’t want to come across as though I’m bashing the MySQLi extension. It’s a good library which offers a lot of functionality, and is well tested. However, PDO has some features which set it apart, ones which may be well worth your while considering. Specifically, I suggest the following three:

• Vendor flexibility
• Reduced learning curve
• Named parameters

Let’s consider each of these.

Vendor Flexibility

For years the mantra of being able to change database vendors, to not be locked in to any specific one, has been touted as a key requirement for professional applications, even as a veritable silver bullet of safety. But in reality, once chosen, how often do applications change vendors? Arguably, not that often.

However, should the need arise, it’s great to know that you can, without a whole lot of hassle. PDO, more than any other extension, offers a much shorter pathway to doing change, as it supports 12 database vendors, which includes:

• PostgreSQL
• SQLite
• MySQL
• Oracle
• ODBC
• MS SQLServer & Azure
• Firebird
• Informix
• IBM DB2
• Sybase
• Cubrid
• 4D

That’s an impressive list of databases supported out of the box. This flexibility is likely even more apt if your application supports multiple vendors, right from the outset. Sure, you do have to consider the SQL that you’re writing, and that it’s portable across each database, or is written in such a way that it only takes advantage of database-specific features when it’s able to do so.

But depending on your setup, were you to want to change from one vendor to another, depending on your SQL, all that might be required is to change the DSN (data source name).

Reduced Learning Curve

Now let’s consider the reduced learning curve, when one extension will support multiple database vendors. When you, or your team, only have to learn one library, instead of several, you can achieve both proficiency and mastery so much sooner. You don’t need to learn multiple libraries. Once you’ve mastered one, you’re done.

Sure, that’s an easy thing to say in theory, but in practise it will take time. However, by reducing the educational load, you reduce the overall investment required. The only thing which you need to know, is the vendor’s SQL implementation and their query testing tools. But this is the case no matter which vendor you’re working with.

I don’t think it can be overstated how, by investing in one library, instead of several, a range of benefits become present. These include:

• Reduced time to library mastery
• Reduced maintenance requirement
• Reduced potential points of failure and bugs

This is a simplistic set of benefits, granted. But they start to hint at the reduced investment required when mastering a universal library, instead of something vendor-specific. It’s worth noting that, whilst PDO is a universal library, it’s not lightweight or lacking. It’s every bit as powerful, in many cases more so, than vendor specific libraries.

Note: PDO doesn’t do everything which MySQLi does. So if you’re considering transitioning, if you’re heavily tied to MySQL, using specific features which other database don’t offer, or PDO doesn’t readily provide, please consider the transition with care.

Named Parameters

Yes, MySQLi has placeholders, but it doesn’t have named parameters. Not sure of the difference? Consider the following example.

php
<?php
// Named parameters in PDO
$sql = 'SELECT name, colour, calories FROM fruit WHERE calories < :calories AND colour = :colour';

This example contains a query with two named parameters, :calories and :colour. When the query is internally generated, the parameters can be reused as often as desired across a range of requests. They’re clear to read and semantically easy to understand.

php
// MySQLi parameterized query
$stmt = $mysqli->prepare("'SELECT name, colour, calories FROM fruit WHERE calories < ? AND colour = ?'"));

Now let’s consider MySQLi, recasting the example using MySQLi. You can see that the query is just the same, but instead of a named parameter, we have a question mark, or placeholder. Now sure, the query’s still legible. But a question mark as a placeholder doesn’t hold as much semantic meaning.

PDO Quick Introduction

Now that we’ve looked at a series of the reasons for using PDO instead of MySQLi, let’s have a look at the basics of how to use it. Specifically, let’s see how to perform some basic querying of database records.

Let’s try and make this a semi-real world example, by using the MySQL Employees Sample Database. This provides a series of tables which model a sample company payroll, covering employees, departments, salaries and so forth.

Dependencies

You don’t need a lot to following along with the examples. Just a project directory and at least PHP 5.4 (ideally 5.6).

Making a Connection

First we need to make a connection to the database. To do that, we’ll use the code below.

php
$dbh = new \PDO(
    'mysql:host=localhost;port:33060;dbname=employees',
    'homestead', 'secret'
);

This creates a connection to my database on host localhost, using port 33060, connecting to a database called employees, using the credentials of homestead and secret. For more information, check out the PDO MySQL DSN documentation.

Select Data

With our connection established, let’s select a set of user records, filtering the users by those with a salary of between $40,000 and $50,000, who work in marketing, and were hired after the first of January, 1985. Here’s the query.

php
$sql = "SELECT e.*, s.salary
FROM employees e
INNER JOIN salaries s ON (s.emp_no = e.emp_no)
INNER JOIN dept_emp de ON (de.emp_no = e.emp_no)
INNER JOIN departments d ON (d.dept_no = de.dept_no)
WHERE (
  s.salary between :minSalary AND :maxSalary 
  AND d.dept_name = :department
  AND e.hire_date > :hireDate
)";
$sth = $dbh->prepare($sql);
$sth->execute([
    ':minSalary' => 40000,
    ':maxSalary' => 50000,
    ':department' => 'Marketing',
    ':hireDate' => '1985-01-01',
]);
$results = $sth->fetchAll();
foreach ($results as $result) {
    print_r($result);
}

Doing this will return an array of arrays, containing both an associative and scalar reference to each column in the resultset, as the example below shows.

php
Array
(
    [emp_no] => 10928
    [0] => 10928
    [birth_date] => 1961-11-01
    [1] => 1961-11-01
    [first_name] => Udaiprakash
    [2] => Udaiprakash
    [last_name] => Schmezko
    [3] => Schmezko
    [gender] => M
    [4] => M
    [hire_date] => 1991-04-07
    [5] => 1991-04-07
    [salary] => 40531
    [6] => 40531
)

What about making it simpler, by hydrating a simple value object per record in the resultset? To do that, first we create a value object to model a row, as below:

php
class employee {
    public $emp_no;
    public $birth_date;
    public $first_name;
    public $last_name;
    public $gender;
    public $hire_date;
    public $salary;
}

Then, to use the object, we pass two arguments to the fetchAll() method, as below, PDO::FETCH_CLASS, which tells it to hydrate an object, and the name of the class to hydrate.

php
$results = $sth->fetchAll(PDO::FETCH_CLASS, "employee");

Doing so, the results will now be hydrated objects, like the following example:

php
employee Object
(
    [emp_no] => 10928
    [birth_date] => 1961-11-01
    [first_name] => Udaiprakash
    [last_name] => Schmezko
    [gender] => M
    [hire_date] => 1991-04-07
    [salary] => 40866

I’d look at creating, updating, and deleting, but the functionality is largely the same as what we’ve seen so far; so we’ll end the examples here.

 Future of MySQLi

To the best of my knowledge, MySQLi isn’t going away anytime soon, and I’d hate to suggest that it is or should. It’s a well written and well tested library, which has a definite place in the PHP ecosystem. Additionally, as I’m not one of the maintainers, I can’t speak for them, nor make any pronouncements on their behalf.

What I can say though, is that, from my own perspective, I’d encourage you to become familiar with PDO, if you’re not already, in addition to what you’ve read here. There are several very compelling arguments for using it, instead of a database vendor-specific extension.

Wrapping Up

And that’s both an introduction to PDO, as well as three good reasons why you should consider using it, over a vendor specific library, such as MySQLi, for interacting with databases. Whilst, like any library of sufficient complexity, there’s a lot to digest and learn with PDO, the investment will be worth it; especially as PDO is the foundation for so many of the newer database-related libraries available in the PHP ecosystem.