Eric

If you read my previous blogs, I stated that using a single connection to the database can help keep database streams to a minimum.  Of course, no matter what, getting all of you the information you want once per page call would be key.  If you are not running memcache or other programs to aid in keeping database calls to a minimum, having a class cache all you queries per page might be beneficial to help keeps hits to a possible low.  Some pages require more interaction than others, and some might require the same call multiple times to get the information it needs to construct the page.

For those of you who rather construct your own database class, using a Singleton pattern would of course be the way to go.  Instead of creating hundreds of instances of the same class, the Singleton pattern will verify to see if a connection has been made, and if so, return the existing connection.

Traditionally, you would start your class like so…

class database{
public function __construct() {};
}

and you would call on the class as such:

$db = new database;

When using a singleton pattern, your connection would be slightly different:

class database {
private static $instance;
private $connection;

private function __construct() {
$this->initiate_connection();
}

public static function get_instance() {
if( !self::$instance ) {
self::$instance = new __CLASS__;
}

return self::$instance;
}
}

Now to call this would be a simple:

$database = database::get_instance();

and you have your connection.  But did you see what happened in get_instance?  first it checked to see if the instance was already created, if it has been, then it returns the already created instance to whatever is calling it.  If not, then it creates the instance, and returns that instance.

Now for the fun part, using it to keep a persistant connection.  If you noticed the construct method called $this->initiate_connection().  The function initiate_connection will by your code to create the connection to the database, then store it in the global variable $connection.  Now your connection is available to all functions in your class.

you can program initiate_connection to be smart, using a simple if statement to check for new credentials, or using default credentials, you can use your class to manage and cache multiple database connections.

For instance:

private function initiate_connection()
{
// check for a special connection, if so use special connection to create the connection.  if not use default connection.

$this->connection['connection_name'] = //your new connection;
}

as you can see, $this->connection is an array of objects.  this will allow you to hold multiple db connections under one class, and allow you to access them through a special name that you give that connection.  You can define new connections by writin a function to set them:

public function database( $db, $user, $pass, $host, $name )
{
$this->connection[$name] = mysql_pconnect( $host, $user, $pass);
mysq_select_db( $db, $this->connection[$name] );
}

now you have just defined a new connection.  by setting global variables for special db, host, user, and pass, you can make use of your already created mysql connector initiate_connection.

now you can use any db connection you want by simple letting the class know which connection to use.  You can write a setter function to do so, or define it in your function to execute the query.  For simplistic reasons, we will just define it in the function that the query will be executed in.

public function query( $sql, $connection_name ) {
return mysql_query( $sql, $this->connection[$connection_name] );
}

Now you have the raw output of the mysql query for your leisure.  to make this even better by caching the results, create a global variable called $cache and run a check to see if the sql has already been executed, if not, execute and return, or return the already executed query.

public function query( $sql, $connection_name ) {

if( exists( $this->cache[$sql] ) )
{
return $this->cache[$sql];
}
else
{
$result = mysql_query( $sql, $this->connection[$connection_name] );
$this->cache[$sql] = $result;
return $result;
}
}

now you have a cached copy of the sql statement saved to be used else where on the page if you need to.  since this is sql specific, no matter which database connection you use, it will always return the appropriate cached copy.

The end result, you should have a single class that will manage multiple db connections, cache the connection as well as cache all sql statements performed per page to ensure minimal usage of the actual database.  Using the singleton pattern ensures that the class is not created multiple times, and you can actually create the method __clone() to ensure this class cannot be cloned to avoid to many unnecessary connections to your database.  Your final code should look something like this.

class database {
private static $instance;
private $connection = array();
private $cache = array();

private function __construct() {
$this->initiate_connection();
}

public static function get_instance() {
if( !self::$instance ) {
self::$instance = new __CLASS__;
}

return self::$instance;
}

private function initiate_connection()
{
$this->connection['default'] = mysql_pconnect( $host, $user, $pass);
mysq_select_db( $db, $this->connection['default'] );
}

public function database( $db, $user, $pass, $host, $name )
{
$this->connection[$name] = mysql_pconnect( $host, $user, $pass);
mysq_select_db( $db, $this->connection[$name] );
}

public function query( $sql, $connection_name ) {

if( exists( $this->cache[$sql] ) )
{
return $this->cache[$sql];
}
else
{
$result = mysql_query( $sql, $this->connection[$connection_name] );
$this->cache[$sql] = $result;
return $result;
}
}
}

How to use:

$db = databae::get_instance();
$db->database( ‘db1′, ‘user’, ‘pass’, ‘192.168.0.100′, ‘mySpecialConnection’ );
$query1 = $db->query( ’select * from table1 where firstname = “john”, ‘primary’ );
$query2 = $db->query( ’select * from table3 where pet = “cat”, ‘mySpecialConnection’ );
$query3 = $db->query( ’select * from table1 where lastname = “smith”, ‘primary’ );
$query4 = $db->query( ’select * from table4 where fish = ‘tiger oscar’, ‘mySpecialConnection’ );

now you have a bunch of queries off of multiple databases, off of one class with virtually one connection.  All results are cached until you move to the next page, so if you so need them, they are already available to you.  Makes your life easier, and your database connection more efficient.

Keeping heavy loads off of a database can be a tedious task in itself.  Many times multiple instances are created, creating multiple connections to the database that hog resources when in fact only one instance and connection is needed.  With PHP5, database abstraction layers are built into the core.  PDO, although it has it’s deficiencies, can be a good starting point for creating a simple database abstraction layer.  However, learning how to use one single instance throughout the life of the page is the key to balancing server load and enhancing performance.

Starting off with a simple “autoload” file I find is helpful.

<?php
include( ‘autoload.php’ );
?>

The autoload file doesn’t have to be complicated.  It can be a basic file that creates all instances to be used through out the page, not to mention autoload classes that can be called else where throughout the script.

autoload.php

<?php
include( ‘config.php’);

// loop to auto load class files

/* Single Database initiation to be used throughout the script */

$dsn = “mysql:host=localhost;dbname=my_database”;
$dbuser = “my_database_username”;
$dbpass = “my_database_password”;

$database = new PDO( $dsn, $dbuser, $dbpass );
?>

Typically it is better to define the database info in a global configuration file that will typically not change.  It keeps all important variables that will be used multiple times in one place.  But this autoload file defines the variable $database that will be used throughout the rest of the page.  This will create on instance and most importantly, 1 connection to the database to be used.

On the file that will be needing, not mention allowing class and objects to use this instance can be accomplished this way:

File:

<?php
include( ‘autoload.php’);

$sql = “select * from my_table”;

$stmt = $database->prepare( $sql );
$stmt->execute();

while( $rows = $stmt->fetchAll() )
{
// Do Something
}
?>

Passing and allowing usage in objects:

<?php
include( ‘autoload.php’ );

$helper = new helper( $database );

echo $helper->displayView();
?>

The class file:

<?php
class helper
{
private $database;

public function __construct( $database )
{
$this->database = $database;
}

public function displayView()
{
// do something
$sql = “some sql statmet”;
$stmt = $this->database->prepare( $sql );
$stmt->execute();

while( $rows = $stmt->fetchAll() )
{
// Do something
}
}
?>

As you can see, this allows one single database thread to be used across the entire page freeing resources on the server making it more efficient.

For more information on PDO visit the manual at http://www.php.net/pdo.