Chapter 8: PDO - the Right Way to Access Databases in PHP

by Parham Doustdar

PDO is the acronym of PHP Data Objects. As the name implies, this extension gives you the ability to interact with your database through objects.

Why not mysql and mysqli?

The very valid question people ask when confronted by a new technology is simply, why should they upgrade? What does this new technology give them that is worth the effort of going through their entire application and converting everything to this new library, extension, or whatever?

It’s a very valid concern. We’ve written about this to some degree before, but let’s go through why we think it’s worth it to upgrade.

PDO is object-oriented

Let’s face it: PHP is rapidly growing, and it is moving toward becoming a better programming language. Usually, when this happens in a dynamic language, the language increases its strictness in order to allow programmers to write enterprise applications with peace of mind.

In case of PHP, better PHP means object-oriented PHP. This means the more you get to use objects, the better you can test your code, write reusable components, and, usually, increase your salary.

Using PDO is the first step in making the database layer of your application object-oriented and reusable. As you will see in the rest of this chapter, writing object-oriented code with PDO is much simpler than you may think.

Abstraction

Imagine that you have written a killer application using MySQL at your current workplace. All of a sudden, someone up the chain decides that you must migrate your application to use Postgres. What are you going to do?

You have to do a lot of messy replaces, like converting mysql_connect or mysqli_connect to pg_connect, not to mention all the other functions you used for running queries and fetching results. If you were using PDO, it would be very simple. Just a few parameters in the main configuration file would need changing, and you’d be done.

It allows parameter binding

Parameter binding is a feature that allows you to replace placeholders in your query with the value of a variable. It means:

• You don’t have to know, at runtime, how many placeholders you will have.
• Your application will be much safer against SQL injection.

You can fetch data into objects

People who have used ORMs like Doctrine know the value of being able to represent data in your tables with objects. If you would like to have this feature, but don’t want to learn an ORM, or don’t want to integrate it into an already existing application, PDO will allow you to fetch the data in your table into an object.

The mysql extension is no longer supported!

Yes, the mysql extension is finally removed from PHP 7. That means if you’re going to use PHP 7, you need to change all those functions to mysqli_* instead of mysql_*. This is a great time to just upgrade straight to PDO because of how much it helps you in writing maintainable, portable code with much less effort.

I hope the reasons above have convinced you to start integrating PDO into your application. Don’t worry about setting it up; you may already have it on your system!

Verifying the Existence of PDO

If you are using PHP 5.5.X and above, chances are that your installation already includes PDO. To verify, simply open the terminal on Linux and Mac OS X, or the command prompt on Windows, and type the following command:

<php
phpinfo();

You can also create a php file under your webroot, and insert a phpinfo() statement inside it:

php -i | grep 'pdo'

Now, open this page in your browser and search for the pdo string.

If you have PDO and MySQL, skip the installation instructions. If you have PDO but don’t have it for MySQL, you merely need to install the mysqlnd extension per the instructions below. However, if you don’t have PDO at all, your path is longer, but not harder! Keep on reading and we’ll tell you how to gear up by installing PDO and mysqlnd!

Installation of PDO

If you have already installed PHP from a repository through a package manager (e.g. apt, yum, pacman, and so on), installing PDO is very simple and straightforward; just run the installation command that is listed under your respective operating system and distribution below. If you haven’t, though, I have also included my recommended methods for starting from scratch.

Fedora, RedHat and CentOS

First, if you don’t have it already, add the Remi repository using the instructions provided on their blog. When that’s done, you can easily install php-pdo using the following command:

sudo yum --enablerepo=remi,remi-php56 install php-pdo

Of course, if you don’t have it already, you also need to install the mysqlnd extension using the following command:

sudo yum --enablerepo=remi,remi-php56 install php-mysqlnd

Debian and Ubuntu

On Ubuntu, you need to add the Ondrej repository. This link points to the PPA for 5.6, but you can find links to previous versions there as well.

On Debian, you should add the Dotdeb repository to your system.

On both of these distributions, once you’ve installed the php5 metapackage, you already have PDO ready and configured. All you need to do is to simply install the mysqlnd extension:

sudo apt-get install php5-mysqlnd

Windows

You should try and use a Linux virtual machine for development on Windows, but in case you’re not up for it, follow the instructions below.

On Windows, you usually get the full lamp stack using Wamp or Xampp. You can also just download PHP straight from windows.php.net. Obviously, if you do the latter, you will only have PHP, and not the whole stack.

In either case, if PDO isn’t already activated, you just need to uncomment it in your php.ini. Use the facilities provided in your lamp stack to edit php.ini, or in case of having downloaded PHP from windows.php.net, just open the folder you chose as your installation directory and edit php.ini. Once you do, uncomment this line:

;extension=php_pdo_mysql.dll

Beginning with PDO: a High-level Overview

When querying your database using PDO, your workflow doesn’t change much. However, there are a few habits you must learn to drop, and others that you have to learn. Below are the steps you need to perform in your application to use PDO. We will explain each one in more detail below.

