Handling MySQL errors

When using PHP and MySQL together, you will likely run into a situation where you've made some sort of error in your SQL query, like misspelling a column name or a keyword or something like that. By default, PHP will not show you exactly what the problem is, only that you wrote a query which is not entirely correct. Let's try writing a faulty query to see the response from PHP:

$query = mysql_query("SELECT id, namme FROM test_users");
while($row = mysql_fetch_array($query))
    echo $row['id'] . " - " . $row["name"] . " is from " . $row["country"] . "<br />";

This is the example we are using a lot in this part of the tutorial, but in this case, we have misspelled the name column to provoke an error, which we get:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in test.php on line 7

As you can see, the error is not thrown until we try using the resource returned by the mysql_query() function, which we do when we call the mysql_fetch_array() function, in my file located on line 7. The error is very generic and not very helpful. This is on purpose, because knowledge about your database structure makes your website more vulnerable to SQL injection attacks, a problem we will discuss later on.

You might be able to spot the error and fix it in a lot of situations, but if not, you can use the mysql_error() function to get a bit more information abut the problem. This function simply returns any error returned from the last executed MySQL function. You should only use this function for finding and fixing problems, and then remove it again once the problem has been fixed. Here's the above example, but where we call the mysql_error() function to get more information:

$query = mysql_query("SELECT id, namme FROM test_users");
while($row = mysql_fetch_array($query))
    echo $row['id'] . " - " . $row["name"] . " is from " . $row["country"] . "<br />";
echo mysql_error();

This will give you a far more useful error message:

Unknown column 'namme' in 'field list'

Try making various errors in the SQL query and see the message that MySQL returns through the mysql_error() function, to see how it responds. This will help you identify SQL errors better in the future. The above one is very precise and easy to fix, while others can be a bit more cryptic to the untrained eye.