USING PDO

Databases are important to any dynamic website. That’s why we’ve had a lot to say about them in this book (too much, some
of you may be thinking). However, PHP Data Objects (PDO) can’t be ignored because they are packaged with PHP version 5.1 and higher, and they are “something many of the PHP dev team would like to see as the recommended API for database work.”1
PDO is a data-access abstraction layer that aims for uniform access to any database. That’s a pretty good reason for looking at PDO, but what interests us in particular is that the PDO interface is entirely object-oriented (OO). It makes extensive use of the OO improvements to PHP 5. In fact, it cannot be run on lower versions of PHP.
Drivers are available for all the major databases supported by PHP— Oracle, Microsoft SQL Server, PostgreSQL, ODBC, SQLite, and all versions of MySQL up to version 5. So, if you use a variety of different databases, PDO

is especially worth investigating. However, even if you use only one database, PDO can be helpful for switching between versions. Be warned, though, that it is still early days for PDO, and some of the drivers may lack some functionality.

Pros and Cons

The promise of database abstraction is the ability to access any database using identical methods. This gives developers the flexibility to change the back-end database with minimal impact on code. Another advantage of an API such as PDO is a reduced learning curve. Instead of having to learn the specifics of each different database, you can learn one interface and use it with any database. Lastly, with an API you may be able to use features not available to the native database—prepared statements, for example—but more about that later.
On the negative side, a data-access abstraction layer may adversely affect performance and may deprive you of the ability to use non-standard features natively supported by specific databases. It may also introduce an unwanted degree of complexity into your code.
The best way to make a decision about the suitability of PDO is to try it. Converting the SQLite application created in Chapter 15 is a good way to do this. Our SQLite application makes use of a limited number of the features of PDO, so we’ll also look at some of PDO’s additional capabilities. We won’t look at every detail, but this chapter will show you enough to allow you to make an informed decision.

NOTE If you are running PHP 5.0.x you can install PDO using PEAR. See the PHP site for instructions. If you install the latest version of PDO you will be able to use SQLite version 3.

Converting the SQLite Application

The very first thing to realize is that we cannot use our derived class SQLiteDatabasePlus with PDO because the PDO driver for SQLite doesn’t know anything about our derived class. We could, of course, extend the PDO class to incorporate the methods we added to our SQLiteDatabasePlus class, but doing so is contrary to the whole purpose of a database abstraction layer. Taking that route would be an implicit admission of defeat right from the start—there wouldn’t be one interface for all databases, but instead any number of derived interfaces.

Code Changes

As usual, a complete version of the code discussed in this application is avail- able from the book’s website. The directory structure for the files accompa- nying this chapter is the same as those for Chapter 15, so you shouldn’t have trouble finding your way around. Also, as usual, I won’t reproduce all the code in this chapter, only relevant sections. We’ll start by looking at the constructor.

NOTE The application we developed in Chapter 15 uses version 2 of SQLite. Take this oppor- tunity to upgrade to SQLite version 3, since PHP 5.1 supports this version. It’s appre- ciably faster than version 2 and handles concurrency better. However, the database format has changed; versions 2 and 3 are incompatible. This is only a minor inconvenience. If you want to get off to a quick start, install the database by running the db_install
_script.php file found in the dbdir director y. This will create an SQLite version 3 database for you. Otherwise, you may download the command-line version of SQLite 3 from the SQLite website, and then see the section “Getting Started” on page 141 for details about using a database dump to install a database. The PDO driver for SQLite version 2 doesn’t support the sqliteCreateFunction method, so upgrading is required if you want to use this method. Matching the version of the command-line tool with the version of SQLite supported by PHP is equally important in this chapter, because of version incompatibilities. For example, a database created at the command line using SQLite version 3.5.5 will not work properly with the current SQLite PDO driver.

Constructing a PDO Object
When constructing a PDO database or connection object, a Data Source Name (DSN) is passed as a parameter. A DSN is made up of a driver name, followed by a colon, followed by database-specific syntax. Here’s how to create a connection to an SQLite database:

$pdo = new PDO('sqlite:resources.sqlite');

A PDO object is constructed in the same way as an SQLiteDatabase object except that the driver name must precede the path to the database and be separated from it by a colon.

The createFunction Method

You may recall that one of the deficiencies of SQLite was the lack of built-in functions, especially with respect to date manipulation. We were able to over- come this deficiency by adding functions to SQLite using the createFunction method. Fortunately for us, the developers of PDO have seen fit to incorporate this capability by including the SQLite-specific method sqliteCreateFunction. This saves us some work but also reduces the “abstractness” of the PDO layer— but more about this later.

Exceptions

In Chapter 15 we extended the SQLiteDatabase class in order to throw an exception if a query failed. For that reason we overrode the five existing query methods. The same effect can be achieved with PDO by using only one line of code:

$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Calling setAttribute against a PDO database connection in this way causes any failed attempt at querying the database to throw a PDOException. This is a very succinct way of dealing with one of the shortcomings of the

SQLite classes. We quickly reap all the benefits of throwing exceptions without having to extend the SQLite database class.

