Error handling, which is important in any script, is even more of an issue when dealing with databases, since the probability for errors will increase dramatically. Common errors you will encounter are
Failure in connecting to a database
Failure in selecting a database
Inability to run a query
No results being returned by a query
Unexpected results being returned by a query
Experience will teach you why these errors normally occur, but immediately seeing what the problem is in your scripts can save you much debugging time. Depending upon your PHP installation, if you have a problem with MySQL, the result may be a blank page, which is no help at all!
To have your scripts give informative reports about errors that occur, you make use of the mysqli_error() and mysqli_errno() functions. The former will print a textual version of the error that MySQL returned, and the latter will return the corresponding number. Both take the database connection as their only argument.
Connection errors are handled differently, though. To report on these use mysqli_connect_error().
Along with these functions are two PHP terms you can use for error handling: @ and die(). The @, when used preceding a function name, will suppress any error messages or warnings the function might invoke, while die() will terminate the execution of a script and send any message within the parentheses to the Web browser.
PHP's display_errors setting dictates whether or not errors, when they occur, are shown in the Web browser. The current default is that they not be shown, which means that any error results in a blank or partially blank screen. In such cases, if you use any of the mysqli functions to report a MySQL error, you'll then see the information you need.
However, if display_errors is on, then you'll see PHP's error message and the message created by the mysqli error function. In theses cases, you might want to prepend any potentially problematic function calls with @, which will suppress the PHP error. You'll see this in action in the edit_expense.php script.
As a rule of thumb, use die() with any function whose successful execution is mandated by the remainder of the script (such as connecting to MySQL and selecting the database). Use @ with functions that might be problematic but would not necessitate stopping the script.
To use error handling:
Open mysqli_connect.inc.php (Script 7.1) in your text editor.
Change the connection code (line 23) to include die()
$dbc = @mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME) or die (`Could not connect
to MySQL: ` . mysqli_connect_error());
This code will first attempt to connect to MySQL. If it fails to do so, any errors that might otherwise be displayed are suppressed by the @
. In such a case, the or die()
section will kick in, where the error will be handled more gracefully.
Since the die()
function can take any string within its parentheses, you can make your error messages as descriptive or customized as you'd like. Here I'm using mysqli_connect_error()
along with a text string, but I could also add HTML formatting to the code.
Save the file and upload it to your Web server.
Once again, for ease of reference, I'll be renaming my script as
Script 7.5. In the new version of mysqli_connect.inc.php, I've added different error-handling techniques for a more professional result.
3 // ***** mysqli_connect2.inc.php *****
4 // ***** Script 7.5 *****
5 // Developed by Larry E. Ullman
6 // MySQL: Visual QuickStart Guide
7 // SECOND EDITION
8 // Contact: mysql2@DMCinsights.com
9 // Created: February 15, 2006
10 // Last modified: February 15, 2006
11 // This file contains the database access information
12 // for the accounting database.
13 // This file also establishes a connection to MySQL
14 // and selects the accounting database.
16 // Database-specific information:
17 DEFINE ('DB_USER', 'username');
18 DEFINE ('DB_PASSWORD', 'password');
19 DEFINE ('DB_HOST', 'localhost');
20 DEFINE ('DB_NAME', 'accounting');
22 // Connect to MySQL and select the database:
23 $dbc = @mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME) or die ('Could not
connect to MySQL: ' .mysqli_connect_error());
Test how these changes affect the execution of your scripts by altering the DB_USER
, or DB_NAME
value to purposefully create errors (Figures 7.17 and 7.18).
Figure 7.17. The or die() construct terminates a script if no database connection can be made and reports upon the error.
Figure 7.18. If the script could not select the proper database, a plain message is stated and the script's execution is halted.
Appendix A, "Troubleshooting," covers common MySQL errors in more detail, along with common causes.
As mentioned in a sidebar earlier in the chapter, the best method of debugging PHP scripts that interact with MySQL is to use the mysql clients to confirm what results you should be seeing.
Another common problem with PHP and MySQL comes from not referring to the returned results properly. If you refer to columns by name, you have to do so in an exact, case-sensitive manner. If you use an alias in your query, you must use that alias in PHP.