Debugging MySQL Queries in PHP
Fri Sep 28, 2007 at 23.21
Over at what is becoming my second home a frequently asked question ultimately results in the original poster needed some guidance in debugging interaction between their PHP scripts and their database, namely MySQL. I hope to uncover some best practices for making it easier to figure out what is going on with your script.
This first snippet is a commonly occurring one and will be known as "the hard way" in terms of finding the cause of your incorrect results quickly.
while($row = @mysql_fetch_assoc($result)) {
echo $row['user_id'] . " " . $row['username'];
}
This is the hard way because we have suppressed any errors using the @ operator and assumed that there will always be results. If something goes wrong, we might be surprised, or even worse just assume that there aren't any results that match our query. The query from above is rather simple and will probably not produce an error, but once you get into more involved queries you might have a mistake or two. Which brings me to my first point: you should create your queries outside of PHP (e.g. on the command line or in a product such as MySQL Query Browser or PhpMyAdmin) and ensure that they are working before adding them to the equation in your script.
Once you have your query working correctly directly against the database add it to your script with a few additions to the above code. First we will utilize the native function mysql_error to return the error from the MySQL engine and use it to show us exactly what went wrong. This coupled with the display of the query that was actually sent to the engine will prove extremely helpful in the future. This is how a query snippet might look with some more debugging features
$result = mysql_query($sql) or die("MySQL error: " . mysql_error() . " generated by " . $sql);
In the event of a failed query, we can now see the error generated by the db engine and the actual query that caused the error. This however is not something you should find in a production environment. The reason is outside the scope of this post, but involves security by not hinting at database structure.
When handling the utilization of result sets the result set should first be verified. We did not do this in our first example we just assumed that there were rows and that the query was successful. That can also be handled better using the following snippet
echo "Database query returned an error.";
}
elseif( mysql_num_rows( $result ) == 0 ) {
echo "There were no results returned.";
}
else {
while($row = mysql_fetch_assoc( $result) ) {
echo $row['user_id'] . " " . $row['user_name'];
}
}
Naturally you will probably want to handle the cases a little differently (i.e. not just echo out those simple statements) but you get the general idea.
- Tagged:
- technicalarticles
- phpalized
- database
- mysql
- php
No comments yet, be the first!