→ TailTemplate Build stunning websites faster with our pre-designed Tailwind CSS templates

Facebook

Twitter

 Email

Modern PHP Developer - PDO

PHP Data Objects, most commonly known as PDO, is a PHP extension built to solve database access problems. It provides a unified interface to access databases.

PDO creates an abstraction layer for data-access, so developers can write portable code without worrying about the underlying database engines. In layman's terms, you use PDO to develop an application using MySQL as the database storage. If you want to switch to PostgreSQL at any point in time, all you need to do is to change the PDO driver. No other code change is required.

PDO consists of three main types of objects: They are PDO object, PDOStatement object and PDOException object. We should not necessarily neglect the PDO Drivers, but these three types of objects together form the main interface of the PDO extension.

Why use PDO?

If you have developed any MySQL database driven application before, but have never tried PDO, you must be wondering what the benefits are to use PDO, especially when comparing it to its two alternatives.

MySQL

The oldest way to interact with MySQL is to use mysql extension. It was introduced in PHP 2.0.0, however it was deprecated as of PHP 5.5.0, and has already been removed in PHP 7.0.0. It is not recommended to use this extension at all given the factor that it is not supported in newer PHP versions.

MySQLi

Since PHP 5.0.0, an improved version of mysql extension, known as mysqli was introduced. It brought a lot of benefits over the mysql extension, such as object-oriented interface, prepare statements, multiple statements, transaction support, enhanced debugging capabilities and embedded server support.

The main differences between MySQLi and PDO are:

  • PDO supports client-side prepared statements, whereas MySQLi does not. We will discuss client-side prepare statements in details in later sections. It basically means it will emulate prepared statement if the chosen database server does not support it.
  • MySQLi supports both object-oriented API and procedural API, whereas PDO religiously uses objected-oriented API.
  • The biggest advantage of using PDO is to write portable code. It enables developers to switch databases easily, whereas MySQLi only supports the MySQL database.

Ultimately, we recommend using PDO to build your applications.

  • It enables developers to write portable code.
  • It encourages object-oriented programming.

Lastly, we want to emphasize that, by no means are you forbidden to use MySQLi.

In the following sections, we will start with some common ways of running queries using PDO. Then we will demonstrate how to perform various MySQL data manipulation statements using PDO. Finally, we will focus on a few PDO APIs, which serve the same purpose but in different ways.

Running PDO Queries

We summarize the different ways of running PDO queries into four categories, classified by number of steps involved from carrying out the query to getting its result. These categories were created to ease the efforts of remembering PDO APIs and include:

  • exec
  • query fetch
  • prepare execute fetch
  • prepare bind execute fetch

Establish database connection

Before we get into each category, you will first need to be familiar yourself with establishing a database connection using PDO. This is the absolute the fundamental of PDO, as it is used in every piece of code below:

try {
    $dbh = new PDO('mysql:host=localhost;dbname=customers', $user, $pass);
} catch (PDOException $e) {
    die($e->getMessage());
}

To establish a database connection, we instantiate a PDO object with three parameters. The first parameter specifies a database source (known as the DSN), which consists of the PDO driver name, followed by a colon, followed by the PDO driver-specific connection syntax. The second and third parameters are database username and password.

An exception will be thrown if the connection fails. You can catch the exception and handle it gracefully. Kudos to exception in this case, we no longer need to put the connection in a if statement due to a clean and easy to read code base.

In the following code samples, we will neglect this piece of code, to avoid clutter. Keep in mind, you will always need to make the connection first before proceeding with any PDO operations.

exec

This is the simplest form of running a query. We can use it to run a quick query and normally we do not expect it to return any results.

$dbh->exec('INSERT INTO customers VALUES (1, "Andy")');

Though PDO::exec does not return the result corresponding to your query, it does return something. Regardless of what query you run with PDO::exec, it returns the number of affected rows on success. It also returns Boolean FALSE on failure.

A caveat when checking the return type: since it PDO::exec returns 0 when there is no row affected, we should always use === comparison operator to verify success of running the method.

