Jay Blanchard, LLC

Demystifying PHP's Data Objects (PDO)

April 2015

• Required: a webserver running PHP and a database of your choice. MySQL is used in all of the examples. If you use another database you will need to change the connection string accordingly.
• Nice to know: PHP basics, SQL basics.
• Download: Project Files

If you have been lurking around the PHP tag on StackOverflow or hanging out on the PHP-General mailing list you have no doubt seen the following, probably more than once:

Please, stop using mysql_* functions. They are no longer maintained and are officially deprecated. Learn about prepared statements instead, and use PDO.

It turns out a great number of schools teaching PHP have not updated their curriculum to account for the changes. They continue to teach using the older API where the function calls are prepended with mysql_*. When those students start reaching out for help with their code they're met with criticism for not quitting the older API in favor of the new. The problem is compounded because these folks are new to programming and they see this as just another roadblock to their success.

Enough editorializing. I could climb way up on a soap....er, never mind.

Can we make PDO much simpler? Absolutely! In the process we'll gain some added benefits such as preventing the dreaded...ominous music queue...SQL Injection!

We'll start with a portable function which connects to the database, performs the query and then returns data when required. The function is very basic but can be enhanced to perform more complex operations if need be. Next we'll look at how to send queries to the function. Finally we'll place our queries in functions to keep our code neat and tidy.

By the time we're done you should have a solid understanding of PDO and you'll walk away with code you can plug into your projects right away.

Let's take care of some housekeeping first. Open a new file and add the following lines:

                    <?php

                    error_reporting(E_ALL);
                    ini_set('display_errors', 1);
                    

We just want to make sure we see any errors while we are developing. Save the file as pdo_connect.php.

Next, add the user name and password needed for accessing your database. In the example here (and in the download) we will be using a connection to a MySQL database but you're free to use any database you choose with PDO as long as PHP has a driver for your database. (Setting up a PHP instance for PDO can be bit of a drag and is much too long to cover here.) I prefer constants for the user name and password as I do not expect them to change:

                    define('USER', 'your_username');
                    define('PASS', 'your_password');
                    

Now we can move to the meat of our file, the portable PDO function I described earlier. I don't want to bore you to death but we'll go line-by-torturous-line through the function to remove the veil of mystery from the PDO magic. Start by setting up the function:

                    function dataQuery($query, $params) {
                        $queryType = explode(' ', $query);
                    

What is the explode() function doing here? We'll actually use the information later in the function to determine if results need to be returned to the caller of the function.

To establish the connection we will use a try/catch statement which will allow us to easily manage errors (show them, send them to a log file, etc.). First we will establish the connection, disable emulation, turn on error checking and throw exceptions when an error occurs:

                    try {
                        $dbh = new PDO('mysql:host=localhost;dbname=test', USER, PASS); // test is the database name used in the examples
                        $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
                        $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                    }
                    

The first $dbh->setAttribute() line, tells PDO to disable emulating prepared statements and use actual prepared statements. This insures the query and its parameters aren't parsed by PHP before sending it to the MySQL server. Adding this destroys a hackers ability to send nasty stuff to your database.

Now add the catch statement for the exceptions:

                    catch(PDOException $e) {
                        echo $e->getMessage();
                        $errorCode = $e->getCode();
                    }
                    

The catch statement is where you might decide to write out the errors to a log file instead of displaying on screen as we have done here. As an example:

                    catch(PDOException $e) {
                        echo "We are experiencing a technical difficulty. Please stand by."; // give user a friendly message
                        file_put_contents('/var/log/sql_error.log', $errorCode = $e->getCode() . ' ' . echo $e->getMessage() , FILE_APPEND); // write the error out to a log you can check at your leisure
                    }
                    

Stick with the first version of the catch statement for now. At this point I recommend closing the dataQuery() function and uploading the file to your web server for a quick test. Before you upload make sure you've included the right user name, password, host and database name. Also, add this line to your code (just for testing) to call the function:

                    $test = dataQuery('SELECT * FROM `test`', array(''));
                    

Once uploaded, browse over to the file's location. Did you get a blank screen? If so, congrats! You've successfully connected to your database.

Let's get pdo_connect.php ready for queries now. To do this we are going to add another try / catch statement, again for the ease of catching and handling errors. Start by opening the try just after the catch you previously entered:

                    try {
                        $queryResults = $dbh->prepare($query);
                    

We start the try statement with preparing of the query we've sent to the function. Next we'll add a single line to the function to handle all of the parameters we send to the query and the same line will execute the query:

                    $queryResults->execute($params);
                    

It is at this point the query has been run and has returned any results we might be expecting. Add some checking to determine if there is data to be returned:

                    if($queryResults != null && 'SELECT' == $queryType[0]) {
                        $results = $queryResults->fetchAll(PDO::FETCH_ASSOC);
                        return $results;
                    }
                    

The 'if' statement allows us to check the type of query which, in turn, allows us to return the results if needed. Here I use the fetchAll() function, preferring to return the entire associative array of data. From there I can pick or loop through the data as needed without having to make any more calls to the database. All we have left is a little cleanup and a catch statement identical to the one we used before:

                            $queryResults = null; // first of the two steps to properly close
                            $dbh = null; // second step to close the connection
                        }
                        catch(PDOException $e) {
                            $errorMsg = $e->getMessage();
                            echo $errorMsg;
                        }
                    }
                    

