Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Handling SQL Errors in PDO

DZone's Guide to

Handling SQL Errors in PDO

· Web Dev Zone ·
Free Resource

Learn how error monitoring with Sentry closes the gap between the product team and your customers. With Sentry, you can focus on what you do best: building and scaling software that makes your users’ lives better.

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!

What’s the best way to boost the efficiency of your product team and ship with confidence? Check out this ebook to learn how Sentry's real-time error monitoring helps developers stay in their workflow to fix bugs before the user even knows there’s a problem.

Topics:

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}