Learning PHP Data Objects: A Beginner's Guide to PHP Data Objects, Database Connection Abstraction Library for PHP 5

Let's consider the well-known MySQL connection scenario:

mysql_connect($host, $user, $password);
mysql_select_db($db);

Here, we establish a connection and then select the default database for the connection. (We ignore the issue of possible errors.)

In SQLite, for example, we would write something like the following:

$dbh = sqlite_open($db, 0666);

Here again we ignore errors (we will cover more on this later). For completeness, let's see how we would connect to a PostgreSQL:

pg_connect("host=$host dbname=$db user=$user password=$password");

As you can see, all three databases require quite different ways of opening a connection. While this is not a problem now, but if you always use the same database management system in case you need to migrate, you will have to rewrite your scripts.

Now, let's see what PDO has to offer. As PDO is fully object-oriented, we will be dealing with connection objects, and further interaction with the database will involve calling various methods of these objects. The examples above implied the need for something analogous to these connection objects—calls to mysql_connect or pg_connect return link identifiers and PHP variables of a special type: resource. However, we didn't use connection objects then since these two database APIs don't require us to explicitly use them if we only have one connection in our scripts. However, SQLite always requires a link identifier.

With PDO, we will always have to explicitly use the connection object, since there is no other way of calling its methods. (Those unfamiliar with object-oriented programming should refer to Appendix A).

Each of the three above connections could be established in the following manner:

// For MySQL:
$conn = new PDO("mysql:host=$host;dbname=$db", $user, $pass);
// For SQLite:
$conn = new PDO("sqlite:$db");
// And for PostgreSQL:
$conn = new PDO("pgsql:host=$host dbname=$db", $user, $pass);

As you can see, the only part that is changing here is the first argument passed to the PDO constructor. For SQLite, which does not utilize username and password, the second and third arguments can be skipped.

As you have seen in previous example, PDO uses the so-called connection strings (or Data Source Names, abbreviated to DSN) that allow the PDO constructor to select proper driver and pass subsequent method calls to it. These connection strings or DSNs are different for every database management system and are the only things that you will have to change.

If you are designing a big application that will be able to work with different databases, then this connection string (together with a connection username and a password) can be defined in a configuration file and later used in the following manner (assuming your configuration file is similar to php.ini)

$config = parse_ini_file($pathToConfigFile);
$conn = new PDO($config['db.conn'], $config['db.user'],
$config['db.pass']);

Your configuration file might then look like this:

db.conn="mysql:host=localhost;dbname=test"
db.user="johns"
db.pass="mypassphrase"

We will cover connection strings in more detail in Chapter 2; here we gave a quick example so that you can see how easy it is to connect to different database systems with PDO.

PDO would not be worth a whole book, if it didn't go beyond the single interface for creating database connections. The PDO object introduced in the previous example has all the methods needed to uniformly execute queries regardless of the database used.

Let's consider a simple query that would select all the car make attributes from an imaginary database employed at a used car lot. The query is as simple as the following SQL command:

SELECT DISTINCT make FROM cars ORDER BY make;

Previously, we would have had to call different functions, depending on the database:

