Diagnosing MySQL Errors

So, I'm a firm believer in diagnosing MySQL errors as easy as possible. In a perfect world, our code would be error-free and so would the data and database. Unfortunately, this is not the case, so it behooves you to take a couple of precautions. Let's have a look at a little bit of sample code. Below is a simple page that shows a list of cakes taken from a database.

if(!mysql_connect("localhost", "admin", "password")) die("Unable to connect to mysql: " . mysql_error());
if(!mysql_select_db("foods")) die("Unable to select table: " . mysql_error());

$query = "SELECT name "
       . "FROM foods_cakes";

if(!$results = mysql_query($query))
{
$html .= $query . "<br />";
$html .= "Unable to retrieve cakes from database: " . mysql_error();

die($html);
}

if(mysql_num_rows($results) == 0) die("There are no cakes in the database");

echo "<ul>";

while($resuls = mysql_fetch_assoc($results))
{
echo "<li>";
echo stripslashes($result['name']);
echo "</li>";
}

echo "</ul>";

Alright, first up is the connection. As you can see, if there's an error with connection or selecting the table, it reports the problem and the technical error, then immediately stops executing the code. The information should help us isolate the problem. We kill the program because everything following it relies on it and so there's no point in continuing.

When we run the query, we again report the problem, report the technical error and stop the code. We've also included the actual query. Right now, the query's fairly simple, but when you add more parameters and start using PHP variables, it's difficult to spot the error inside the actual code without seeing the end result. Stopping the code is necessary again, for the same reason above. However, in different circumstances, it might be better to craft your code in order to allow some code to continue while preventing code that relies on your non-functioning queries.

We do more or less the same thing with the result count. However, using a 'die' in this situation would usually be a mite extreme, as usually an empty result set should be taken into account when you're developing code.

Finally, we strip the slashes from the results. Whenever anything goes into the database, it should have mysql_escape_string applied to it, so that it doesn't screw up your query. When it comes out again, you have to strip slashes in order to remove the slashes that were added to prevent the query screwing. You might also choose to apply html_special_chars when you display it in HTML, in order to prevent things like '&' and '<' from playing merry hell with your page. Note, however, that it doesn't work in page titles or form inputs.

And there you have it, a guide to diagnosing MySQL errors and preventing them from screwing up your website. Remember that these are just guidelines and that there will be times when you might have to do things completely differently.

 
09/01/2010