One of the challenges in building a website is keeping up with things that go wrong. How will you know when a database query fails? Or when the MySQL server is unavailable, but the webserver isn't?

The functionality I needed was for the website to send me an e-mail automatically when something went wrong with the database. The complete code can be downloaded here (ZIP file). It will also be posted in this article, however, I will post it in parts.

To begin, let's define the class. There are several variables that are required. Some are optional, although I do recommend you set these.

Here's what all the variables are used for:

Database connection:

$sServerUrl

Contains the url used to connect to the database. This will usually be "localhost", except when you're accessing a remote database.

$sDatabase

Contains the name of the database to use. This defaults to "test".

$sUsername

Contains the username to use when logging in to the database. Defaults to "root".

$sPassword

Contains the password to use when logging in to the database. Defaults to no password.

Database query results:

$sQuery

Contains the last query executed, no matter the result.

$iRows

Contains the number of rows returned by the query. This variable does not contain the affected rows from an INSERT or UPDATE query, but the number of rows returned by mysql_num_rows().

$sError

Contains the error returned by the database, if applicable.

$iErrNo

Contains the error number returned by the database, if applicable.

$iRowId

The ID of the auto increment column that was automatically generated by an INSERT-query. This is returned by mysql_insert_id().

$aResult

Contains the result of the query as a multidimensional array. The keys for this array are numeric, the arrays holding the results have the column names as keys.

Error handler:

$sIdentifier

Contains a string to include in the e-mail subject so you'll know which website is reporting an error. Defaults to the hostname of the server.

$sErMail

E-mail address to send error reports to. Defaults to false, meaning no error reports are sent.

$sFromName

Name to use in the FROM-header of the error reports.

$sFromMail

E-mail address to use in the FROM-header of the error reports.

The constructor:

Here's what it does. You call the class with all the arguments being optional. You can leave them out, if you want to connect to the database "test" at the server on "localhost" with username "root" and no password. Or you can fill them in, as described above.

The function checks if a persistent connection is requested, and uses mysql_pconnect() or mysql_connect() accordingly. If that fails, and an e-mail address was given to send the error reports to, an e-mail message will be sent to that address containing the filename in which the error occurred (database.class.php), the line number at which the error occurred (current line minus 5 - there's comments in the actual file, so the 5 is incorrect above), and the mysql_error() received. It will also terminate all script execution with the message "Failed to connect to the database."

If a connection was succesfully established, it will attempt to open the database using mysql_select_db(). If that fails, again, an error message is sent out and all script execution is terminated with the message "Failed to open the database."

That's it. If you want something else, you'll have to call a function on the database object. You could, for example, clean up a string so that it's relatively safe to use in a query:

	function Clean(
		$sText = null
	)
	{
		if (!$sText || is_null($sText) || $sText == null || empty($sText)) return "NULL";

		$bCouldEscape = @mysql_real_escape_string($sText);

		if (!$bCouldEscape)
		{
			if ($this->sErMail) @mail($this->sErMail, "[".$this->sIdentifier."] [ERROR] [MYSQL_REAL_ESCAPE_STRING]", __FILE__.":".(__LINE__ - 5)."\n\n".mysql_error(), "From: \"".$this->sFromName."\" <".$this->sFromMail.">\r\n");
			$sText = addslashes($sText);
		}
		else $sText = $bCouldEscape;

		return "'".$sText."'";
	}

If the string is empty, null, false or undefined, this function returns "NULL". If it isn't, mysql_real_escape_string() is called to escape the string. This function needs a MySQL-connection to work, so it could very well fail. If it did, an error message will be sent, and the string will be escaped using addslashes(), which isn't quite as good for database escaping. The escaped string will eventually be returned with a single quote in front and behind it.

Executing a query:

	function Execute(
		$sFilename,
		$iLineNumber,
		$sQuery,
		$bDebug = false
	)
	{
		$this->sQuery  = "";
		$this->iRows   = 0;
		$this->iRowId  = 0;
		$this->sError  = "";
		$this->iErrNo  = 0;
		$this->aResult = array();

		$this->sQuery = $sQuery;

		if ($bDebug) echo "<pre>".$sFileName.":".$iLineNumber.&quot\n\n&quot.__FILE__.":".(__LINE__ - 3)."\n\n".$sQuery."\n\n";

		$bIsExecuted = @mysql_query($sQuery);

		if (!$bIsExecuted)
		{
			$this->sError = @mysql_error();
			$this->iErrNo = @mysql_errno();

			if (!$bDebug)
			{
				if ($this->sErMail) @mail($this->sErMail, "[".$this->sIdentifier."] [ERROR] [MYSQL_QUERY]", __FILE__.":".(__LINE__ - 10)."\n\n".$sFileName.":".$iLineNumber."\n\n".$sQuery."\n\n".$this->sError, "From: \"".$this->sFromName."\" <".$this->sFromMail.">\r\n");
			}
			else
			{
				die($this->iErrNo.": ".$this->sError."\n\n</pre>");
			}
		}
		else
		{
			$oResultSet = $bIsExecuted;

			$this->iRows = @mysql_num_rows($oResultSet);
			$this->iRowId = @mysql_insert_id() ? @mysql_insert_id() : 0;

			if ($this->iRows > 0)
			{
				while ($aResult = @mysql_fetch_assoc($oResultSet))
				{
					$this->aResult[] = $aResult;
				}
			}

			if ($bDebug)
			{
				die(print_r($this->aResult, true)."\n\n");
			}
		}
	}
}
?>

