MySQL database class using Singleton design pattern

(Note: The following is a revised tutorial I wrote earlier this year, transferred from one of my old web sites.)

Using the Singleton design pattern is a simple way to limit the number of class instances to one, helps keep database connections organized, and can also save memory.  The PHP code below is a basic example on how to use the Singleton pattern to create a database class.  This class can be used to manage a database connection throughout PHP script execution.  While this is a basic example, many improvements have been made over the original version.  If this is well received, we will be happy to add additional functionality. Click read more to view the class, and learn about the functionality of each part.

The $wjdb class

/**
 * PHP5 MySQL Database Class implementing the Singleton Design Pattern
 * 
 * Usage:
 * 	$wjdb = wjdb::instance();
 *  $wjdb->connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
 *  $results = $wjdb->query("SELECT * FROM example_db WHERE id = 1");
 *  $db->close();
 * 
 * @package WJ_Tutorials
 * @author Chris Strosser
 * @link http://webjawns.com/
 */
class wjdb {
	/**
	 * The wjdb database object
	 * 
	 * @access private
	 * @var object
	 */
	private $wjdb;
	
	/**
	 * MySQLi database object
	 * 
	 * @access private
	 * @var object
	 */
	private static $instance;
	
	/**
	 * Current result set
	 * 
	 * @access private
	 * @var object
	 */
	private $result;
	
	/**
	 * The last result (processed)
	 * 
	 * @access private
	 * @var array
	 */
	private $last_result;
	
	/**
	 * The number of rows from last result
	 * 
	 * @access private
	 * @var int
	 */
	private $row_count;
	
	/**
	 * Last error
	 * 
	 * @access private
	 * @var string
	 */
	private $last_error;
	
	/**
	 * PHP5 Constructor
	 * 
	 * Making this function 'private' blocks this class from being directly created.
	 * 
	 * @access private

	 */
	private function __construct() { }
	
	/**
	 * Creates and references the wjdb object.
	 * 
	 * @access public
	 * @return object MySQLi database object
	 */
	public static function instance() {
		if ( !self::$instance )
			self::$instance = new wjdb();
		return self::$instance;
	}
	
	/**
	 * Connect to the MySQL database.
	 * 
	 * @param string $host MySQL hostname
	 * @param string $user MySQL username
	 * @param string $password MySQL password
	 * @param string $name MySQL database name
	 * @return bool True if successful, false on error.
	 */
	public function connect($host, $user, $password, $name) {
		// Connect to the database
		$this->wjdb = new mysqli($host, $user, $password, $name);
		// Check connection
		if ( mysqli_connect_errno() ) {
			$this->last_error = mysqli_connect_error();
			return false;
		}
		return true;
	}
	
	/**
	 * Checks for errors.
	 * 
	 * @return string|false $last_error if it exists or false if no errors.
	 */
	public function is_error() {
		if ( isset($this->last_error) && !empty($this->last_error) )
			return $this->last_error;
		return false;
	}
	
	/**
	 * Close active connection to MySQL database.
	 * 
	 * @access public
	 * @return bool Always returns true.
	 */
	public function close() {
		if ( $this->wjdb )
			$this->wjdb->close();
		return true;
	}
	
	/**
	 * Executes query and returns results.
	 * 
	 * @access public
	 * @param string $sql The SQL statement to execute.
	 * @return mixed
	 */
	public function query($sql) {
		$this->result = $this->wjdb->query($sql);
		return $this->result;
	}
	
	public function get_results($sql) {
		if ( !$this->query($sql) )
			return false;
		
		$num_rows = 0;
		while ( $row = $this->result->fetch_object() ) {
			$this->last_result[$num_rows] = $row;
			$num_rows++;
		}
		
		$this->result->close();
		
		return $this->last_result;
	}
	
	public function num_rows() {
		return (int) $this->row_count;
	}
	
	/**
	 * Retrieve a single row from the database.
	 * 
	 * Do not include LIMIT 1 on the end, as this will be taken care
	 * of automatically.
	 * 
	 * @param string $sql The SQL statement to execute.
	 * @return object The MySQL row object
	 */
	public function get_row($sql) {
		if ( !$results = $this->query($sql . " LIMIT 1") )
			return false;
		
		return $results->fetch_object();
	}
	
