Handling MySQL errors

When it comes to handling SQL querying problems, these are often easier to fix than pure PHP problems because you can narrow down the position of the error very easily, then analyse the faulty SQL line to spot the problem. There are several ways to debug your SQL usage:

Firstly, always check your code is actually correct - use the MySQL monitor to try your queries out to make sure they do what you think they should do, as it will show you your results in an easy-to-read manner and will also give you meaningful error messages if you have slipped up along the way.

Secondly, remember that mysqli_query() will return false if the query failed to execute, which means you can test its return value to see whether your SQL statement is faulty. You should be wary of trying to wrap mysqli_query() up inside another function call, because if it returns false due to a bad query, the chances are the parent function will error out. Consider the following code:

extract(mysqli_fetch_assoc(mysqli_query($db, "SELECT Blah FROM Blah WHERE Blah = $Blah;")));

Yes, it is perfectly valid SQL and under ideal conditions should work, but what if $Blah is unset? Another possibility is that $Blah might end up being a string - note that there are no quotes around $Blah, which means that if $Blah is a string, MySQL will consider it to be a field name, and the query will likely fail.

If the query does fail for some reason, mysqli_fetch_assoc() will fail and output errors, then extract() will fail and output errors, causing a splurge of error messages that likely hinder more than help. Much better is this code:

$result = mysqli_query($db, "SELECT Blah FROM Blah WHERE Blah = $Blah;");
if ($result) {
    extract(mysqli_fetch_assoc($result));
}

That is not to say that having all three functions on one line is incorrect - indeed, I use it all the time. However, you should be very sure of any SQL statement you use in that manner, because any mistakes will be very visible to your users.

A helpful function for debugging MySQL queries is mysqli_error(), which returns any MySQL errors from the last function call. Each time you call a new MySQL function, the value mysqli_error() is wiped, which means you need to call mysqli_error() as soon as your suspect mysqli_query() has been called, otherwise it might be wiped over by subsequent queries from your connection.

Copyright ©2015 Paul Hudson. Follow me: @twostraws.