PHP 5 MySQL Database Singleton

Today I pushed the source code to a MySQL "singleton pattern" database class into a git repository. This post will explain the history of this code, how it can be used, and how you can help make it better.

I have gotten a lot of feedback on a very old PHP database class I wrote for PHP4 back in 2004. Frankly, this class is flawed in several ways and very much out of date. I've been promising folks that I would post an updated, singleton pattern MySQL database wrapper for years. Here it is.

This is actually 2 PHP 5 classes: MySqlDatabase is the MySQL database singleton and MySqlResultSet is an iterable object representing a MySQL result set. I had meant to do more work on these classes, but, I simply have too much on my plate. So I've put them on GitHub so that you can use what I've finished thus far and if you want to, you can fork the project and make it better.

Get the code from the git repository

Features

The PHP5 MySQL database classes are designed to simplify the most common tasks associated with interfacing with MySQL. In a nutshell:

  • Works with default spl_autoload() function
  • Singleton design pattern
  • Shortcut methods for common SELECT queries
  • Iterable result set object for SELECT queries
  • Import local SQL files

Autoloading

These classes are named such that they will work with the default spl_autoload() function in PHP5. Simply drop them somewhere in your PHP include path and call spl_autoload_register() without any parameters. You can then use the classes without explicity including them in you code.

// automatically include class files as they are used
spl_autoload_register();

Of course, the auto loading is optional and you can still include them explicitly anywhere you use them.

include_once('mysqldatabase.php');
include_once('mysqlresultset.php');

The Singleton Design Pattern

The MySqlDatabase class is implemented using the singleton design pattern. This design pattern allows for only one single instance of the object. This one instance can then be obtained through a static method. This provides a convenience in that you can get your database object from anywhere in your code.

Some poeple may cry foul as a signleton acts very much like a global (without the name conflict problem). However, using singleton pattern for your database interfacing is convenient and quite popular. Of course, since there can be only one instance of the database object, this class only works in situations in which you need only one database connection at a time and you aren't doing connection pooling (unless you would like to modify this code to support pooling). This is the case for most of us most of the time.

// get the MySqlDatabase instance
$db = MySqlDatabase::getInstance();

Connecting to a MySQL Database

Connecting to MySQL and selecting the database can be combined since that's what I tend to need to do the majority of the time. If you do not select the database while connecting, you can do so later with a call to useDatabase().

try {
    $conn = $db->connect('localhost', 'username', 'password', 'database');
} 
catch (Exception $e) {
    die($e->getMessage());
}

Iterating a SELECT Result Set

You can easily iterate over the result set from a SELECT statement, or any statement that returns a result set, using the iterate() method. This method returns a MySqlResultSet object which uses the PHP interator interface. The MySQL resource will automatically be freed by the class destructor.

$query = "SELECT foo FROM bar LIMIT 10";
foreach ($db->iterate($query) as $row) {
    echo $row->foo;
}

SELECT Query Shortcut Methods

Several shortcut methods are provided as a convenience for some common ways we want a query's result stored in PHP. Each takes care of executing the query, fetching the data, and freeing the resource.

// get one column from one row
$count = $db->fetchOne("SELECT COUNT(*) FROM table");

// get one row 
$row = $db->fetchOneRow("SELECT foo, bar FROM table LIMIT 1");
echo $row->foo;
echo $row->table;

// get one from each row
$names = $db->fetchOneFromEachRow("SELECT name FROM table");
print_r($names);

The data returned from each of these methods, or any result set, is always represented as an object unless you explicity specify that it should be returned as an array. See the source code for more information.

// get one row as an array
$query = "SELECT foo, bar FROM table LIMIT 1";
$row = $db->fetchOneRow($query, MySqlResultSet::DATA_ARRAY);
echo $row['foo'];
echo $row['bar'];

INSERT, UPDATE, and DELETE statements

You can get the auto increment value for single INSERT statements (default) or the affected rows from a multiple row INSERT statement.

// get auto increment value
$query = "INSERT INTO foo VALUES (NULL, 'bar')";
$last_id = $db->insert($query);
echo $last_id;

// get affected rows
$query = "INSERT INTO bar VALUES (1, 'one'), (2, 'two')";
$num_rows = $db->insert($query, MySqlDatabase::INSERT_GET_AFFECTED_ROWS);
echo $num_rows; 

The delete() and update() methods will also return the affected rows.

Importing SQL Files

USE WITH CAUTION: You do not want to import SQL statements from an unknown source!

You can execute local SQL files on the server using the importSqlFile() method. This method can optionally call your own callback function for information about each statement executed.

// simple import
try {
    $filename = 'my_data.sql';
    $num = $db->importSqlFile($filename);
    echo "Imported $num statements from $filename.";
}
catch (Exception $e) {
    die($e->getMessage());
}

// import with callback function
function import_callback($line, $sql, $result) 
{
    $sql = nl2br(htmlentities($sql));
    echo "Line $line: ";
    
    if (!$result) echo 'Failed!<br/>';
    else echo 'OK.<br/>';
    
    echo "<pre>$sql</pre>";
}
try {
    $filename = 'my_data.sql';
    $num = $db->importSqlFile($filename);
    echo "Imported $num statements from $filename.";
}
catch (Exception $e) {
    die($e->getMessage());
}

Using Standard MySQL Functions

And of course you still have access to the resource identifiers you need to use the standard MySQL functions from the PHP libarary.

// get the connection resource ID
$conn = $db->link;

// get a result set resource ID
$result_set = $db->iterate("SELECT * FROM foo");
$resource_id = $result_set->getResultResource();
Did you enjoy PHP 5 MySQL Database Singleton? If you would like to help support my work, A donation of a buck or two would be very much appreciated.
blog comments powered by Disqus
Linux Servers on the Cloud IN MINUTES