Issuing SQL Queries, Quoting Parameters,
and Handling Result Sets
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 defi nes 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 unifi ed.
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 specifi ed 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.
