MySQL database wrapper for PHP

Overdue update: March 6th, 2009: new version available, not backwards-compatible with earlier version

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, the class requires a settings file called settings.database.php, in the same folder as the class file itself. This file contains the database connection details:

1
2
3
4
5
6

<?php
$sDbServerName = "localhost";
$sDbDatabase   = "test";
$sDbUsername   = "root";
$sDbPassword   = "";
?>

You will need to modify these four variables to be able to connect to your database. The variable names are plain enough. You set the server to connect to, the name of the database to use, and the username and password to use when logging in to the MySQL-server.

Now let’s define the class.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

<?php
class Database
{
	private $sErrorMail = "webmaster@example.com";
	private $rDb;
	private $rResult;
	private $sServerUrl;
	private $sDatabase;
	private $sUsername;
	private $sPassword;
	public $sQuery = "";
	public $iRows = 0;
	public $sError = "";
	public $iErrNo = 0;
	public $iRowId = 0;
	public $aResult = array();

Here’s what all the variables are used for:

Error reporting:

$sErrorMail

When a query failes, or it is not possible to connect to the database, an error message with debugging information is automatically sent to the e-mail address entered here.

Internal resources:

$rDb

This variable will store a reference to the database connection.

$rResult

This variable will store a reference to the raw data returned when a query is executed.

Private database connection details (not accessible from outside the class):

$sServerUrl

MySQL server hostname to connect to.

$sDatabase

Name of the database to use.

$sUsername

Username to log in with when connecting to the server.

$sPassword

Password to log in with when connecting to the server.

Public variables (accessible from outside the class):

$sQuery

The last query that was executed.

$iRows

The number of rows resulting from (or affected by) the last executed query.

$sError

Error message (if any) returned by the last query.

$iErrNo

The error number (if any) returned by the last query.

$iRowId

The primary key ID of the row inserted by the last query (if that was an INSERT query).

$aResult

An associative array containing all rows returned by the last query.

The constructor:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78

	public function __construct(
		$bPersist   = false,
		$sServerUrl = false,
		$sDatabase  = false,
		$sUsername  = false,
		$sPassword  = false
	)
	{
		if ($sServerUrl) $this->sServerUrl = $sServerUrl;
		if ($sDatabase)  $this->sDatabase  = $sDatabase;
		if ($sUsername)  $this->sUsername  = $sUsername;
		if ($sPassword)  $this->sPassword  = $sPassword;
 
		if (empty($this->sServerUrl))
		{
			include(dirname(__FILE__)."/settings.database.php");
			$this->sServerUrl = $sDbServerName;
			$this->sDatabase  = $sDbDatabase;
			$this->sUsername  = $sDbUsername;
			$this->sPassword  = $sDbPassword;
		}
 
		if ($bPersist)
		{
			$bIsConnected = @mysql_pconnect($this->sServerUrl, $this->sUsername, $this->sPassword);
 
			if (!$bIsConnected)
			{
				$bIsConnected = @mysql_connect($this->sServerUrl, $this->sUsername, $this->sPassword);
 
				if (!$bIsConnected)
				{
					$this->sError = @mysql_error();
					$this->iErrNo = @mysql_errno();
 
					@mail($this->sErrorMail, "[{$_SERVER['SERVER_NAME']}] Connection failure", "Failed to connect to the database:\n\n{$this->iErrNo}: {$this->sError}", "From: ErrorHandler <errors@example.com>\r\n");
					trigger_error("Failed to connect to the database (error #{$this->iErrNo})", E_USER_ERROR);
				}
				else
				{
					$this->rDb = $bIsConnected;
				}
			}
			else
			{
				$this->rDb = $bIsConnected;
			}
		}
		else
		{
			$bIsConnected = @mysql_connect($this->sServerUrl, $this->sUsername, $this->sPassword);
 
			if (!$bIsConnected)
			{
				$this->sError = @mysql_error();
				$this->iErrNo = @mysql_errno();
 
				@mail($this->sErrorMail, "[{$_SERVER['SERVER_NAME']}] Connection failure", "Failed to connect to the database:\n\n{$this->iErrNo}: {$this->sError}", "From: ErrorHandler <errors@example.com>\r\n");
				trigger_error("Failed to connect to the database (error #{$this->iErrNo})", E_USER_ERROR);
			}
			else
			{
				$this->rDb = $bIsConnected;
			}
		}
 
		$bIsReady = @mysql_select_db($this->sDatabase, $this->rDb);
 
		if (!$bIsReady)
		{
			// Set the errors
			$this->sError = @mysql_error();
			$this->iErrNo = @mysql_errno();
 
			@mail($this->sErrorMail, "[{$_SERVER['SERVER_NAME']}] Selection failure", "Failed to select the database:\n\n{$this->iErrNo}: {$this->sError}", "From: ErrorHandler <errors@example.com>\r\n");
			trigger_error("The database could not be opened (error #{$this->iErrNo})", E_USER_ERROR);
		}
	}

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 using the settings provided in the settings.database.php file. 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 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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