• Connecting to your database
• Optionally, preparing a statement and binding parameters
• Executing the query

Connecting to your database

To connect to your database, you need to Instantiate a new PDO object and pass it a data source name, also known as a DSNIn general, a DSN consists of the PDO driver name, followed by a colon, followed by the PDO driver-specific connection syntax. Further information is available from http://php.net/manual/en/pdo.drivers.php.

For example, here is how you can connect to a MySQL database:

$connection = new PDO('mysql:host=localhost;dbname=mydb;charset=utf8', 'root', 'root');

The function above contains the DSN, the username, and the password. As quoted above, the DSN contains the driver name (mysql), and driver-specific options. For mysql, those options are host (in the ip:port format), dbname, and charset.

Queries

Contrary to how mysql_query() and mysqli_query() work, there are two kinds of queries in PDO: ones that return a result (e.g. select and show), and ones that don’t (e.g. insert, delete, and so on). Let’s look at the simpler option first: the ones that don’t.

Executing queries

These queries are very simple to run. Let’s look at an insert.

$connection->exec('INSERT INTO users VALUES (1, "somevalue"');

Technically, I lied when I said that these queries don’t return a result. If you change the code above to the following code, you will see that exec() returns the number of affected rows:

$affectedRows = $connection->exec('INSERT INTO users VALUES (1, "somevalue"');
echo $affectedRows;

As you can probably guess, for insert statements, this value is usually one. For other statements though, this number varies.

Fetching Query Results

With mysql_query() or mysqli_query(), here is how you would run a query:

$result = mysql_query('SELECT * FROM users');

while($row = mysql_fetch_assoc($result)) {
    echo $row['id'] . ' ' . $row['name'];
}

With PDO, however, things are much more intuitive:

foreach($connection->query('SELECT * FROM users') as $row) {
    echo $row['id'] . ' ' . $row['name'];
}

Fetching Modes: Assoc, Num, Obj and class

Just as with the mysql and mysqli extensions, you can fetch the results in different ways in PDO. To do this, you must pass in one of the PDO::fetch_* constants, explained in the help page for the fetch function. If you want to get all of your results at once, you can use the fetchAll function.

Below are a few of what we think are the most useful fetch modes.

• PDO::FETCH_ASSOC: returns an array indexed by column name. That is, in our previous example, you need to use $row['id'] to get the id.
• PDO::FETCH_NUM: returns an array indexed by column number. In our previous example, we’d get the id column by using $row[0] because it’s the first column.
• PDO::FETCH_OBJ: returns an anonymous object with property names that correspond to the column names returned in your result set. For example, $row->id would hold the value of the id column.
• PDO::FETCH_CLASS: returns a new instance of the requested class, mapping the columns of the result set to named properties in the class. If fetch_style includes PDO::FETCH_CLASSTYPE (e.g. PDO::FETCH_CLASS | PDO::FETCH_CLASSTYPE) then the name of the class is determined from a value of the first column. If you remember, we noted that PDO, at its simplest form, can map column names into classes that you define. This constant is what you would use to do that.

As an example, let’s get our rows as associative arrays:

$statement = $connection->query('SELECT * FROM users');

while($row = $statement->fetch(PDO::FETCH_ASSOC)) {
    echo $row['id'] . ' ' . $row['name'];
}

As you might remember, above, when we were listing the advantages of PDO, we mentioned that there’s a way to make PDO store the current row in a class you have previously defined. You have probably also seen the PDO::FETCH_CLASS constant explained above. Now, let’s use it to retrieve the data from our database into instances of a User class. Here is our User class:

class User
{

    protected $id;
    protected $name;

    public function getId()
    {
        return $this->id;
    }

    public function setId($id)
    {
        $this->id = $id;
    }

    public function getName()
    {
        return $this->name;
    }

    public function setName($name)
    {
        $this->name = $name;
    }

}

Now, we can make the same query again, this time using our User class, which is, in these cases, also known as Model, Entity, or a plain old PHP object (taken from Plain Old Java Object in the world of Java).

$statement = $connection->query('SELECT * FROM users');

while($row = $statement->fetch(PDO::FETCH_CLASS, 'User')) {
    echo $row->getId() . ' ' . $row->getName();
}

Prepared Statements and Binding Parameters

To understand parameter binding and its benefits, we must first look more deeply into how PDO works. When we called $statement->query() above, PDO internally prepared a statement, and executed it, returning the resulting statement to us.

When you call $connection->prepare(), you are creating a prepared statement. Prepared statements are a feature of some database management systems that allow them to receive a query like a template, compile it, and execute it when they receive the value of placeholders - think of them as rendering your Blade or Twig templates.

When you later on call $statement->execute(), you are passing in the values for those placeholders, and telling the database management system to actually run the query. It’s like calling the render() function of your templating engine.

To see this in action, let’s create a query that returns the specified id from the database:

$statement = $connection->prepare('Select * From users Where id = :id');

