TPC! Web Development Home

Creating a MySQL Database Class Using the Singleton Design Pattern

From TPC! Web Development

Contents

Introduction

Using the Singleton Pattern is a simple way to limit the number of instances of an object to just one, helps keep database connections organized, and saves memory. We will save details on the abstractions of the Singleton Pattern, and limit explanation to the code on this page used to create the mysqli_database object.

MySQL database class code

The code below is a basic example of how to use the Singleton Pattern to create a database class. This concept makes it easy to stay organized and use a centralized object for all of your database connections. Keep in mind that this is only a very basic level example, and there are many things that can be done to improve this class. Some functionality we will add in later parts of this tutorial include:

  • Multiple database connections (simultaneous)
  • Result caching 
<?php
/**
 * MySQLi database class using Singleton Pattern
 *
 * @package tpc_tutorials
 * @version 0.1.1 20090311T19:4800-05:00
 * @author  Chris Strosser
 * @link    http://dev.thatspoppycock.com/
 *
 * @use	This class requires PHP5 to use.
 *
 *	$db = mysqli_database::instance();
 *	$db->connect(DB_HOSTNAME, DB_USERNAME, DB_PASSWORD, DB_DATABASE);
 *	$result = $db->query("SELECT * FROM example WHERE id = '1'");
 *	$db->close();
 *
 */
class mysqli_database {
	/**
	 * Database connection object
	 *
	 * @access private
	 * @var object
	 */
	private $mysqli;
 
	/**
	 * Instance of database object
	 *
	 * @access private
	 * @var object
	 */
	private static $db_instance;
 
	/**
	 * Constructor
	 *
	 * @access private
	 * @note Making the constructor private blocks this from being directly created
	 */
	private function __construct() {}
 
	/**
	 * Creates/references database class instance
	 *
	 * @access public
	 * @return object
	 */
	public static function instance() {
		if (!self::$db_instance) {
			self::$db_instance = new mysqli_database();
		}
		return self::$db_instance;
	}
 
	/**
	 * Connect to the MySQL database
	 *
	 * @access public
	 * @param string $host		Database hostname
	 * @param string $user		Database username
	 * @param string $password	Database password
	 * @param string $name		Database name
	 * @return void
	 */
	public function connect($host, $user, $password, $name) {
		# Connect to database
		$this->mysqli = new mysqli($host, $user, $password, $name);
		# Check connection
		if (mysqli_connect_errno()) {
			printf("Error connecting to database: %s", mysqli_connect_error());
			exit();
		}
	}
 
	/** 
	 * Close connection to MySQL database
	 *
	 * @access public
	 * @return void
	 */
	public function close() {
		$this->mysqli->close();
	}
 
	/**
	 * Execute query and return results
	 *
	 * @access public
	 * @param string $sql MySQL statement to execute
	 * @return mixed
	 */
	public function query($sql) {
		if (!$result = $this->mysqli->query($sql)) {
			return false;
		} else {
			return $result;
		}
	}
 
	/**
	 * Make data safe for database
	 *
	 * @access public
	 * @param string $data Data to be escaped
	 * @return string Sanitized data
	 */
	public function escape($data) {
		return $this->mysqli->real_escape_string($data);
	}
 
	/**
	 * Prevent cloning of this class
	 *
	 * @access public
	 * @return void
	 */
	public function __clone() {
		# Issue E_USER_ERROR if clone attempted
		trigger_error('Cloning <em>mysqli_database</em> is forbidden.', E_USER_ERROR);
	}
 
	/**
	 * Destructor (Close database connection)
	 *
	 * @access public
	 * @return void
	 */
	public function __destruct() {
		$this->mysqli->close();
	}
}
?>

How to use

# Reference mysqli_database object
$db = mysqli_database::instance();
 
# Connect to MySQL database
$db->connect($hostname, $username, $password, $database);
 
# Execute MySQL query
$db->query("SELECT * FROM example WHERE id = '1'");
 
# Close database connection
$db->close();

Explanation

If you've never used the Singleton Pattern before, you may a different syntax is used. You might be used to creating database objects using the syntax listed below: 

$db = new database();
$db->query( /* ... query ... */ );
$db->close();

One potential problem with this approach is that each time we create a new database object, we allocate more and more memory to those operations. In some cases, it might make sense to do it that way, but a lot of times we only need one instance. This will help keep things organized and save memory. The syntax we will use to reference this class is as follows: 

$db = mysqli_database::instance();
 
# Connect to database
$db->connect( /* ... DB credentials ... */ );
 
# Perform query
$db->query( /* ... query ... */ );
 
# Close database connection
$db->close();

To prevent direct creation of this object, we make the constructor private. This means that the class can only be instantiated from inside of itself. 

private function __construct() {}

The $db_instance property of this class is where we store the object. This property must always be static: 

private static $db_instance;

The mysqli_database::instance() function is the method used to return the object reference, which must also be static. The first part of the function handles checking/creating the object: 

# Check if mysqli_database instance has already been created
if (!self::$db_instance) {
	# If mysqli_database hasn't been created, create...
	self::$db_instance = new mysqli_database();
}

On major improvement with the introduction of PHP5, which really pushed PHP into the land of object-oriented programming (OOP), involves object reference. In PHP5, objects are always passed by reference. The next part of the mysqli_database::instance() function returns the object reference so that we can use the class in our application: 

return self::$db_instance;

Comments

Leave a Comment

Creating a MySQL Database Class Using the Singleton Design Pattern

Retrieved from "http://dev.thatspoppycock.com/index.php/Creating_a_MySQL_Database_Class_Using_the_Singleton_Design_Pattern"