DZone
Web Dev Zone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
  • Refcardz
  • Trend Reports
  • Webinars
  • Zones
  • |
    • Agile
    • AI
    • Big Data
    • Cloud
    • Database
    • DevOps
    • Integration
    • IoT
    • Java
    • Microservices
    • Open Source
    • Performance
    • Security
    • Web Dev
DZone > Web Dev Zone > Handling SQL Errors in PDO

Handling SQL Errors in PDO

Lorna Mitchell user avatar by
Lorna Mitchell
·
Nov. 16, 11 · Web Dev Zone · Interview
Like (0)
Save
Tweet
5.69K Views

Join the DZone community and get the full member experience.

Join For Free

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!

MySQL

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • Refactoring Java Application: Object-Oriented And Functional Approaches
  • Evolving Domain-Specific Languages
  • How to Minimize Software Development Cost
  • A Smarter Redis

Comments

Web Dev Partner Resources

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • MVB Program
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends:

DZone.com is powered by 

AnswerHub logo