if (FALSE === $dbh->exec('INSERT INTO customers VALUES (1, "Andy")')) {
    throw new MyException('Invalid sql query');
}

If you are building the query string with user input and manually handling security issues as such escaping characters, you should use other alternatives, which we will discuss later.

query fetch

When running query such as SELECT statement, we do expect a return of corresponding results. The easiest way of accomplishing this is use:

$statement = $dbh->query('SELECT * FROM customers');
while ($row = $statement->fetch(PDO::FETCH_ASSOC)) {
    echo $row['id'] . ' ' . $row['name'] . PHP_EOL;
}

Note that methods $dbh->query() and $statement->fetch() , are how we name our categories, by the sequences of calling PDO APIs.

Because PDO::query returns a result set as a PDOStatement object on success (It will return Boolean FALSE on failure, do similar check as PDO::exec if you want to verify). PDOStatement class implements the Traversable interface, which is the base interface for Iterator, meaning it can be used in an iteration statement as such as a loop . Naturally, there is a short version of previous code:

foreach ($dbh->query('SELECT * FROM customers', PDO::FETCH_ASSOC) as $row) {
    echo $row['id'] . ' ' . $row['name'] . PHP_EOL;
}

You might have noticed, when calling either PDO::query or PDOStatement::fetch, we have supplied a flag parameter. This parameter specifies what type of data structure we want from the callee.

A few of the options include:

  • PDO::FETCH_ASSOC: returns an associative array indexed by column name.
  • PDO::FETCH_NUM: returns an numerically indexed array.
  • PDO::FETCH_BOTH (default): returns an array indexed by both column name and 0- indexed column number as returned in your result set. (Combination of PDO::FETCH_ASSOC and PDO::FETCH_NUM).

There are a lot more options. We recommended that you take a quick look at them at PHP Manual. Though this parameter is optional, we should always specify it unless we really want an array indexed by both column name and number. PDO::FETCH_BOTH takes twice as much memory.

prepare execute fetch

We frequently need to accept user's input in order to run a database query. There are two major concerns if we were to use A query fetch approach.

First, we will have to make sure the sql query passed to PDO::query is safe. Escaping and quoting the input values must be well taken care of. Second, PDO::query executes an SQL statement in a single function call, which means if we need to run the same query multiple times, it will use multiple times of resources. There is a better way of doing this.

PDO introduces prepare statement for the first time. So what is prepare statement? According to Wikipedia.

In database management systems, a prepared statement or parameterized statement is a feature used to execute the same or similar database statements repeatedly with high efficiency. Typically used with SQL statements such as queries or updates, the prepared statement takes the form of a template into which certain constant values are substituted during each execution.

Prepare statement solves two concerns raised above. It not only improves the efficiency of running multiple similar queries, but also takes care of escaping and quoting user input values.

Below is how we implement prepare statement using PDO:

$users = ['Andy', 'Tom'];
$statement = $dbh->prepare('SELECT * FROM customers where name = :name');
foreach ($users as $user) {
    $statement->execute([':name' => $user]);
    while ($row = $statement->fetch(PDO::FETCH_ASSOC)) {
        echo $row['id'];
    }
}

Note the steps we have taken here:

  • PDO::prepare is used to create a sql query containing a variable parameter. Naming convention for parameters are either named variables prefixed by a colon(:) or a question mark (?).
  • PDOStatement::execute is called to execute a query with parameters' value. When ? is used in the prepare statement, they are numbered parameters. We can bind the values using a numeric indexed array. Note in the foreach , it uses the same statement to carry out the query after binding the value. It returns Boolean FALSE upon failure. We can use PDOStatement::errorInfo() to get the error information associated with the operation.
  • PDOStatement::fetch is used to fetch result with desired data structure.

prepare bind execute fetch

A minor issue you might have OBSERVED in previous code is what happens when there are a lot of parameters in the prepare statement. We can easy create code piece like this:

$statement->execute([':name' => $user, ':mobile' => $mobile, ':address' => $address ]);

