Debug MySQL Queries in PHP

This post has been archived. It's probably out of date or shameful in some other way.

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);
$total = @mysql_num_rows($result);
if (@mysql_error() &lt;&gt; "") {
    echo "&nbsp;&lt;br&gt;&lt;font face=\"Verdana\" size=\"1\"&gt;&lt;small&gt;&lt;b&gt;&lt;p align=\"center\"&gt;Sorry, there has been an unexpected database error. The webmaster has been informed of this error.&lt;/p&gt;&lt;/b&gt;&lt;/small&gt;&lt;/font&gt;";

    // Error number

    $error_message = "&lt;table border=\"0\" cellpadding=\"3\" cellspacing=\"1\" style=\"border: 1px solid #bbbbbb;\" bgcolor=\"#ffffff\" width=\"80%\" align=\"center\"&gt;&lt;tr&gt;&lt;td align=\"right\" width=\"25%\"&gt;&lt;font face=\"Verdana\" size=\"1\"&gt;&lt;small&gt;&lt;b&gt;Error Number:&lt;/b&gt;&lt;/small&gt;&lt;/font&gt;&lt;/td&gt;&lt;td width=\"75%\"&gt;&lt;font face=\"Verdana\" size=\"1\"&gt;&lt;small&gt;" . @mysql_errno() . "&lt;/small&gt;&lt;/font&gt;&lt;/td&gt;&lt;/tr&gt;";

    // Error Description

    $error_message .= "&lt;tr&gt;&lt;td align=\"right\"&gt;&lt;font face=\"Verdana\" size=\"1\"&gt;&lt;small&gt;&lt;b&gt;Error Description:&lt;/b&gt;&lt;/small&gt;&lt;/font&gt;&lt;/td&gt;&lt;td&gt;&lt;font face=\"Verdana\" size=\"1\"&gt;&lt;small&gt;" . @mysql_error() . "&lt;/small&gt;&lt;/font&gt;&lt;/td&gt;&lt;/tr&gt;";

    // Error Date / Time

    $error_message .= "&lt;tr&gt;&lt;td align=\"right\"&gt;&lt;font face=\"Verdana\" size=\"1\"&gt;&lt;small&gt;&lt;b&gt;Error Time:&lt;/b&gt;&lt;/small&gt;&lt;/font&gt;&lt;/td&gt;&lt;td&gt;&lt;font face='Verdana' size='1'&gt;&lt;small&gt;" . date("H:m:s, jS F, Y") . "&lt;/small&gt;&lt;/font&gt;&lt;/td&gt;&lt;/tr&gt;";

    // Client

    $error_message .= "&lt;tr&gt;&lt;td align=\"right\"&gt;&lt;font face=\"Verdana\" size=\"1\"&gt;&lt;small&gt;&lt;b&gt;Client:&lt;/b&gt;&lt;/small&gt;&lt;/font&gt;&lt;/td&gt;&lt;td&gt;&lt;font face=\"Verdana\" size=\"1\"&gt;&lt;small&gt;" . $client . "&lt;/small&gt;&lt;/font&gt;&lt;/td&gt;&lt;/tr&gt;";

    // Script

    $error_message .= "&lt;tr&gt;&lt;td align=\"right\"&gt;&lt;font face=\"Verdana\" size=\"1\"&gt;&lt;small&gt;&lt;b&gt;Script:&lt;/b&gt;&lt;/small&gt;&lt;/font&gt;&lt;/td&gt;&lt;td&gt;&lt;font face=\"Verdana\" size=\"1\"&gt;&lt;small&gt;" . $_SERVER["SCRIPT_NAME"] . "&lt;/small&gt;&lt;/font&gt;&lt;/td&gt;&lt;/tr&gt;";

    // Line Number

    $error_message .= "&lt;tr&gt;&lt;td align=\"right\"&gt;&lt;font face=\"Verdana\" size=\"1\"&gt;&lt;small&gt;&lt;b&gt;Line:&lt;/b&gt;&lt;/small&gt;&lt;/font&gt;&lt;/td&gt;&lt;td&gt;&lt;font face=\"Verdana\" size=\"1\"&gt;&lt;small&gt;" . $line . "&lt;/small&gt;&lt;/font&gt;&lt;/td&gt;&lt;/tr&gt;&lt;/table&gt;";

    // SQL

    $error_message .= "&lt;table border=\"0\" cellpadding=\"3\" cellspacing=\"1\" style=\"border: 1px solid #bbbbbb;\" bgcolor=\"#ffffff\" width=\"80%\" align=\"center\"&gt;&lt;tr&gt;&lt;td align=\"right\"&gt;&lt;font face=\"Verdana\" size=\"1\"&gt;&lt;small&gt;&lt;b&gt;Query:&lt;/b&gt;&lt;/small&gt;&lt;/font&gt;&lt;/td&gt;&lt;td&gt;&lt;font face=\"Verdana\" size=\"1\"&gt;&lt;small&gt;" . $sql . "&lt;/small&gt;&lt;/font&gt;&lt;/td&gt;&lt;/tr&gt;";

    $error_message .= "&lt;tr&gt;&lt;td align=\"right\" valign=\"top\" width=\"25%\"&gt;&lt;font face=\"Verdana\" size=\"1\"&gt;&lt;small&gt;&lt;b&gt;Processes:&lt;/b&gt;&lt;/small&gt;&lt;/font&gt;&lt;/td&gt;&lt;td&gt;&lt;font face=\"Verdana\" size=\"1\"&gt;&lt;small&gt;";

    $result = @mysql_list_processes();
    while ($row = @mysql_fetch_assoc($result)){
        $error_message .= $row["Id"] . " " . $row["Command"] . " " . $row["Time"] . "&lt;br&gt;";
    }
    @mysql_free_result($result);

    $error_message .= "&lt;/small&gt;&lt;/font&gt;&lt;/td&gt;&lt;/tr&gt;&lt;/table&gt;";

    $headers = "From: \"MySQL Debug\" &lt;" . $email . "&gt;\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 "&lt;div width=\"100%\" style=\"margin: 10px; margin-bottom: 0; padding: 3px; border: 1px solid #ff0000; background-color: #ffffff; font: 10px verdana; overflow: auto;\"&gt;".$sql."&lt;/div&gt;&lt;div width=\"100%\" style=\"margin: 10px; margin-top: 0; padding: 3px; border: 1px solid #ff0000; background-color: #ff0000; font: 10px verdana; color: #ffffff; font-weight: bold;\"&gt;" . $total . " rows found.&lt;/div&gt;";
}
return $result;

}

7 January 2004

Debug MySQL Queries in PHP

Dave Child

Latest Posts

Recommended