How to Handle MySQL Errors

You use the mysqli functions of the PHP language, such as mysqli_connect and mysqli_query, to interact with the MySQL database. Things will sometimes go wrong when you use the statements. You may make an error in your typing, such as mistyping a database name. Sometimes, problems arise that you can't avoid, such as the database or the network being down.

You need to include code in your script that handles error situations.

You usually want to make your error handling more descriptive to assist with troubleshooting problems during development, but you don't want the extra information displayed to the public.

For instance, suppose that you're using an account called root to access your database and you make a typo, as in the following statements:

$host = "localhost";
$user = "rot";
$password = "";
$cxn = mysqli_connect($host,$user,$password)

Because you type "rot" rather than "root", you see a warning message similar to this one:

Warning: Access denied for user: 'rot@localhost' (Using password: NO) ...

The preceding error message contains the information that you need to figure out the problem — it shows your account name that includes the typo.

However, after your script is running and customers are using it, you don't want your users to see a technical error message that shows your user ID. You want to turn the PHP errors off or send them to an error log file. You could then use a die statement to stop the script and display a polite message to the user, as follows:

$cxn = mysqli_connect($host,$user,$password)
   or die("The Catalog is not available at the moment. Please try again later.");

When a mysqli_query() function fails, MySQL returns an error message that contains information about the cause of the failure. However, this message isn't displayed unless you specifically display it. Again, you may want to see these messages when you're developing the script, but you may not want to display them to the public. You can display the MySQL error that's returned by using the following function:

mysqli_error($cxn)

For example, you might include the function in your code, as follows:

$query = "SELECT * FROM Cust";
$result = mysqli_query($cxn,$query)
           or die ("Error: ".mysqli_error($cxn));

In this example, if the function call fails, the die statement displays the MySQL error, which might be something like this:

Error: Table 'catalog.cust' doesn't exist

Occasionally, you may want to perform additional actions if the function fails, such as delete variables or close the database connection. You can perform such actions by using a conditional statement:

if(!$result = mysqli_query($cxn,$query))
{
    echo mysqli_error($cxn);
    unset($auth);
    exit();
}

If the function call fails, the statements in the if block are executed. The echo statement displays the MySQL error returned by the function. A variable is removed, and the script exits.

Notice the ! (exclamation point) in the if statement. ! means "not". In other words, the if statement is true if the assignment statement is not true.

  • Add a Comment
  • Print
  • Share
blog comments powered by Disqus
Advertisement

Inside Dummies.com

Dummies.com Sweepstakes

Win $500. Easy.