Error Handling of MySQL Database Functions : PHP

The PHP interface functions to MySQL support two error-handling functions for detecting and reporting errors:

int mysql_errno(resource connection): Returns the error number of the last error on the connection resource
string mysql_error(resource connection): Returns a descriptive string of the last error on the connection resource

Example 4-5 shows the script illustrated earlier in Example 4-1 with additional error handling. We have deliberately included an error where the name of the database winestore is misspelled as "winestor". The error handler is a function, showerror( ), that-with the database name error-prints a phrase in the format:

Error 1049 : Unknown database 'winestor'

The error message shows both the numeric output of mysql_errorno( ) and the string output of mysql_error( ). The die( ) function outputs the message and then gracefully ends the script.

The functions mysql_query( ) and mysql_unbuffered_query( ) return false only on failure; that is, when a query is incorrectly formed and can't be executed.

A query that executes but returns no results still returns a result resource handle. However, a subsequent call to mysql_num_rows( ) reports no rows in the result set.

The mysql_connect( ) and mysql_pconnect( ) functions don't set either the error number or error string on failure and so must be handled manually. This custom handling can be implemented with a die( ) function call and an appropriate text message, as in Example 4-5.

Example 4-5. Querying a database with error handling

<!DOCTYPE HTML PUBLIC
               "-//W3C//DTD HTML 4.0 Transitional//EN"
               "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
  <title>Wines</title>
</head>
<body><pre>
<?php
   function showerror(  )
   {
      die("Error " . mysql_errno(  ) . " : " . mysql_error(  ));
   }
   // (1) Open the database connection
   if (!($connection = @ mysql_connect("localhost",
                                       "fred","shhh")))
      die("Could not connect");
   // NOTE : 'winestore' is deliberately misspelt to
   // cause an error
   if (!(mysql_select_db("winestor", $connection)))
      showerror(  );
   // (2) Run the query on the winestore through the
   //  connection
   if (!($result = @ mysql_query ("SELECT * FROM wine",
                                   $connection)))
      showerror(  );
   // (3) While there are still rows in the result set,
   // fetch the current row into the array $row
   while ($row = mysql_fetch_row($result))
   {
      // (4) Print out each element in $row, that is,
     // print the values of the attributes
      for ($i=0; $i<mysql_num_fields($result); $i++)
         echo $row[$i] . " ";
      // Print a carriage return to neaten the output
      echo "\n";
   }
   // (5) Close the database connection
   if (!mysql_close($connection))
      showerror(  );
?>
</pre>
</body>
</html>

The MySQL error-handling functions should be used with the @ operator that suppresses default output of error messages by the PHP script engine. Omitting the @ operator produces messages that contain both the custom error message and the default error message produced by PHP. Consider an example where the string localhost is misspelled, and the @ operator is omitted:

if (!($connection = mysql_connect("localhos",
                                  "fred",:"shhh") ))
   die("Could not connect");

This fragment outputs the following error message that includes both the PHP error and the custom error message:

Warning:  MySQL Connection Failed: Unknown MySQL Server
Host 'localhos' (0) in Example 4-5.php on line 42
Could not connect

Don't forget to add an @ operator as the prefix to any function call that is handled manually with a custom error handler. The @ operator prevents PHP from issuing its own internal error message.