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 fi nish 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.
Appropriate Understanding of PDO
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.
Summary
This introductory chapter showed you the basics of using PDO when developing
dynamic, database-driven applications with the PHP5 language. Also we looked
at how PDO can be effectively used to eliminate the differences between different
traditional database access APIs and to produce a clearer and more portable code.
In the subsequent chapters, we will be looking at each of the features discussed in this
chapter in a greater detail so that you fully master the PHP Data Objects extension.