The list can go on and on. This makes code very difficult to read. However, a more important thing to notice here is that, PHP will cast user input value to match its database field type if they do not match exactly, which is prone to bugs.

Here is where PDOStatement::bindValue comes in to save. The recommended way of running previous is:

$users = ['Andy', 'Tom'];
$statement = $dbh->prepare('SELECT * FROM customers where name = :name');
foreach ($users as $user) {
    $statement->bindValue(':name', $user, PDO::PARAM_STR);
    $statement->execute();
    while ($row = $statement->fetch(PDO::FETCH_ASSOC)) {
        echo $row['id'];
    }
}

Instead of using PDOStatement::execute to bind value to parameter, we used PDOStatement::binValue. It adds a few significant improvements to our code:

  • Readability: it makes the code easy to read for other developers, as it indicates the exact data type a parameter should accept.
  • Maintainability: The third parameter, which specifies datatype of passing variable, prevents PHP from casting incompatible datatype, which is prone to bug. In the long run, it also makes the code easier to maintain, as future developer will be able to spot the datatype at a glance.

These four techniques are definitely not official: they are just naming conventions made to memorize PDO APIs. There is no need to follow them strictly. In fact, most of time we combine these techniques together.

Data manipulation

Let's put what we have learned into action. In this section, we will use PDO to accomplish some of the most common MySQL tasks.

Sample database table

We will need a database table to play around with.

For demonstration purpose, we will create a very simple database table:

CREATE TABLE IF NOT EXISTS `customers` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Insert data

Our first task is to insert some data into the table. For this use case, let's suppose we accept data from a user input form via a POST request, and then we insert whatever data is from the form into the customers table.

try {
    $dbh = new PDO('mysql:host=localhost;dbname=inventory', 'root', 'root');
} catch (PDOException $e) {
    die($e->getMessage());
}
 
$name = $_POST['name'];
 
$statement = $dbh->prepare('INSERT INTO customers (name) VALUES (:name)');
 
if (false === $statement) {
    throw new Exception('Invalid prepare statement');
}
 
if (false === $statement->execute([':name' => $name])) {
    throw new Exception(implode(' ', $statement->errorInfo()));
}

We are using the prepare->execute->fetch techinique in this code sample, except we removed the fetch part since we do not expect it to return any result set.

  • The first step is to connect to the database as usual.
  • Then, we create a prepare statement. Note that we also handle failure case by throwing an exception.
  • And lastly, we execute the prepare statement. Failure case is also handled. We are able to output useful information by calling PDOStatement::errorInfo method.

Update data

The second common task is to update existing data. Assume the use case is the same as the previous case, except the user is able to pass in an additional parameter ($id).

try {
    $dbh = new PDO('mysql:host=localhost;dbname=inventory', 'root', 'root');
} catch (PDOException $e) {
    die($e->getMessage());
}
$id = $_POST['id'];
$name = $_POST['name'];
$statement = $dbh->prepare('UPDATE customers SET name = :name WHERE id = :id');
if (false === $statement) {
    throw new Exception('Invalid prepare statement');
}
if (false === $statement->execute([':name' => $name, ':id' => $id])) {
    throw new Exception(implode(' ', $statement->errorInfo()));
}

As you have probably guessed, beside the additional parameter $id ,the code is identical to previous code sample.

Delete

The third common task is to delete an existing data record (similar use case here). User is able to pass in a single parameter ($id), and the corresponding record should be deleted.

try {
    $dbh = new PDO('mysql:host=localhost;dbname=inventory', 'root', 'root');
} catch (PDOException $e) {
    die($e->getMessage());
}
$id = $_POST['id'];
$statement = $dbh->prepare('DELETE from customers WHERE id = :id');
if (false === $statement) {
    throw new Exception('Invalid prepare statement');
}
if (false === $statement->execute([':id' => $id])) {
    throw new Exception(implode(' ', $statement->errorInfo()));
}

