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.

In the “old days”, using JavaScript to enhance your site was not a recommended thing to do when going for search engine optimization.  Javascript often times  added a lot of code to the page that blocked many search engines from reading the content, produced rollover links that were not readable to a search engine spider, and weighed down the page giving the page a low content to code ratio.

Now days, with the recognition of the importance of search engine optimization, new ways of using JavaScript has been implemented to allow rollover links and other various effects to be readable by search engines.  Not necessarily does JavaScript produce the code on the demand, the combination of using CSS and JavaScript together allows “dynamic” content to be produced, as well as “dynamic links”.  Note that I quote them.  The content and links in fact are not dynamic and hard coded into the HTML page, however using CSS and div tags, you can hide the div until it is ready to display.  Using DOM and JavaScript, you can call on these div tags, manipulate the CSS to grab the content or links and display at your leisure.

This will allow the ability to still have the content and links visible to the search engine spiders, yet give your site more options as far as looking modern or cutting edge.  There are a lot of good open source packages out there that do this.  One of the most common is script.aculo.us. Script.aculo.us allows animated effects to your site, making it more cutting edge.

There are other ways of using JavaScript and other open source packages to achieve various effects to enhance your site.  My portofolio site zencreatives.com uses a lot of open source JavaScript packages and CSS to produce various effects, yet making the page completely readable by a search engine.

Also a thing to note, all the JavaScript is kept in a seperate JS file.  You do not want code on your page that is not necessary.  This will wieght down your page, not to mention, to much JavaScript can keep a search engine spider from wanting to crawl the rest of your site.

Some time in the near future, I will put up working code samples and using open source packages to show you how it can be done.  Not to mention I will show you how to tweak open source packages to achieve other effects that you may need.  If you have any questions as to how to do something, feel free to leave a comment, and I will compile a list to make tutorials on.  I will create them as I recieve requests or the ones that are most commonly needed.  So stay tuned.

PHP, though having it’s limitations, has grown into a vast and robust language of choice by many developers, including myself.  PHP5 when initially released back in 2006, offered a true Object Oriented experience.  Yet to this day, I still find that many PHP develpers are not using the true capabilities that PHP has to offer.  Frameworking in PHP is not only ideal, but offers a great amount of flexibility and power to drive any system.  Granted, not all sites will need a robust framework, having a psuedo framework in place is still idea.

The days of inter-twining PHP and HTML should be something of the past, yet I find many still do it.  Even without creating a J2EE Framework, a small psuedo framework could be something that will help you rapidly deploy the site in a matter of days to a week.  Like most mainstream software engineering is concerned, you have several layers.  You will have the view or graphical interface layer, the database abstraction layer, and the processing layer.  Properly setting up classes and objects to handle each layer can greatly improve speed, reliability, and ease of trouble shooting.

Mixing PHP and HTML makes the code hard to read, hard to understand, and sends you on a needle in a haystack hunt trying to find the break.  Creating a templating layer that will read a template and combined with the processing layer can help you easily find a break when it comes to visual.  You basically know it will be in one of those components.  If you are smart, you will incorporate a debug mode to all of your classes that you can verbose out all information that is being processed and how it is being processed.  The only layer that should ever talk to the visual layer is the constructor page and the content processing layer.  Even then they really don’t need to talk.  The visual object or layer should only have to deal with the visual aspects.  The content processing layer should only have to work with the database layer to get the content ready to be inserted into the visual layer.

This will provide a basic framework that you can use through out all sites that will make it easier to deploy and rapidly deploy sites that do not need the power of an actual framework.  The typical core objects I usually have are the template class, and the content class.  Though the content class will usually be broken up into it’s own classes and subclasses based on the function of the particular page or data being processed.  The template class is basically just that.  It will grab a template, insert the necessary data, and return.  Lately I have been using PDO as a the database layer.  If you have ready my previous post on a better way of using a database object, then you already know that I initiate the database object in an autoload page, and then make that object readily available to all classes.  It cuts database tunnels to only one, and i believe is more efficient on resources.  Not to mention it makes things a whole lot easier.

It provides flexibility in changing templates on the fly, keeps the data where it belongs, and it makes the code easier to read.

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.