	function Clean(
		$sText = null
	)
	{
		if (ctype_digit($sText)) return "'{$sText}'";
		else if (empty($sText)) return "''";
		else if (!$sText || is_null($sText) || $sText == null) return "NULL";
 
		$bCouldEscape = @mysql_real_escape_string($sText, $this->rDb);
 
		if (!$bCouldEscape)
		{
			$sText = addslashes($sText);
		}
		else $sText = $bCouldEscape;
 
		return "'".$sText."'";
	}

If the string is numerical, it will be returned unchanged, as a string, including single quotes. This is to prevent 0 being returned as NULL or a blank string. Otherwise, if the string is in fact an empty string, an empty string, including single quotes, is returned. Or, if the string is a boolean false or a null value, the string NULL is returned, without quotes. MySQL should see that as a null value. For any other values, the string is escaped using mysql_real_escape_string(), which needs a MySQL-connection to work, so it could very well fail. If it does, 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, so if you use this function, be sure not to include those in your query.

Executing a query:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62

	function Execute(
		$sQuery,
		$sFilename   = null,
		$iLineNumber = null,
		$bDebug      = false
	)
	{
		$this->rResult = null;
		$this->sQuery   = "";
		$this->iRows    = 0;
		$this->iRowId   = 0;
		$this->sError   = "";
		$this->iErrNo   = 0;
		$this->aResult  = array();
 
		if (empty($sFilename)) $sFilename     = $_SERVER['REQUEST_URI'];
		if (empty($iLineNumber)) $iLineNumber = "(??)";
 
		$this->sQuery = $sQuery;
 
		if ($bDebug) echo "<pre>{$sFilename}:{$iLineNumber}\n\n".__FILE__.":".(__LINE__ - 3)."\n\n{$sQuery}\n\n";
 
		$bIsExecuted = @mysql_query($sQuery, $this->rDb);
 
		if (!$bIsExecuted)
		{
			$this->sError = @mysql_error($this->rDb);
			$this->iErrNo = @mysql_errno($this->rDb);
 
			@mail($this->sErrorMail, "[{$_SERVER['SERVER_NAME']}] Query failure", "Failed to properly execute a query in {$sFilename} on line {$iLineNumber}.\n\n{$sQuery}\n\n{$this->iErrNo}: {$this->sError}", "From: ErrorHandler <errors@example.com>\r\n");
 
			if ($bDebug)
			{
				die($this->iErrNo.": ".$this->sError."\n\n</p"."re>");
			}
		}
		else
		{
			$this->rResult = $bIsExecuted;
			$this->iRows = 0;
			$type = 0;
 
			if (($this->iRows = @mysql_num_rows($this->rResult))) { $type = 1; }
			else if (($this->iRows = @mysql_affected_rows($this->rDb))) { $type = 2; }
 
			$this->iRowId = 0;
			if (($this->iRowId = @mysql_insert_id($this->rDb))) {}
 
			if ($this->iRows > 0 && $type == 1)
			{
				while ($aResult = @mysql_fetch_assoc($this->rResult))
				{
					$this->aResult[] = $aResult;
				}
			}
 
			if ($bDebug)
			{
				die(print_r($this->aResult, true)."\n\n</p"."re>");
			}
		}
	}

When calling this function, $sQuery is required, but $sFileName, $iLineNumber and $bDebug are optional. The latter three are very handy for debugging purposes, however, 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 determines the current file from the requested URL. Next 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.

Changing the error reporting e-mail address at runtime:

1
2
3
4

	public function setErrorMail($sEmail)
	{
		$this->sErrorMail = $sEmail;
	}

It is possible to change the error message recipient during runtime. This is useful when, for example, an application has been built by one programmer, and another one is jumping in to add or modify a feature. If a global database object is used in the project, this will allow the new developer to make sure his code will send errors to him, instead of to the original developer.

Finding out the current error reporting e-mail address:

1
2
3
4
5
6

	public function getErrorMail()
	{
		return $this->sErrorMail;
	}
}
?>

Using this function, it is possible to find out what e-mail address is currently being used to deliver error reports to. Very useful if the setErrorMail() function is used a lot.

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

<?php
require_once("class.Database.php");
$oDb = new Database();
 
// Get the first 5 rows of mycolumn1 and mycolumn2 from mytable
$oDb->Execute("
	SELECT mycolumn1,
	       mycolumn2
	FROM   mytable
	LIMIT  0,5
", __FILE__, __LINE__);
 
foreach ($oDb->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
$oDb->Execute("
	SELECT *
	FROM   mytable
	WHERE  mycolumn1 = ".$oDb->Clean($_GET['mycolumn1'])."
	LIMIT  0,1
", __FILE__, __LINE__);
 
// 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!