Again, this is a very similar code sample as the previous one (prepare->execute->fetch techinique with fetch part). That is the "beautify" of PDO - its object-oriented design makes code easy to write.

Select

Our final task is to select all data records from customers, but this time, we won't ask for user's input.

try {
    $dbh = new PDO('mysql:host=localhost;dbname=inventory', 'root', 'root');
} catch (PDOException $e) {
    die($e->getMessage());
}
$results = array();
$statement = $dbh->query('SELECT * FROM customers');
if (false === $statement) {
    throw new Exception('Invalid query');
}
while ($row = $statement->fetch(PDO::FETCH_ASSOC)) {
    $results[] = $row['name'];
}

In this example, we have used the query->fetch technique. As we mentioned earlier and it is worth mentioning again, when calling PDOStatement::fetch, it is a good habit to always specify the fetch mode.

Now we have run through some quick samples of using PDO for various tasks. These samples are very simple, yet they have showed us a very easy to use and consistent API provided by PDO.

PDO API

So far, we have demonstrated some common PDO APIs. PDO still provides a lot more than we have shown above. In this section, we will explore PDO APIs for the last time and try to cover as many useful APIs as we can. It is nearly impossible to cover every aspect of this topic but always keep in mind that there is a manual page that can be referenced when you are in doubt. (PDO Manual page)

Fetch methods

We have covered one(PDOStatement::fetch) of the fetching methods used to retrieve result sets. In fact, PDOStatement provides three additional fetching methods.

PDOStatement::fetchAll

Working similarly to PDOStatement::fetch, PDOStatement::fetchAll also accepts a flag as first parameter, which is to specify fetch mode. We should always specify the fetch mode the same way we do for PDOStatement::fetch method. It differs from PDOStatement::fetch in that it returns all result at once.

$statement = $dbh->query('SELECT * FROM customers');
$result = $statement->fetchAll(PDO::FETCH_ASSOC);
print_r($result);
// Output
Array
(
    [0] => Array (
            [id] => 2
            [name] => TEST2
        )
    [1] => Array (
            [id] => 3
            [name] => TEST2
        )
    [2] => Array (
        [id] => 4
        [name] => TEST2
    )
)

A caveat of this method is that, since it loads all result sets at once, it might result in heavy memory usage depending on how much data is available. You should use this method with attention.

PDOStatement::fetchColumn

A handy method for retrieving data from the desired column is PDOStatement::fetchColumn. It returns a single column from the next row of a result set. It is similar to PDOStatement::fetch, however it returns the next single column only instead of a next array of result reset.

$statement = $dbh->query('SELECT id, name FROM customers');
while($result = $statement->fetchColumn(1)) {
    echo $result . PHP_EOL;
}

PDOStatement::fetchColumn accepts a single parameter(column name) optionally. The parameter is a 0-indexed number specifying the column to retrieve data from. When this parameter is omitted, it defaults to column number 0.

Two points to not when using this method:

  • PDOStatement::fetchColumn will return Boolean FALSE when it reaches the end of the result set, so it should not be used to retrieve Boolean type from the database.
  • PDOStatement::fetchColumn moves its pointer one step forward when it is called, so there is no way to retrieve another column from the same row. (Obviously the pointer has already moved to the next row when we call it using different column number).

PDOStatement::fetchObject

This method is an alternative to PDOStatement::fetch() with PDO::FETCH_CLASS or PDO::FETCH_OBJ style. Its purpose is to make our code easier to read when called separately, when this method is called, it will return next result set as a PHP object:

$statement = $dbh->query('SELECT id, name FROM customers');
while($object = $statement->fetchObject()) {
    print_r($object);
}
// Output
stdClass Object
(
    [id] => 2
    [name] => TEST2
)
stdClass Object
(
    [id] => 3
    [name] => TEST2
)

We can also pass in our custom PHP class as first parameter, PHP will instantiate one instance of our custom PHP object with data retrieved and return it:

