Error Handling
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 fi le 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 fi nd out later whether
anything bad has happened.
Prepared Statements
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";
}
