When you issue a statement using a database handle, you must know whether the statement modifies rows
or produces a result set, so that you can invoke whichever of exec() or query() is appropriate. However,
under certain circumstances, you might not know the statement type, such as when you write a script
to execute arbitrary statements that it reads from a file. To handle such cases, use prepare() with the
database handle to get a statement handle and execute() to execute the statement. Then check the statement’s
column count:
• If columnCount() is zero, the statement did not produce a result set. Instead, it modified rows and
you can invoke rowCount() to determine the number of affected rows.
• If columnCount() is greater than zero, the statement produced a result set and you can fetch the
rows. To determine how many rows there are, count them as you fetch them.
The following example determines whether a statement modifies rows or produces a result set, and then
processes it accordingly:
$sth = $dbh->prepare ($stmt);
$sth->execute ();
if ($sth->columnCount () == 0)
{
# there is no result set, so the statement modifies rows
printf ("Number of rows affected: %d\n", $sth->rowCount ());
}
else
{
# there is a result set
printf ("Number of columns in result set: %d\n", $sth->columnCount ());
$count = 0;
while ($row = $sth->fetch (PDO::FETCH_NUM))
{
# display column values separated by commas
print (join (", ", $row) . "\n");
$count++;
}
printf ("Number of rows in result set: %d\n", $count);
}
Handling Errors
When you invoke new PDO() to create a database handle, occurrance of an error causes a PDOException
to be thrown. If you don’t catch the exception, PHP terminates your script. To handle the exception
yourself, use a try block to perform the connection attempt and a catch block to catch any error that
occurs:
try
{
$dbh = new PDO("mysql:host=localhost;dbname=test", "testuser", "testpass");
}
catch (PDOException $e)
{
print ("Could not connect to server.\n");
print ("getMessage(): " . $e->getMessage () . "\n");
}
A PDOException is an extension of the PHP Exception class, so it has getCode() and getMessage()
methods that return an error code and descriptive message, respectively. (However, I find that
getCode() always returns 0 for connection errors and is meaningful only for PDO exceptions that occur
after the connection has been established.)
After you successfully obtain a database handle, PDO handles subsequent calls that use it according to the
PDO error mode. There are three modes:
• PDO::ERRMODE_SILENT
When an error occurs in silent or warning mode for a given object method, PDO sets up error information
that you can access when the method returns. This is the default error mode.
• PDO::ERRMODE_WARNING
This is like silent mode but PDO also displays a warning message in addition to setting up error information
when an error occurs.
• PDO::ERRMODE_EXCEPTION
PDO sets up error information when an error occurs and throws a PDOException.
PDO sets error information for the object to which the error applies, regardless of the error mode. This
information is available via the object’s errorCode() and errorInfo() methods. errorCode()
returns an SQLSTATE value (a five-character string). errorInfo() returns a three-element array containing
the SQLSTATE value, and a driver-specific error code and error message. For MySQL, the driverspecific
values are a numeric error code and a descriptive error message.
To handle errors in silent mode, you must check the result of each PDO call. The following example shows
how to test for errors during an operation that uses a database handle, $dbh, and a statement handle, $sth
(you would not necessarily print all the available information as the example does):
if (!($sth = $dbh->prepare ("INSERT INTO no_such_table")))
{
print ("Could not prepare statement.\n");
print ("errorCode: " . $dbh->errorCode () . "\n");
print ("errorInfo: " . join (", ", $dbh->errorInfo ()) . "\n");
}
else if (!$sth->execute ())
{
print ("Could not execute statement.\n");
print ("errorCode: " . $sth->errorCode () . "\n");
print ("errorInfo: " . join (", ", $sth->errorInfo ()) . "\n");
}
Testing the result of every call can become messy quickly. Another way to deal with failures is to set the
error handling mode so that any error raises an exception:
$dbh->setAttribute (PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
In this case, you can assume that if you invoke a method and it returns, it succeeded. You can either leave
exceptions uncaught or catch and handle them yourself. If you leave them uncaught, exceptions cause PHP
to print a backtrace and terminate your script. To catch exceptions, perform PDO operations using a
try/catch construct. The try block contains the operations and the catch block handles an execption
if one occurs.
try
{
$sth = $dbh->prepare ("INSERT INTO no_such_table");
$sth->execute ();
}
catch (PDOException $e)
{
print ("The statement failed.\n");
print ("getCode: ". $e->getCode () . "\n");
print ("getMessage: ". $e->getMessage () . "\n");
}
By using try and catch, you can substitute your own error messages if you like, perform cleanup operations,
and so on.
As shown in the preceding example, the try block can contain operations on multiple handles. However,
if an exception occurs in that case, you won’t be able to use the handle-specific errorCode() or
errorInfo() methods in the catch block very easily because you won’t know which handle caused
the error. You’ll need to use the information available from the exception methods, as shown.