Pragma, not Dogma

PHP is rigorous when reporting errors. It can report the exact file and line number that generated an error, and give you an informative message telling you what went wrong. This rigour doesn't always extend to its relationship with MySQL. This article aims to help you deal with that.

Take this simple piece of code:

$dbhost = "host";
$dbuser = 'user';
$dbpass = "password";
$dbname = "database";

$query = "SELECT (col1, col2 from columnstable";
$mysqli = new Mysqli($dbhost, $dbuser, $dbpass, $dbname);
$result = $mysqli->query($query);
$data = $result->fetch_all();
foreach($data as $row) {
    echo $row[0].','.$row[1]."<br>\n";
}
$mysqli->close();

It will fail because there's a syntax error in the query. How will the program know? It won't.

The code will attempt to execute the query, but MySQL will complain about the broken syntax and $mysqli->query() will return false. The first you'll know about it is when PHP complains with a cryptic message like Fatal error: Call to a member function fetch_all() on boolean.

Checking for errors

The most obvious thing to do is check the result of the call to mysqli->query(). If it returns false then the program can take some action. That's a good start, but it doesn't tell you what the error actually is. To get that information we need to look at $mysqli->error

Updating our code we now have:

// Database credentials omitted for brevity

$query = "SELECT (col1, col2 from columnstable";
$mysqli = new Mysqli($dbhost, $dbuser, $dbpass, $dbname);

// Check that our connection succeeded
if ($mysqli->connect_error) {
    echo $mysqli->connect_error."<br>\n";
    exit;
}
$result = $mysqli->query($query);

// Check for an error in our query
if ($result !== false) {
    $data = $result->fetch_all();
    foreach ($data as $row) {
        echo $row[0] . ',' . $row[1] . "<br>\n";
    }
} else {
    echo "MySQL error: ".$mysqli->error."<br>\n";
    exit;
 }
$mysqli->close();
exit;

Better! Now we see the error message provided by MySQL: Error #1064 - You have an error in your SQL syntax...

However, it's clear that this will quickly require a great deal of error checking, and a substantial amount of code to handle errors. Since most MySQL errors are likely to be fatal, there has to be a better way.

Exceptions and MySQL errors

A simple call to mysqli_report() with the right flags set will cause MySQL failures to throw an exception instead of just returning a false value.

Updating our code again we now have:

$query = "SELECT col1 col2 from columnstable";
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new Mysqli($dbhost, $dbuser, $dbpass, $dbname);

// Check that our connection succeeded
if ($mysqli->connect_error) {
    echo $mysqli->connect_error."
\n";
    exit;
}

try {
    $result = $mysqli->query($sql);   
    $data = $result->fetch_all();
    $mysqli->close();
catch (Exception $e) {
    echo "An SQL error occurred: ".$e->getMessage();
}
exit;

These examples use MySQLi. What about PDO?

Naturally, as PDO is a more recent and more sophisticated interface, error reporting is built in. You can test the return value of a call to PDO::query() or PDO::execute() exactly as you would for MySQLi.

And, of course, you can enable exceptions:

// Database Credntials omitted for brevity

$dsn = "mysql:host=$dbhost;dbname=$dbname";

try {
    $PDO = new PDO($dsn, $dbuser, $dbpass); // PDO always throws an exception if it fails here

    // Tell PDO to throw exceptions if errors occur
    $PDO->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$query = "SELECT (col1, col2 from columnstable";
    $result = $PDO->query($query);
    $data = $result->fetchAll();
    foreach($data as $row) {
        echo $row[0] . ',' . $row[1] . "<br>\n";

    }
} catch (Exception $e) {
    echo "An SQL error occurred: ".$e->getMessage();
}
exit;

References:

• MySQLi
• PDO
• Exceptions