$statement = $connection->prepare('Select * From users Where id = :id');

The above PHP code sends the statement, including the :id placeholder, to the database management system. The database management system parses and compiles that query, and based on your configuration, may even cache it for a performance boost in the future. Now, you can pass in the parameter to your database engine and tell it to execute your query:

$id = 5;
$statement->execute([
    ':id' => $id
]);

Then, you can fetch the result from the statement:

$results = $statement->fetchAll(PDO::FETCH_OBJ);

The Benefits of Parameter Binding

Now that you are more familiar with how prepared statements work, you can probably guess at their benefits.

PDO has taken the task of escaping and quoting the input values you receive from the user out of your hands. For example, now you don’t have to write code like this:

$results = mysql_query(sprintf("SELECT * FROM users WHERE name='%s'", 
        mysql_real_escape_string($name)
    )
) or die(mysql_error());

Instead, you can say:

$statement = $connection->prepare('Select * FROM users WHERE name = :name');
$results = $connection->execute([
    ':name' => $name
]);

If that isn’t short enough for you, you can even make it shorter, by providing parameters that are not named - meaning that they are just numbered placeholders, rather than acting like named variables:

$statement = $connection->prepare('SELECT * FROM users WHERE name = ?');
$results = $connection->execute([$name]);

Likewise, having a prepared statement means that you get a performance boost when running a query multiple times. Let’s say that we want to retrieve a list of five random people from our users table:

$numberOfUsers = $connection->query('SELECT COUNT(*) FROM users')->fetchColumn();
$users = [];
$statement = $connection->prepare('SELECT * FROM users WHERE id = ? LIMIT 1');

for ($i = 1; $i <= 5; $i++) {
    $id = rand(1, $numberOfUsers);
    $users[] = $statement->execute([$id])->fetch(PDO::FETCH_OBJ);
}

When we first call the prepare function, we tell our DBMS to parse, compile and cache our query. Later on in our for loop, we only send it the values for the placeholder - nothing more. This allows the query to run and return quicker, effectively decreasing the time our application would need in order to retrieve the results from the database.

You also might have noticed that I have used a new function in the piece of code above: fetchColumn. As you can probably guess, it returns the value of one column only, and is good for getting scalar values from your query result, such as count, sum, min, max, and other functions which return only one column as their result.

Binding Values to an IN Clause

Something that has a lot of people stumped when they first start to learn about PDO is the IN clause. For example, imagine that we allow the user to enter a comma-separated list of names that we store in $names. So far, our code is:

$names = explode(',', $names);

What most people do at this point is the following:

$statement = $connection->prepare('SELECT * FROM users WHERE name IN (:names)');
$statement->execute([':names' => $names]);

This doesn’t work - you can only pass in a scalar value (like integer, string, and so on) to prepared statements! The way to do this is - you guessed it - to construct the string yourself.

$names = explode(',', $names);
$placeholder = implode(',', array_fill(0, count($names), '?'));

$statement = $connection->prepare("SELECT * FROM users WHERE name IN ($placeholder)");
$statement->execute([$names]);

Despite its scary appearance, line 2 is simply creating an array of question marks that has as many elements as our names array. It then concatenates the elements inside that array and places a , between them - effectively creating something like ?,?,?,?. Since our names array is also an array, passing it to execute() works as expected - the first element is bound to the first question mark, the second is bound to the second question mark, and so on.

Providing Datatypes When Binding Parameters

The techniques we showed above for binding values to parameters are good when you are just starting out to learn PDO, but it’s always better to specify the type of every parameter you bind. Why?

• Readability: for someone reading your code, it’s easy to see what type a variable must be in order to be bound to a parameter
• Maintainability: knowing that the first placeholder in your query must be an integer allows you to catch any errors that slip out. For example, if someone passes a variable containing test to your function which will then use that value to search for a particular id as an integer, having a datatype allows you to quickly find the error.
• Speed: when you specify the datatype of the variable, you are telling your database management system that there’s no need to cast the variable and that you’re providing it the correct type. In this way, you don’t have the (small) overhead that comes with casting between datatypes.

To specify the type of each variable, I personally recommend the bindValue function. Let’s alter our code above to specify the type of our placeholder:

$numberOfUsers = $connection->query('SELECT COUNT(*) FROM users')->fetchColumn();
$users = [];
$statement = $connection->prepare('SELECT * FROM users WHERE id = ? LIMIT 1');

for ($i = 1; $i <= 5; $i++) {
    $id = rand(1, $numberOfUsers);
    $statement->bindValue(1, $id, PDO::PARAM_INT);
    $statement->execute();
    $users[] = $statement->fetch(PDO::FETCH_OBJ);
}

As you can see, the only thing that has changed is our call to execute(): instead of passing in the values straight to it, we have bound it first, and have specified that its type is an integer.

Conclusion

As PHP improves, so do the programmers that use it. PDO allows you to write better code. It’s agile, fast, easy to read, and a delight to work with, so why not implement it in your own project?