Eric

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.