Methods of SQLiteDatabasePlus
Because a PDO connection can be configured to throw exceptions, we don’t need the query methods of our derived class SQLiteDatabasePlus. However, the utility methods we added are another matter. The only way to use these methods is to rewrite them as functions. They convert fairly readily, and the metadata queries they employ work as before. The only real difference is that as functions they are not quite as convenient to use. I won’t reproduce them here, but they can be found in the dbfunctions.inc file included in this chap- ter’s downloads.

Escaping Input
The cleanData utility method of our derived class incorporated the sqlite_escape_string function to escape input before insertion into the database. The equivalent PDO method is quote. Be aware that this method not only escapes single quotation marks, but also encloses the item quoted within quotation marks. If you need to manipulate data prior to inserting it, quote should only be called immediately prior to insertion. For portability reasons, the PHP site recommends using prepare rather than quote. We’ll investigate this method more fully when we discuss statements, in the section “Additional Capabilities of PDO” on page 161.

Query Methods
One of the attractions of SQLite is its variety of query methods. If you need a quick refresher, see the section “Query Methods” on page 148. In this section we’re going to compare SQLite’s query methods to what’s available using PDO.
The difference between the object models of SQLite and PDO makes a direct comparison a bit awkward. Principally, querying a PDO connection returns a statement object, and this statement effectively becomes a result set once it is executed. As already mentioned, we’ll cover statements in the section “Additional Capabilities of PDO” on page 161, but for the moment we can treat them as identical to result sets.
The five query methods of SQLite are singleQuery, execQuery, arrayQuery,
query, and unbufferedQuery. We’ll discuss each SQLite query method in turn.
The singleQuery method returns a single column as an array, bypassing the need to create a result set. To mimic it we would use a PDO query to return a statement and then call fetchColumn against this statement. The fetchColumn method can return any column in the current row.
The execQuery method of an SQLite result set can execute multiple, semicolon-separated queries. PDO can easily mimic this behavior—in fact this is something that statements excel at.
As you might expect, returning an array in the fashion of arrayQuery is also easily handled by PDO by calling the fetchAll method against a statement.
The two remaining query methods of SQLite, query and unbufferedQuery, return SQLiteResult and SQLiteUnbuffered objects, respectively. PDO statements are comparable to unbuffered result sets rather than buffered ones, so the

unbuffered behavior is easily reproduced. Our SQLite application uses buffered result sets in cases where we need to know that there are records returned or where the specific number of records is required. To buffer records using PDO you can use the fetchAll method of a statement to return an array. A record count can be obtained by using the count function on the returned array. Alternately, calling the function empty on the statement returned by a query will determine whether there is at least one record.
In general, when querying the database, it looks like some efficiencies have been lost. What is a single process using SQLite becomes a two-step process with PDO. Using two methods instead of one can make code more complicated. However, as we’ll soon see, there are some important advantages to the PDO methods, and in some cases this two-step process can be simplified.

Additional Capabilities of PDO

Converting one application certainly doesn’t tell the whole story about PDO, so let’s have a look at some of the other capabilities of PDO. There are three PDO classes: PDO; the database or connection class, PDOStatement; and PDORow. By far the most interesting and unfamiliar class is the statement class, and this is where we’ll concentrate our attention. We’ll briefly discuss PDORow when we come to the fetchObject method of the statement class.

The PDO Class

So far in this book we’ve created our own connection class and used the SQLiteDatabase class—classes that have many similarities to PDO. With this experience, I needn’t say a lot about the PDO class.
I’ve already mentioned the quote, setAttribute, and query methods of the PDO class. For databases such as SQLite that support transactions, this class also has methods to begin, commit, or roll back transactions.
The most important method, however, is prepare. This method is similar to the query method in that it also returns a PDOStatement. The major differ- ence is that query is typically used for SQL statements that are issued once and prepare for queries that will be issued a number of times.

PDOStatement

In the conversion of our application from SQLite to PDO, in some cases the difference between a result set and a statement isn’t apparent at all. For example, the snippet of SQLite code to display all the resources in our data- base (from the file getresources.php) is shown in Listing 16-1.

