MySQL Error Checking in PHP
Warning: mysql_fetch_assoc():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:
supplied argument is not a valid MySQL result resource
in /var/www/htdocs/somefile.php on line 18
mysql_connect ("server", "user", "password");Where did the error occur? The answer is it could be any number of places. Let's make a list:
$result = mysql_query ("SELECT something FROM mytable");
while ($row = mysql_fetch_assoc ($result))
- The server name is wrong.
- MySQL is not running on the server
- The script is being run on a host that is not allowed to connect to the MySQL server
- Incorrect username
- Incorrect password
- The database does not exist
- The username does not have permission to access the database at all
- There is an error in the query
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:
- 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.
- 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)
- Display a compound error message with detailed information about a failing query.
- or die()
- or die("Query failed!")
- or die ("Query failed: " . mysql_error())
- or die ("Query failed: " . mysql_error() . " Actual query: " . $query)