Modern PHP Developer
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.
Table Of Content
1. 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.
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.
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.
2. 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:
- 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:
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.
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.
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 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.
When running query such as SELECT statement, we do expect a return of corresponding results. The easiest way of accomplishing this is use:
Note that methods
$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:
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:
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:
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:
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.
3. 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:
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.
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.
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).
As you have probably guessed, beside the additional parameter $id ,the code is identical to previous code sample.
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.
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.
Our final task is to select all data records from customers, but this time, we won't ask for user's input.
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.
4. 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)
We have covered one(PDOStatement::fetch) of the fetching methods used to retrieve result sets. In fact, PDOStatement provides three additional fetching methods.
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.
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.
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.
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).
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:
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:
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.
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.
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.
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.
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.
In the last section, we will discuss some tips when working with PDO.
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:
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.
When building a LIKE clause, we might be tempted to do this:
However, that won't work in PDO. We need to shift the wildcard characters to the variable itself:
5. 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.