When calling this function, $sFileName, $iLineNumber and $sQuery are required. I recommend not bothering to actually keep up with the filename and line number, because you'll probably edit the files a lot, and queries get shifted around. Fortunately, PHP comes with several built-in constants, including __FILE__ and __LINE__, which always refer to the filename and line number from which they were called. Use those.

This function first clears any previously stored results. Then it stores the query, and outputs some details if the query is to be executed in debug mode. Note: In debug mode, queries ARE executed. Be careful when debugging database altering queries, like INSERT, UPDATE, DELETE, ALTER, CREATE and cohorts.

After trying to execute the query, if it failed, the error will be stored, mailed, and outputted (if in debug mode). If the query was succesfully executed, the number of rows and, if applicable, the auto incremented id will be stored. After that, the results will be stored in the array. In debug mode, that array will be printed out and script execution will be terminated.

At this time, those are all the functions available in the class. Here's a few examples on how to implement it:

<?php
require_once("database.class.php");
$db = new Database(
	"localhost",
	"mydatabase",
	"myusername",
	"mypassword",
	true,
	"My Test Database Implementation",
	"test@rickdenhaan.net",
	"Database ErrorHandler",
	"error@rickdenhaan.net"
);

// Get the first 5 rows of mycolumn1 and mycolumn2 from mytable
$db->Execute(__FILE__, __LINE__, "
	SELECT mycolumn1,
	       mycolumn2
	FROM   mytable
	LIMIT  0,5
");

foreach ($db->aResult as $iRowNo => $aRow)
{
	echo "<strong>Row ".$iRowNo.":</strong><br />\n";
	echo "MyColumn1: ".$aRow['mycolumn1']."<br />"\n";
	echo "MyColumn2: ".$aRow['mycolumn2']."<br />\n";
}

/* Outputs the following if only two rows were available:
Row 0:
MyColumn1: value
MyColumn2: value

Row 1:
MyColumn1: value
MyColumn2: value
*/

// Retrieve a value with a WHERE clause
$db->Execute(__FILE__, __LINE__, "
	SELECT *
	FROM   mytable
	WHERE  mycolumn1 = ".$db->Clean($_GET['mycolumn1'])."
	LIMIT  0,1
");

// Single quotes are automatically put around the string by the Clean() function
?>

I'm sure many people will have questions. You can ask those in the blog post, and I will try to answer them so you can work with it.

Good luck!