$result = $db->query($strsql);
if(!empty($result)){
$previous = "";
foreach ($result as $row){
foreach ($row as $key => $value){

The equivalent PDO code is identical. In one case, the variable $db represents an SQLiteDatabasePlus, and in the other it represents a PDO. Like- wise the $result variable is an SQLiteResult or a PDOStatement. Because result sets and statements are both iterable, they can be used in the same way within foreach loops. In this case, using PDO takes no more steps than using SQLite directly.
This similarity between a result set and a statement makes it easy to start using statements, but it also masks important differences. These differences are more apparent when the prepare method is used.

prepare

Instead of using the query method to create a PDOStatement object, the code
$result = $db->query($strsql); in Listing 16-1 can be changed to the following:

$result = $db->prepare($strsql);
$result->execute();

I have already hinted at one of the advantages of using prepare instead of query. Any variables used in the parameter to the prepare method will auto- matically be quoted. This is an easier and more portable way of escaping quotes than using the quote method. If used exclusively, you needn’t worry about forgetting to quote an SQL statement. This is a security advantage that will protect against SQL injection attacks.
This is one way in which a statement is superior to a result set, but it is not the most important difference. Statements are more commonly used to insert multiple records into a database, and they do this more efficiently than a series of individual SQL statements. This is what is referred to as a prepared statement.

Prepared Statements
There are a number of ways that statements can be used with both input and output parameters. We’ll content ourselves with one example of a prepared statement used to make multiple inserts. The SQLite application in Chapter 15 has no need for multiple inserts, so we’ll create a simple new example.
Suppose you have an ecommerce application. The inventory numbers for various purchased items are stored in an array. Here’s how we can update our database using a prepared statement:

//$pdo is an instance of a PDO connection
$orderid = "200";
$array_skus = array(1345, 2899, 6502);
$strsql = "INSERT INTO tblorderitems (orderid, inventorynumber) ". " Values ($orderid, ? ) ";
$stmt = $pdo->prepare($strsql);
$stmt-> bindParam(1, $number);
foreach ($array_skus as $number){
$stmt-> execute();
}

This is a fairly simple example of a prepared statement, but it will give you an understanding of how statements work. A replaceable parameter ( ) is indicated by a question mark, this parameter is bound to the variable
$number, and each iteration of the foreach loop executes the query, inserting a different value.
Using statements is much more efficient than separately querying the database. The performance improvements are due to the fact that after a parameterized query is first executed, for each subsequent query, only the bound data needs to be passed.
Remember, there’s no such thing as a prepared statement in SQLite. The developers of PDO thought it important to support this feature for all databases regardless of native support. Using PDO is a good way to familiar- ize yourself with statements and makes it easy to switch to a database that supports this capability.

Fetching Objects

For an OO programmer, the ability to retrieve rows as objects is important. PDO has a number of ways of doing this. An easy way of doing this is to create an instance of the PDORow class in the following way:

$stmt = $pdo->query( "SELECT * FROM tblresources", PDO::FETCH_LAZY );
$pdorow = $stmt->fetch();

There is also a fetchObject method that can be used to create an instance of a specific class. Supposing we have defined a class called RowInfo, creating an instance of that class is done in this way:

$row = $stmt->fetchObject('RowInfo');

This method is perhaps the simplest way to create an object. You can use it with an existing class or, if you don’t specify a class, it will create an instance of stdClass, the generic object class.
What these various ways of creating objects have in common is that they instantiate an object, creating data members from the columns of the current row.
PDOStatement also has a method, getColumnMeta, to dynamically retrieve metadata about the current query. By using this method in conjunction with one of the create object methods and adding a magic get method to the class you’re instantiating, it is easy to retrieve the data members of any object cre-
ated from any query without knowing the structure of that query beforehand.2
Perhaps our criticisms of magic set and get methods in Chapter 13 were a little harsh.

NOTE SQLite has a procedural version of fetchObject that returns a stdClass object. It is documented as a result set method but not yet implemented.

2 You could, of course, query the sqlite_master table for this information, but the PDO method provides a database-independent way of doing this.

Assessment

We’ve touched on a number of the capabilities of PDO. We’ve used some of them in our application, but not all of them. This is by no means a definitive overview of PDO, but we certainly have enough information to make a judgment about the utility of this data-access abstraction layer.
Our application behaves exactly as it did without PDO. We haven’t had to sacrifice any functionality and some things were much easier to implement— catching exceptions, for example. All our queries, triggers, and views work in exactly the same way. One minor inconvenience was converting the utility methods of our derived class, but we were able to implement them proce- durally without loss of functionality. The object model of PDO is perhaps a little more difficult, but along with this we’ve gained the ability to use pre- pared statements should we need them. No question—PDO works well with SQLite.
But what if we decided to use a MySQL back-end instead? How many changes would we have to make? Beyond changing the driver, the most obvious change would be removal of the SQLite-specific function sqliteCreateFunction. As noted in Chapter 15, this could be replaced by the MySQL function SUBDATE. Likewise, any other operators or functions not used by MySQL would have to be changed.
Another option would be to use standard SQL wherever possible. The date manipulation functions could be ignored, and this task performed from within PHP. That’s a choice each developer will have to make for themselves, but I expect most won’t quickly give up on hard-won knowledge about specific SQL dialects.

Is It the Holy Grail?

One very legitimate concern might be voiced over the inclusion of the SQLite-specific method sqliteCreateFunction, and this is certainly not the only database-specific capability provided by PDO.3 Doesn’t providing database- specific functionality do exactly what we refrained from doing at the start— namely, extending the PDO class?
The short answer is, unquestionably, yes. But the whole notion of a perfect database abstraction layer is a Holy Grail—glimpsed here and there but never grasped. Providing some database-specific functionality is a sensible compromise and an impetus to use PDO. As always with PHP, utility and not purity of concept is paramount. The important thing to note is that each developer can make their own decision about an acceptable level of database abstraction by incorporating database-specific methods and database-specific SQL or not as the case may be. However, in one respect there’s no choice at all: If you choose to use PDO, you must take an OO approach.