// Let's keep our SQL in a single variable
$sql = 'SELECT DISTINCT make FROM cars ORDER BY make';
// Now, assuming MySQL:
mysql_connect('localhost', 'boss', 'password');
mysql_select_db('cars');
$q = mysql_query($sql);
// For SQLite we would do:
$dbh = sqlite_open('/path/to/cars.ldb', 0666);
$q = sqlite_query($sql, $dbh);
// And for PostgreSQL:
pg_connect("host=localhost dbname=cars user=boss
password=password");
$q = pg_query($sql);

Now that we are using PDO, we can do the following:

// assume the $connStr variable holds a valid connection string
// as discussed in previous point
$sql = 'SELECT DISTINCT make FROM cars ORDER BY make';
$conn = new PDO($connStr, 'boss', 'password');
$q = $conn->query($sql);

As you can see, doing things the PDO way is not too different from traditional methods of issuing queries. Also, here it should be underlined, that a call to $conn->query() is returning another object of class PDOStatement, unlike the calls to mysql_query(), sqlite_query(), and pg_query(), which return PHP variables of the resource type.

Now, let's make our simplistic SQL query a bit more complicated so that it selects the total value of all Fords on sale in our imaginary car lot. The query would then look something like this:

SELECT sum(price) FROM cars WHERE make='Ford'

To make our example even more interesting, let's assume that the name of the car manufacturer is held in a variable ($make) so that we must quote it, before passing it to the database. Our non-PDO queries would now look like this:

$make = 'Ford';
// MySQL:
$m = mysql_real_escape_string($make);
$q = mysql_query("SELECT sum(price) FROM cars WHERE make='$m'");
// SQLite:
$m = sqlite_escape_string($make);
$q = sqlite_query("SELECT sum(price) FROM cars WHERE make='$m'",
$dbh);
// and PostgreSQL:
$m = pg_escape_string($make);
$q = pg_query("SELECT sum(price) FROM cars WHERE make='$m'");

The PDO class defines a single method for quoting strings so that they can be used safely in queries. We will discuss security issues such as SQL injection, in Chapter 3. This method does a neat thing; it will automatically add quotes around the value if necessary:

$m = $conn->quote($make);
$q = $conn->query("SELECT sum(price) FROM cars WHERE make=$m");

Again, you can see that PDO allows you to use the same pattern as you would have used before, but the names of all the methods are unified.

Now that we have issued our query, we will want to see its results. As the query in the last example will always return just one row, we will want more rows. Again, the three databases will require us to call different functions on the $q variable that was returned from one of the three calls to mysql_query(), sqlite_query(), or pg_query(). So our code for getting all the cars will look similar to this:

// assume the query is in the $sql variable
$sql = "SELECT DISTINCT make FROM cars ORDER BY make";
// For MySQL:
$q = mysql_query($sql);
while($r = mysql_fetch_assoc($q))
{
echo $r['make'], "\n";
}
// For SQLite:
$q = sqlite_query($dbh, $sql);
while($r = sqlite_fetch_array($q, SQLITE_ASSOC))
{
echo $r['make'], "\n";
}
// and, finally, PostgreSQL:
$q = pg_query($sql);
while($r = pg_fetch_assoc($q))
{
echo $r['make'], "\n";
}

As you can see, the idea is the same, but we have to use different function names. Also, note that SQLite requires an extra parameter if we want to get the rows in the same way as with MySQL and PostgreSQL (of course, this could be omitted, but then the returned rows would contain both column name indexed and numerically indexed elements.)

As you may already have guessed, things are pretty straightforward when it comes to PDO: We don't care what the underlying database is, and the methods for fetching rows are the same across all databases. So, the above code could be rewritten for PDO in the following way:

$q = $conn->query("SELECT DISTINCT make FROM cars ORDER BY make");
while($r = $q->fetch(PDO::FETCH_ASSOC))
{
echo $r['make'], "\n";
}

Nothing is different from what happens before. One thing to note here is that we explicitly specified the PDO::FETCH_ASSOC fetch style constant here, since PDO's default behavior is to fetch the result rows as arrays indexed both by column name and number. (This behavior is similar to mysql_fetch_array(), sqlite_fetch_array() without the second parameter, or pg_fetch_array().) We will discuss the fetch styles that PDO has to offer in Chapter 2.

Of course, the above examples didn't provide for any error checking, so they are not very useful for real-life applications.

When working with a database, we should check for errors when opening the connection to the database, when selecting the database and after issuing every query. Most web applications, however, just need to display an error message when something goes wrong (without going into error detail, which could reveal some sensitive information). However, when debugging an error, you (as the developer) would need the most detailed error information possible so that you can debug the error in the shortest possible time.

One simplistic scenario would be to abort the script and present the error message (although this is something you probably would not want to do). Depending on the database, our code might look like this:

// For SQLite:
$dbh = sqlite_open('/path/to/cars.ldb', 0666) or die
('Error opening SQLite database: ' .
sqlite_error_string(sqlite_last_error($dbh)));
$q = sqlite_query("SELECT DISTINCT make FROM cars ORDER BY make",
$dbh) or die('Could not execute query because: ' .
sqlite_error_string(sqlite_last_error($dbh)));
// and, finally, for PostgreSQL:
pg_connect("host=localhost dbname=cars user=boss
password=password") or die('Could not connect to
PostgreSQL: . pg_last_error());
$q = pg_query("SELECT DISTINCT make FROM cars ORDER BY make")
or die('Could not execute query because: ' . pg_last_error());

As you can see, error handling is starting to get a bit different for SQLite compared to MySQL and PostgreSQL. (Note the call to sqlite_error_string (sqlite_last_error($dbh)).)

Before we take a look at how to implement the same error handling strategy with PDO, we should note that this will be only one of the three possible error handling strategies in PDO. We will cover them in detail later in this book. Here we will just use the simplest one:

// PDO error handling
// Assume the connection string is one of the following:
// $connStr = 'mysql:host=localhost;dbname=cars'
// $connStr = 'sqlite:/path/to/cars.ldb';
// $connStr = 'pgsql:host=localhost dbname=cars';
try
{
$conn = new PDO($connStr, 'boss', 'password');
}
catch(PDOException $pe)
{
die('Could not connect to the database because: ' .
$pe->getMessage();
}
$q = $conn->query("SELECT DISTINCT make FROM cars ORDER BY make");
if(!$q)
{
$ei = $conn->errorInfo();
die('Could not execute query because: ' . $ei[2]);
}

This example shows that PDO will force us to use a slightly different error handling scheme from the traditional one. We wrapped the call to the PDO constructor in a try … catch block. (Those who are new to PHP5's object-oriented features should refer to Appendix A.) This is because while PDO can be instructed not to use exceptions, (in fact, it is PDO's default behavior not to use exceptions), however, you cannot avoid exceptions here. If the call to the constructor fails, an exception will always be thrown.

It is a very good idea to catch that exception because, by default, PHP will abort the script execution and will display an error message like this:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[28000] [1045] Access denied for user 'bosss'@'localhost' (using password: YES)' in /var/www/html/pdo.php5:3 Stack trace: #0 c:\www\hosts\localhost\pdo.php5(3): PDO->__construct('mysql:host=loca...', 'bosss', 'password', Array) #1 {main} thrown in /var/www/html/pdo.php5 on line 3

We made this exception by supplying the wrong username, bosss, in the call to the PDO constructor. As you can see from this output, it contains some details that we would not like others to see: Things like file names and script paths, the type of database being used, and most importantly, usernames and passwords. Suppose that this exception had happened when we had supplied the right username and something had gone wrong with the database server. Then the screen output would have contained the real username and password.

If we catch the exception properly, the error output might look like this:

SQLSTATE[28000] [1045] Access denied for user 'bosss'@'localhost' (using password: YES)

This error message contains much less sensitive information. (In fact, this output is very similar to the error output that would be produced by one of our non-PDO examples.) But we will again warn you that the best policy is just show some neutral error message like: "Sorry, the service is temporarily unavailable. Please try again later." Of course, you should also log all errors so that you can find out later whether anything bad has happened.

This is a rather advanced topic, but you should become familiar with it. If you are a user of PHP with MySQL or SQLite, then you probably didn't even hear of prepared statements, since PHP's MySQL and SQLite extensions don't offer this functionality. PostgreSQL users might have already used pg_prepare() and pg_execute() in tandem. MySQLi (the improved MySQL extension) also offers the prepared statements functionality, but in a somewhat awkward way (despite the possible object-oriented style).

For those who are not familiar with prepared statements, we will now give a short explanation.

When developing database-driven, interactive dynamic applications, you will sooner or later need to take user input (which may originate from a form) and pass it as a part of a query to a database. For example, given our cars' database, you might design a feature that will output a list of cars made between any two years. If you allow the user to enter these years in a form, the code will look something like this:

// Suppose the years come in the startYear and endYear
// request variables:
$sy = (int)$_REQUEST['startYear'];
$ey = (int)$_REQUEST['endYear'];
if($ey < $sy)
{
// ensure $sy is less than $ey
$tmp = $ey;
$ey = $sy;
$sy = $tmp;
}
$sql = "SELECT * FROM cars WHERE year >= $sy AND year <= $ey";
// send the query in $sql…

In this simple example the query depends on two variables, which are part of the resulting SQL. A corresponding prepared statement in PDO would look something like this:

$sql = 'SELECT * FROM cars WHERE year >= ? AND year <= ?';

As you can see, we replaced the $sy and $ey variables with placeholders in the query body. We can now manipulate this query to create the prepared statement and execute it:

// Assuming we have already connected and prepared
// the $sy and $ey variables
$sql = 'SELECT * FROM cars WHERE year >= ? AND year <= ?';
$stmt = $conn->prepare($sql);
$stmt->execute(array($sy, $ey));

These three lines of code tells us that the prepared statements are objects (with class PDOStatement). They are created using calls to PDO::prepare() method that accepts an SQL statement with placeholders as its parameters.

The prepared statements then have to be executed in order to obtain the query results by calling the PDOStatement::execute() method. As the example shows, we call this method with an array that holds the values for the placeholders. Note how the order of the variables in that array matches the order of the placeholders in the $sql variable. Obviously, the number of elements in the array must be the same as the number of placeholders in the query.

You have probably noticed that we are not saving the result of the call to the PDOStatement::execute() method in any variable. This is because the statement object itself is used to access the query results, so that we can complete our example to look like this:

// Suppose the years come in the startYear and endYear
// request variables:
$sy = (int)$_REQUEST['startYear'];
$ey = (int)$_REQUEST['endYear'];
if($ey < $sy)
{
// ensure $sy is less than $ey
$tmp = $ey;
$ey = $sy;
$sy = $tmp;
}
$sql = 'SELECT * FROM cars WHERE year >= ? AND year <= ?';
$stmt = $conn->prepare($sql);
$stmt->execute(array($sy, $ey));
// now iterate over the result as if we obtained
// the $stmt in a call to PDO::query()
while($r = $stmt->fetch(PDO::FETCH_ASSOC))
{
echo "$r[make] $r[model] $r[year]\n";
}

As this complete example shows, we call the PDOStatement::fetch() method until it returns a false value, at which point the loop quits—just like we did in previous examples when discussing result sets traversal.

Of course, the replacement of question mark placeholders with actual values is not the only thing that prepared statements can do. Their power lies in the possibility of being executed as many times as needed. This means that we can call the PDOStatement::execute() method as many times as we want, and every time we can supply different values for the placeholders. For example, we can do this:

$sql = 'SELECT * FROM cars WHERE year >= ? AND year <= ?';
$stmt = $conn->prepare($sql);
// Fetch the 'new' cars:
$stmt->execute(array(2005, 2007));
$newCars = $stmt->fetchAll(PDO::FETCH_ASSOC);
// now, 'older' cars:
$stmt->execute(array(2000, 2004));
$olderCars = $stmt->fetchAll(PDO::FETCH_ASSOC);
// Show them
echo 'We have ', count($newCars), ' cars dated 2005-2007';
print_r($newCars);
echo 'Also we have ', count($olderCars), ' cars dated 2000-2004';
print_r($olderCars);

Prepared statements tend to execute faster than calls to PDO::query() methods, since the database drivers optimize them only once, in a call to PDO::prepare() methods. Another advantage of using prepared statements is that you don't have to quote the parameters passed in a call to PDOStatement::execute().

In our example we used an explicit cast of the request parameters into integer variables, but we could also have done the following:

// Assume we also want to filter by make
$sql = 'SELECT * FROM cars WHERE make=?';
$stmt = $conn->prepare($sql);
$stmt->execute(array($_REQUEST['make']));

The prepared statement here will take care of the proper quoting made before executing the query.

And just to finish the introduction of the prepared statements here, probably the best feature about them is that PDO emulates them for every supported database. This means you can use prepared statements with any databases; even if they don’t know what they are.

Our introduction would not be complete if we didn't mention that. PDO is a database connection abstraction library, and as such, cannot ensure that your code will work for each and every database that it supports. This will only happen if your SQL code is portable. For example, MySQL extends the SQL syntax with this form of insert:

INSERT INTO mytable SET x=1, y='two';

This kind of SQL code is not portable, as other databases do not understand this way of doing inserts. To ensure that your inserts work across databases, you should replace the above code with :

INSERT INTO mytable(x, y) VALUES(1, 'two');

This is just one example of incompatibilities that may arise when you use PDO. It is only by making your database schema and SQL portable that can ensure you that your code will be compatible with other databases. However, ensuring this portability is beyond this text.