The entire file is available in the download, but if you'd like to copy and paste here is the code all together:

                    <?php

                    error_reporting(E_ALL);
                    ini_set('display_errors', 1);

                    define('USER', '');
                    define('PASS', '');


                    function dataQuery($query, $params) {
                        $queryType = explode(' ', $query);

                        // establish database connection
                        try {
                            $dbh = new PDO('mysql:host=localhost;dbname=test', USER, PASS);
                            $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
                            $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                        }
                        catch(PDOException $e) {
                            echo $e->getMessage();
                            $errorCode = $e->getCode();
                        }

                        // run query
                        try {
                            $queryResults = $dbh->prepare($query);
                            $queryResults->execute($params);
                            if($queryResults != null && 'SELECT' == $queryType[0]) {
                                $results = $queryResults->fetchAll(PDO::FETCH_ASSOC);
                                return $results;
                            }
                            $queryResults = null; // first of the two steps to properly close
                            $dbh = null; // second step to close the connection
                        }
                        catch(PDOException $e) {
                            $errorMsg = $e->getMessage();
                            echo $errorMsg;
                        }
                    }
                    ?>
                    

You can upload this to your server now and run it with the same call we used earlier, adding one line to display any results:

                    $test = dataQuery('SELECT * FROM `test`', array(''));
                    print_r($test);
                    

If you don't have a database named 'test' (in our connection string) containing a table named 'test' (in our query) you will get errors. You will need to create these or use the SQL script included in the download to create and populate the table. Once created run this code again and an array should be displayed on the screen. The test lines should be included on pdo_connect.php below the dataQuery() function. Once tested remove or comment out those lines.

The function we created simplifies PDO database operations to the point where it only takes a handful of lines to get results. Let's setup a basic file called pdo_test.php in which we will include the PDO connection file. We'll add query functions later:

                    <?php

                    include '/inc/php/pdo_connect.php'; // makes assumptions about directory structure, yours may differ

                    // save room here for queries!

                    ?>
                    

Our earlier test example for getting data from the table was workable, but not really extendable. Let's rewrite our test code a little bit:

                    $query = "SELECT * FROM `test` ";
                    $params = array(''); // recall the caution about binding and arrays!
                    $results = dataQuery($query, $params);
                    echo '<pre>';
                    print_r($results);
                    

Upload pdo_test.php to your server with the new code in place. Open the file in your browser and if all is OK you will see the array output to your screen:

Make sure you have created a database named 'test' with a table named 'test'. You can use the SQL script included in the download to set up the new table and populate the table with some data.

Let's take it a step further by narrowing our search. To do this we modify the query and add to the parameters. The parameters will hold the filter information:

                    $query = "SELECT * FROM `test` WHERE `id` = ?";
                    $params = array('2');
                    $results = dataQuery($query, $params);
                    echo '<pre>';
                    print_r($results);
                    

Writing queries like this using question marks (?) as place holders is known as parameterizing the query. We can specify any number of parameters (never table or column names though) and as long as the number of items in our $params variable matches the number of place holders in our queries. Using parameterized queries prevents SQL Injection because the parameter values are combined with the compiled SQL statement, not the SQL string. All of your parameters are treated as strings or other appropriate data types instead of commands that can be executed against your database.

To put icing on the cake we can raise our game by creating specialized functions. Change the code in pdo_test.php (you can delete the other query code or comment it out) to add a new function:

                    function findKitByPrice($price) {
                        $query = "SELECT * FROM `test` WHERE `prod_price` = ?";
                        $params = array($price);
                        $results = dataQuery($query, $params);
                        return $results;
                    }
                    

Once done we can add the code to call the function with the proper filters:

                    $price = 34.95;
                    $kitsByPrice = findKitByPrice($price);
                    echo '<pre>';
                    print_r($kitsByPrice);
                    

One more data retrieval example using multiple parameters. First the function:

                    function findKitByMfgPrice($mfg, $price) {
                        $query = "SELECT * FROM `test` WHERE `prod_name` LIKE ? AND `prod_price` > ?";
                        $params = array($mfg, $price);
                        $results = dataQuery($query, $params);
                        return $results;
                    }
                    

Note there are 2 parameters in this case, so you must have 2 parameters. Here is the call:

                    $mfg = '%Mobeius%'; // prod_name contains a longer string, so we need to use LIKE
                    $price = 34.95;
                    $kitsByMfgPrice = findKitByMfgPrice($mfg, $price);
                    echo '<pre>';
                    print_r($kitsByMfgPrice);
                    

Save for matching up parameters in our queries and properly handling errors there is not much else to do to be successful with PDO. In the next installment we'll look at inserting, updating and deleting data using parameterized queries paired with the connection / query function we wrote earlier.

Happy coding!

Comments? Shoot me your thoughts on Twiiter: @jaylblanchard