MySQL Error Checking in PHP

So often it seems that I am seeing someone struggling to find out what is wrong with their PHP script. They see the following error message:

Warning: mysql_fetch_assoc():
supplied argument is not a valid MySQL result resource
in /var/www/htdocs/somefile.php on line 18

and have no clue what this means. The line number has no bearing on where the real problem is, and there may be a whole cascade of these messages. Let's assume the code looks something like this:

mysql_connect ("server", "user", "password");
mysql_select_db ("dbname");
$result = mysql_query ("SELECT something FROM mytable");
while ($row = mysql_fetch_assoc ($result))
{
...
}

Where did the error occur? The answer is it could be any number of places. Let's make a list:

1. The server name is wrong.
2. MySQL is not running on the server
3. The script is being run on a host that is not allowed to connect to the MySQL server
4. Incorrect username
5. Incorrect password
6. The database does not exist
7. The username does not have permission to access the database at all
   
   And finally:
   
   
8. There is an error in the query

So we can play the guessing game trying to figure out where the error is. Or we can do error checking. The next mistake is to do some error checking, but not in all potential places for error. This can be more confusing than no error checking at all. Let's say that we checked for errors after the call to mysql_query but nowhere else, and the connect call failed. We put in the password wrong perhaps. So the call to mysql_connect fails. Then the call to mysql_select_db fails. Finally the call to mysql_query fails. We actually do check the error message here and what we get is the following:

Access denied for user 'nobody'@'localhost' (using password: NO)

At this point our user is scratching his head, because he knows he put in the right credentials, he's double-checked. This error message gives a complete different set of credentials: wrong username, no password, maybe even a diffrent server name. The reason for this, which is not always obvious, is that a connection did not get established via mysql_connect, so PHP reverts to a default set that is in its config file. Chances are nobody has edited it to put valid credentials in, and I wouldn't personally recommend it for security reasons.

So we can see how it is essential to perform error checking every step of the way to eliminate any guesswork as to what went wrong, where it went wrong, and why. The remaining item to discuss is how to check for errors. There are numerous different styles and techniques, each with their own pros and cons. My personal preference is to do several things:

1. Store the credentials in a separate script. This might even be outside of the htdocs directory tree entirely, so there is no chance of someone referencing it maliciously.
2. Write a small script that references the credentials script, connects to the database and selects the database, using proper error checking of course.
   
   (At this point we have eliminated the possibility of any connection error being mistaken for a faulty query)
   
   
3. Display a compound error message with detailed information about a failing query.

I prefer a compound error message since it provides complete details, but any of the following will work. I recommend aborting the page load by calling die since it can print a message and do the abort in a single call. Plus it can be piggy-backed onto the mysql function calls using the short-circuit boolean logic which says that if the left-hand operand of an or is true, the right-hand side is ignored. So simply append one of the following forms:

• or die()
• or die("Query failed!")
• or die ("Query failed: " . mysql_error())
• or die ("Query failed: " . mysql_error() . " Actual query: " . $query)

I prefer the latter with its complete display of pertinent information. If you have more than one query in a particular document, it may be helpful to add some indication of which on failed to further eliminate confusion.