Interaction Between PHP and MySQL Using the Singleton Pattern

The Singleton pattern is a design pattern which restricts instantiation of a class to one single object . An object is instantiated when you first call for it , from that point on , each call will return the same object (instance of the Class) . The singleton pattern is generally used for objects that represent resources to be used over and over within many different parts of the application , but should always be the same . Common examples might include your database connections and configuration information . On simple projects , this pattern is just an overkill . Frameworks like  Zend-Framework instantiate tens , if not hundreds of Classes during each session . Instead of wasting memory by instantiating the same class multiple times , you can instantiate it only once and use that instance repeatedly ( leads to a huge performance boost ) . The Singleton pattern is often implemented in Database Classes , Loggers , Front Controllers or Request and Response objects .

A Class that implements the Singleton Design pattern has the following structure :

1. a protected static property holds our single instance
2. the constructor (__construct()) is defined as private  so that the class can only be instantiated by a  method contained within itself
3. a static method (getInstance() , the name is up to you ) should decide how to react . When called , Db::getInstance() will either instantiate an object of the Database class and assign it to the Db::$_instance property, then return it , or simply return the previously instantiated object .
4. Optionally , make use of the magic function __clone to prevent users from cloning the object itself .

Let’s demonstrate a Mysql interaction using the Singleton pattern :

</pre>
<?php
class Db extends mysqli
{
 protected static $instance;
 protected static $options = array();

private function __construct() {
 $o = self::$options;
 // turn of error reporting
 mysqli_report(MYSQLI_REPORT_OFF);

// connect to database
 @parent::__construct(
 $o['host'] ,
 $o['user'] ,
 $o['pass'] ,
 $o['dbname'] ,
 isset($o['port']) ? $o['port'] : 3306,
 isset($o['sock']) ? $o['sock'] : false );

// check if a connection established
 if( mysqli_connect_errno() ) {
 throw new exception("Could not establish a Database connection ");
 }
 }
 public function __clone() {
 die(__CLASS__ . ' class can\'t be instantiated. Please use the method called getInstance.');
}
 public static function getInstance(array $opt = array()) {
 if( !self::$instance ) {
 self::$options = $opt ;
 self::$instance = new self();
 }
 return self::$instance;
 }

public function query($query) {
 if( !$this->real_query($query) ) {
 throw new exception( $this->error, $this->errno );
 }

$result = new mysqli_result($this);
 return $result;
 }

public function prepare($query) {
 $stmt = new mysqli_stmt($this, $query);
 return $stmt;
 }
}

$options = array(
 'host' => 'localhost' ,
 'user' => 'root',
 'pass' => '' ,
 'dbname' => 'test'
 );
try
{
 $con = Db::getInstance($options);
}
catch(Exception $e)
{
echo $e->getMessage() ;
exit ;
}

// Trying to use the "new" statement will lead to a fatal error
//Fatal error: Call to private Db::__construct() from invalid context
$cann = new Db() ;
// Same instanse is returned , even if the Class is instantiated multiple times
$con2 = Db::getInstance();
$con3 = Db::getInstance();
$con4 = Db::getInstance();
$result = $con->query("select * from committee_members");
echo "<pre>" ;
print_r($result) ;
/* Fetch the results of the query */
while( $row = $result->fetch_assoc() ){
 //printf("%s (%s)\n", $row['Name'], $row['email']);
 echo "<pre>" ;
print_r($row) ;
}

&nbsp;
?>
<pre>

To use the singleton , because static methods are accessible within the global scope , wherever we want a database connection , we can simply call Db::getInstance() . Keep in mind that this pattern is highly criticized .  There are several problems built into the  the singleton pattern. The first and foremost  is that while the idea of a singleton seems great , the limitation quickly becomes apparent as you find you need a second instance for some new aspect of your software . For example :

1.  what happens if you decide to split database read/writes to different servers ?
2. singletons are designed to hang around once an object is instantiated (what about garbage collection ? ) .
3. unit testing becomes a nightmare

Beside the aforementioned con’s , a carefully designed code that implements this pattern ( merged up with other patterns ,e.g. registry, factory) , can lead to huge performance boost .

Links :

• Another implementation of the Singleton MySQL-Database-Class on Github .
• How is testing Registry Pattern or Singleton hard in PHP ?
• PHP global in functions
• Are global variables in PHP considered bad practice? If so, why?

About these ads