Page 1 of 1

Database Error Handling in PHP 5 making use of Exception Handling Rate Topic: -----

#1 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3554
  • View blog
  • Posts: 10,335
  • Joined: 08-June 10

Posted 12 August 2010 - 02:31 PM

*
POPULAR

Database Error Handling in PHP 5

prerequisites

a working installation of PHP 5

Introduction

The following error message is probably one of the most encountered, when using PHP:

Quote

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in *** on line ***


The reason for that is usually no error handling at all, so that boolean false (the return value of a failed query) is passed to a mysql_fetch_* function.

The traditional way of database error handling tests the return value of each database function and kill the script, if the return value is false:

// connect
$cxn = mysql_connect($host, $user, $password) or die(mysql_error());
       mysql_connect_db($database) or die(mysql_error());

// $sql being an SQL statement, that returns a result set
$result = mysql_query($sql) or die(mysql_error());
while($row = mysql_fetch_assoc($result))
{
	// your result handling code
}



or to put it into one sentence …

Quote

Also note that it is your responsibility to die() if necessary.
(ref.)

While this indeed prevents the emission of PHP error messages, it has the great disadvantage of breaking your output. If this happens on a web page, you might end up with a half processed page, which not only looks ugly, but also exposes parts of your working code (the SQL error message, which can contain info of your database structure) to the user. Gentle users may ignore that, crackers will use that to exploit and crack your server.

A far bigger problem is when you use XML output (RSS/Atom feeds, AJAX, XHTML served as such, etc.). XML parsers have the nasty habit to quit parsing not well-formed XML and throw a parser error (the so-called “Yellow Screen of Death”), which effectively kills your application using that output.

PHP 5 — let it flow

What you need to do to prevent that is controlling the program flow without breaking the script’s output. PHP 5 with its improved OOP capabilities finally provide you with a means to do that efficiently: Exceptions.

You can imagine an Exception as a kind of error message, that travels through the function stack until it reaches the top level (the global scope of the script called in the URI). In contrast to errors, you are able to intercept an Exception at any level in your script. That is, you can fix the problem that caused the Exception. After you have caught an Exception, the script will continue processing from the point the Exception was caught, meaning you skip all the code that comes between the problematic code and the Exception catching so that this code cannot cause you any more trouble (e.g. if a failing DB connection throws an Exception, all subsequent DB handling code will not be executed and thus can’t fail too).

Coming next, I’ll show you how to implement that.

mysql-functions

Quote

If you are using MySQL versions 4.1.3 or later it is strongly recommended that you use the mysqli extension instead.
(ref.)

Please use the mysqli-extension. The mysql-extension is not developed anymore and lacks support of modern features in MySQL (see also the comparison table under the above link)

PostgreSQL, Oracle, MSSQL, IBM DB2, …

See mysqli-functions (procedural style). The approach is exactly the same, only the function names and calls differ. You may also use PDO unless you use functions not supported by the PDO API or there is no PDO-Driver available for your database.

mysqli-functions

MySQLi provides two different styles to use it: a procedural style (very similar to the mysql-functions) and an object oriented style. Use the one you prefer.

The main difference between mysql and mysqli is that mysqli-functions use the connection handle as first argument.

// procedural style
try
{
	// connect
	$cxn = mysqli_connect($host, $user, $password, $database);

	// you are not allowed to use "throw" with a comparison operator like OR
	if (!$cxn)
	{
		throw new Exception(mysqli_connect_error());
	}

	$result = mysqli_query($cxn, $sql);

	if (!$result)
	{
		throw new Exception(mysqli_error($cxn));
	}

	while($row = mysqli_fetch_assoc($result))
	{
		// your result handling code
	}
}
catch (Exception $e)
{
	echo $e->getMessage();
}

// proceed with script



// object oriented style
try
{
	// connect (create a new MySQLi object)
	$mysqli = new MySQLi($host, $user, $pass, $database);

	// $mysqli->connect_error is buggy until PHP 5.3.0
	if (mysqli_connect_error())
	{
		throw new Exception(mysqli_connect_error());
	}

	$result = $mysqli->query($sql);

	if (!$result)
	{
		throw new Exception($mysqli->error);
	}

	while($row = $mysqli->fetch_assoc($result))
	{
		// your result handling code
	}
}
catch (Exception $e)
{
	echo $e->getMessage();
}

// proceed with script



PDO

All you need to do is setting PDO’s internal error handling to Exceptions.

$pdo = new PDO($dsn, $user, $pass, $options); 
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);



That’s all.

Really.

Catching Exceptions

As I mentioned earlier, you do not need to catch exceptions immediately (like in the examples). This encourages the use of function to structure your code. However, should you fail to catch an Exception, PHP will quit with a fatal error. You can prevent that by setting a global Exception handler.

// functions.db.php
function queryBySql($sql)
{
	// connect (create a new MySQLi object) with custom predefined constants
	$mysqli = new MySQLi(DB_HOST, DB_USER, DB_PASSWORD, DB_TABLE_NAME);

	// $mysqli->connect_error is buggy until PHP 5.3.0
	if (mysqli_connect_error())
	{
		throw new Exception(mysqli_connect_error());
	}

	$result = $mysqli->query($sql);

	if (!$result)
	{
		throw new Exception($mysqli->error);
	}

	while($row = $mysqli->fetch_assoc($result))
	{
		// your result handling code (print it)
	}
}


// file.php
<html>
<!-- more HTML code -->

<?php

include "functions.db.php";

try
{
	queryBySql("SELECT `entries` FROM `blog_content` WHERE `category` = 1");
}
catch (Exception $e)
{
	// excuse for the problem (on a production server)
	// even if your code works without error, the database may be corrupted
	// at some point (e.g. a cracker deleted your database)
	'<p class="error">Sorry, we have a problem with our server.</p>';
	// get the error message (very simple)
	mail("admin@example.org", "Database Error", $e->getMessage());
}

?>
<!-- further HTML code -->
</html>



This is but a preview of what you can do with Exceptions. PHP not only has the standard Exception, but many individual types (ErrorException, SPL Exceptions, PDOException) which you can use to handle different problems. If that’s still not enough, you can even extend the native exceptions to gather more information (see also my Error Handling Tutorial).

Exceptions (as a part of PHP OOP) will reveal their full potential when you use OOP (e.g. Database Abstraction Layers).

That’s it for today.

I wish you a happy coding, Dormilich

This post has been edited by Dormilich: 15 March 2011 - 11:36 PM


Is This A Good Question/Topic? 7
  • +

Replies To: Database Error Handling in PHP 5

#2 gregwhitworth  Icon User is offline

  • Tired.
  • member icon

Reputation: 220
  • View blog
  • Posts: 1,604
  • Joined: 20-January 09

Posted 29 September 2010 - 11:20 PM

Thanks. Didn't know about the usage of mysqli either. Good to know.
Was This Post Helpful? 0
  • +
  • -

#3 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3554
  • View blog
  • Posts: 10,335
  • Joined: 08-June 10

Posted 29 September 2010 - 11:26 PM

when youíre at the verge of switching to MySQLi, take a step further and switch to PDO. itís easier to handle, esp. with Prepared Statements.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1