class MyClass
{
}
$statement = $dbh->query('SELECT id, name FROM customers');
while($object = $statement->fetchObject('MyClass')) {
    print_r($object);
}
// Output
MyClass Object
(
    [id] => 2
    [name] => TEST2
)
MyClass Object
(
    [id] => 3
    [name] => TEST2
)

Bind methods:

Previously, we have used PDOStatement::bindValue. This method binds desired value to the placeholder of the query. This method is not the only method for that task though.

bindParam

This method is almost identical to PDOStatement::bindValue and it's no surprise that some people use these two methods interchangeably, however there is a very significant different between these two methods, and it might cost you a fortune if you are not aware of it.

Unlike PDOStatement::bindValue(), the variable is bound as a reference and will only be evaluated at the time that PDOStatement::execute() is called.

Let's see what it means by example.

$user = 'Andy';
$statement = $dbh->prepare('SELECT * FROM customers where name = :name');
$statement->bindValue(':name', $user, PDO::PARAM_STR);
$user = 'Tom';
$statement->execute();
echo $statement->fetchColumn(1);
// Output
Andy
 
$user = 'Andy';
$statement = $dbh->prepare('SELECT * FROM customers where name = :name');
$statement->bindParam(':name', $user, PDO::PARAM_STR);
$user = 'Tom';
$statement->execute();
echo $statement->fetchColumn(1);
// Output Tom

Do you spot the difference? These two pieces of code are identical except one is using $statement->bindParam and the other is using $statement->bindValue. The result produced is entirely different.

PDOStatement::bindParam binds variable $user as a reference. At the time of PDOStatement::execute is called, $user variable is changed to 'Tom' whereas PDOStatement::bindParam variable as a value, it remains as 'Andy' from the time PDOStatement::bindValue is called.

Make sure you understand the difference between these two and choose them according to your needs. Switching these two methods without a fair amount of consideration is discouraged.

bindColumn

Different from PDOStatement::bindValue and PDOStatement::bindParam, this method is not a method for binding variable to prepare statement. In fact, it is quite the opposite: it binds columns from resulting set to PHP local variables.

This is an interesting method to observe. Previously, we discussed that a method PDOStatement::fetchObject, can return result set as a defined object. Here, with PDOStatement::bindColumn, we can bind columns from result set to variables.

$statement = $dbh->prepare('SELECT id, name FROM customers');
$statement->bindColumn('name', $name);
$statement->execute();
while ($statement->fetch(PDO::FETCH_ASSOC)) {
    echo $name . PHP_EOL;
}

The first parameter which specifies the table column, accepts both string column name and 0-indexed number as a value. So the following is valid too.

$statement->bindColumn(1, $name);

Conditions:

In the last section, we will discuss some tips when working with PDO.

IN clause

Building IN clause in a prepare statement is an interesting task. Take a look at following code and imagine this is what we need to build:

?

$users = ['Andy', 'Tom'];
$statement = $dbh->prepare('SELECT * FROM customers where name IN :name');
$statement->execute($user);

At first glance, it seems legitimate. Take a closer look. It won't work because prepare statement only accepts scalar types (e.g. string, int and so on).

The ultimate task becomes building a comma separated string containing equal question marks(?) to the binding array variable. This is how we can build a legit IN clause string.

$users = ['Andy', 'Tom'];
$placeholder = implode(',', array_fill(0, count($users), '?'));
$statement = $dbh->prepare('SELECT * FROM customers where name IN '. $placeholder); $statement->execute($users);

Wildcard characters

When building a LIKE clause, we might be tempted to do this:

$name = 'Andy';
$statement = $dbh->prepare('SELECT count(*) FROM customers where name LIKE %:name%');
$statement->bindValue(':name', $name);

However, that won't work in PDO. We need to shift the wildcard characters to the variable itself:

$name = '%Andy%';
$statement = $dbh->prepare('SELECT count(*) FROM customers where name LIKE :name');
$statement->bindValue(':name', $name);

The end

Hopefully this simple tutorial helped you with your development. If you like our post, please follow us on Twitter and help spread the word. We need your support to continue. Did we miss out anything? Do leave a comment below to let us know.