How to Debug MySQL Queries in PHP
do_query() is a replacement for mysql_query in PHP, providing help with debugging of MySQL queries, and notification from your site when something goes wrong.
Usage is quite simple. First, you must define the two variables at the top of the script, client name and email address. The client name is only useful if you manage several sites, and the email address is where you wish to have error notifications send. You need to include the function itself on each page you wish to use it. Normally, a developer will have a selection of functions in one file, then include that file on every page – and that’s where to add this to.
To call it, you replace the “mysql_query” function call with “do_query” function call, and add “__LINE__” towards the end. The addition of the “__LINE__” variable at the end of the call means that rather than needing to hunt through an entire script once the system has informed you of an error, you can see exactly where it is called from. The two boxes below demonstrate, first, the usual way to execute a query and, second, how to execute a query with do_query.
$result = mysql_query("SELECT * FROM table");
$result = do_query("SELECT * FROM table", __LINE__);
If the query executes correctly, you will see no difference in the running of the site. On any error though, the system (instead of the usual message from MySQL) will display a customised error notification telling the user you have been informed of the error. The system will also email you to tell you the file name and line on which the error occurred, and send you the query that made it fail.
The other purpose of do_query is to aid in debugging of script that isn’t behaving itself, specifically by actually showing any queries on the page itself. Simply add “debug=1″ to your querystring, and any query executed on the page using do_query will be displayed, along with the number of results returned, hopefully allowing you to spend more time fixing bugs and less time hunting them down.
function do_query($sql, $line) {
$client = ""; // Client Name
$email = ""; // Email to notify on error
$result = @mysql_query($sql); Sorry, there has been an unexpected database error. The webmaster has been informed of this error.
$total = @mysql_num_rows($result);
if (@mysql_error() <> "") {
echo "
// Error number
$error_message = "
| Error Number: | " . @mysql_errno() . " |
| Error Description: | " . @mysql_error() . " |
| Error Time: | " . date("H:m:s, jS F, Y") . " |
| Client: | " . $client . " |
| Script: | " . $_SERVER["SCRIPT_NAME"] . " |
| Line: | " . $line . " |
";
// SQL
$error_message .= "
| Query: | " . $sql . " |
| Processes: | "; $result = @mysql_list_processes(); while ($row = @mysql_fetch_assoc($result)){ $error_message .= $row["Id"] . " " . $row["Command"] . " " . $row["Time"] . " "; } @mysql_free_result($result); $error_message .= " |
";
$headers = "From: \"MySQL Debug\" <" . $email . ">\r\n";
$headers .= "Content-Type: text/html; charset=ISO-8859-1\r\n";
//mail($email, "[MySQL Error] ". $client, $error_message, $headers);
die();
}
if ($_GET["debug"]=="1") {
echo "
";
}
return $result;
}
Original Author – Dave Child
Related Posts:
Tags: Debug Php INI | Debugging PHP Using Eclipse | Php Remote Debugging | Php Zend Debugger
You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.
Leave a comment.