Debugging SQL

When a PHP call to the MySQL interface encounters a database error, the warnings displayed are not always as helpful as you might hope. In the following sections you will find out how to make the most of MySQL's error reporting to debug errors at the database level.

SQL Errors

When there is an error in a SQL statement, it is not reported right away. You should check the return value from mysql_query to determine whether there was a problemit is NULL if the query has failed for any reason. This applies to DDL and DML statements as well as to SELECT queries.

The following example tries to perform an invalid SQL statement (the table name is missing from the DELETE command):

 $sql = "DELETE FROM"; $res = mysql_query($sql, $db); if (!$res) {   echo "There was an SQL error";   exit; } 

If you want to find out why a call to mysql_query failed, you must use the mysql_error and mysql_errno functions to retrieve the underlying MySQL warning text and error code number. A link resource argument can be provided but is required only if you have two or more open MySQL connections in the script:

 if (!$res) {   echo "Error " . mysql_errno() . " in SQL ";   echo "<PRE>$sql</PRE>";   echo mysql_error();   exit; } 

Debugging SQL When you're debugging SQL, it is useful to see the query that was attempted alongside the error message, particularly if your query uses variable substitutions. This is easy to do if the query is stored in a variablesuch as $sql used throughout this lessonrather than given directly as an argument to mysql_query.

If you do not trap SQL errors in script, PHP will continue to execute until an attempt is made to use the failed result resource. You will see an error message similar to the following if, for instance, mysql_result is called with an invalid $res value:

 Warning: mysql_result(): supplied argument is not a valid MySQL result resource in /home/chris/mysql.php on line 8 

This error does not give any indication of what the problem was, or even when in the script it occurred. The line number given is the line of the mysql_result call, not mysql_query, so you have to search upward in the script to find the root of the problem.

Connection Errors

If an error occurs during connection to a MySQL database, a PHP error is displayed onscreen, similar to the following, which were caused by an invalid password and a mistyped hostname, respectively:

 Warning: mysql_connect(): Access denied for user 'root'@'localhost'  (using password: YES) in /home/chris/connect.php on line 3 Warning: mysql_connect(): Unknown MySQL server host 'local-host'  (1) in /home/chris/connect.php on line 3 

These warnings are generated by PHP and are adequately descriptive. If you want, you can view the actual MySQL error message and error code by using mysql_error and mysql_errno.

For instance, if you have stopped PHP warnings from being displayed onscreenyou will learn how to do this in Lesson 23it might be useful to output this information or write it to a log file. You can detect that the connection attempt failed because the link resource is NULL.

The following code checks that a connection has been successful before continuing, and it displays the reason for failure, if appropriate:

 $db = mysql_connect("localhost", "chris", "mypassword"); if (!$db) {   echo "Connection failed with error " .                 mysql_errno() . "<br>";   echo "Warning: " . mysql_error();   exit; } 

Passwords Neither the PHP warning nor the message from mysql_error contains the password used when the reason for failure is an invalid logon attempt.