	/**
	 * Sanitizes data for safe execution in SQL query.
	 * 
	 * @access public
	 * @param mixed $data The data to be escaped.
	 * @return mixed
	 */
	public function escape($data) {
		return $this->wjdb->real_escape_string($data);
	}
	
	/**
	 * Prevent cloning of wjdb.
	 * 
	 * @access public
	 * @return void
	 */
	public function __clone() {
		// Issue E_USER_ERROR if clone is attempted
		trigger_error('Cloning <em>wjdb</em> is prohibited.', E_USER_ERROR);
	}
	
	/**
	 * Destructor
	 * 
	 * @access public
	 */
	public function __destruct() {}
}

Differences in syntax

For those who have never used the Singleton design pattern before, it is important to know that the syntax used to create the object is different.  In the Singleton design pattern, the class itself actually creates the instance, and returns a reference.

$db = new database();

One potential problem with the “old” approach is that it still allows for multiple connections to the same database, when only one is needed.  This causes unnecessary memory usage and extra load on the server.  The syntax used to reference the wjdb class is as follows:

$wjdb = wjdb::instance();

Example usage

$host = 'localhost';
$user = 'username';
$password = 'password';
$name = 'database_name';

/**
 * Example on how to use $wjdb.
 *
 * @uses $wjdb wjdb database object
 */
if ( !isset($wjdb) ) {
	// Reference wjdb database object
	$wjdb = wjdb::instance();

	// Connect to the MySQL database
	if ( !$wjdb->connect($host, $user, $password, $name) )
		printf("Could not connect to database: %s", $wjdb->last_error);

	$wjdb->close();
}

How we block multiple instances of wjdb

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

private function __construct() { }

The $instance object is where we store the database object.  This property must be private and static.

private static $instance;

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

/**
 * Creates and references the wjdb object.
 *
 * @access public
 * @return object MySQLi database object
 */
public static function instance() {
	if ( !self::$instance )
		self::$instance = new wjdb();
	return self::$instance;
}

The magic method __clone() is invoked anytime cloning is attempted.  Anytime this is attempted, we trigger an error (E_USER_ERROR).

/**
 * Prevent cloning of wjdb.
 *
 * @access public
 * @return void
 */
public function __clone() {
	// Issue E_USER_ERROR if clone is attempted
	trigger_error('Cloning <em>wjdb</em> is prohibited.', E_USER_ERROR);
}

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

return self::$instance;

11 thoughts on “MySQL database class using Singleton design pattern”

  1. Instead of using a singleton, why not make all of the class variables and functions static, then define the class as abstract so PHP won’t allow instantiation.

    abstract class mydb
    {
    private static $dbconn = null;

    public static connect($host, $user, $password, $name)
    {
    self::$dbconn = new mysqli($host, $user, $password, $name);

    }
    }

    mydb::connect(…);

    There’s nothing in your class that requires multiple instances (obviously) and it’s easier to let PHP enforce the singleton pattern by declaring the class abstract.

    1. Hi Greg,

      Thanks for taking the time to comment. You have a good point, and everyone should keep that in mind when creating their own classes.

      (Don’t forget that this is for beginners =] … learning about abstract classes may come in another segment of this tutorial, and we will definitely transition into more advanced tutorials once we have some basics to back them up.)

      Thanks again!

      -Chris

  2. Pingback: php-html.net
  3. this is very interesting. Does thid code works for updata and deletion of records on the database? I Prefere using procedures, how can I accomodate them in your code above? Can you also give me an example of how to use your code in a class and html page, becouse I am thing 3-tier architecture. Dataaccsess, busieness and presentation layer

  4. Thanks, I’ve been looking for a good solution to use the mysqli object in combination with mysqli_real_escape_string.

    Best tutorial I found all evening!

  5. I know the topic is about the single pattern but wondered about the get_results method.

    What if I called this method with multiple queries? I would probably end up with consolidated $this->last_result?

  6. Hi… old post, but still very good.

    One thing .. the connect method could be inside the __construct scope so we can reduce one line of code.

    Another thing.. its better let the $db var as public so we can use mysql native methods on other classes, like this: $results = mysqli_query($mysql->db, “select * from tablename”);

    Hugs.. and congrats (a little lately) for the great post.

  7. I know this is a couple of years old, but when your are starting to look at the Singleton design pattern, this is far and away the best tutorial I’ve found.

    Answers all of the questions I had, is simple, elegant and doesn’t feel the needs (as most seasoned developers do) to trample on aspiring developers confidence.

    Thank you!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>