Handling SQL Errors in PDO

I love PHP's PDO (PHP Data Objects) extension; it gives a consistent, object-oriented interface to handling all kinds of relational database backends. One thing that annoys me is that the MySQL driver for PDO defaults to a silent error mode which can make SQL errors tricky to spot!

To give you an example, consider the query below (the correct tablename is country, so this SQL will fail):

$db = new PDO('mysql:host=localhost;dbname=sakila', 'user', 'pass');
 
$sql = 'select * from countrt';
$stmt = $db->query($sql);
 
while(($row = $stmt->fetch()) != false) {
    echo $row['country'] . "\n";
}

The script will output an error because $stmt is not an object.

You have a few options here - you can check that you got an object back before you try to do anything with it, for example. Alternatively you can prepare() and then execute() the statement, which means that you'll have a statement object and if there are any errors, they'll be available. This gives code that looks something like:

$db = new PDO('mysql:host=localhost;dbname=sakila', 'user', 'pass');
 
$sql = 'select * from countrt';
$stmt = $db->prepare($sql);
$stmt->execute();
 
if($stmt->errorCode() == 0) {
    while(($row = $stmt->fetch()) != false) {
        echo $row['country'] . "\n";
    }
} else {
    $errors = $stmt->errorInfo();
    echo($errors[2]);
}

This way, we can check if we got errors and examine what they are if we did. (if you're coming on my database course next week, you'll see this example and also a demonstration of how to change this silent behaviour. I will endeavour to write about changing it here as well at some point).

The main thing to look out for is that it is easy to miss the difference between getting no results because there aren't any and getting no results because there's an error that you can't see - and I hope this code example shows how you can interrogate PDO to find out which one it was!