Until now, we’ve covered in detail the different classes needed to make the “Cache” class work. The next thing to do is to put all the pieces together and implement a practical example. First, let’s list the complete source code for each class, and then implement the example. The full code listing is as follows:

// class MySQL
class MySQL{
            var $conId; // connection identifier
            var $host; // MySQL host
            var $user; // MySQL username
            var $password; // MySQL password
            var $database; // MySQL database
            // constructor
            function MySQL($options=array()){
                        // validate incoming parameters
                        if(count($options)>0){
                                   foreach($options as $parameter=>$value){
                                               (!empty($value))?$this->{$parameter}=$value:$this->isError('Invalid parameter '.$parameter);
                                   }
                                   // connect to MySQL
                                   $this->connectDB();
                        }
                        else {
                                   $this->isError('No connection parameters were provided');
                        }
            }
            // connect to MYSQL server and select database
            function connectDB(){
                        if(!$this->conId=mysql_connect($this->host,$this->user,$this->password)){
                                    $this->isError('Error connecting to the server');
                        }
                        if(!mysql_select_db($this->database,$this->conId)){
                                    $this->isError('Error selecting database');
                        }
            }
            // perform query
            function query($query){
                        if(!$this->result=mysql_query($query,$this->conId)){
                                   $this->isError('Error performing query '.$query);
                        }
                        // return new Result object
                        return new Result($this,$this->result);
            }
            // display errors
            function isError($errorMsg){
                       trigger_error($errorMsg.' '.mysql_error());
                       exit();
            }
}
// class Result
class Result{
            var $mysql; // instance of MySQL object
            var $result; // result set
            // constructor
            function Result(&$mysql,$result){
                        $this->mysql=&$mysql;
                        $this->result=$result;
            }
            // fetch row
            function fetchRow(){
                        return mysql_fetch_array($this->result,MYSQL_ASSOC);
            }
            // count rows
            function countRows(){
                        if(!$rows=mysql_num_rows($this->result)){
                                   $this->mysql->isError('Error counting rows');
                        }
                        return $rows;
            }
            // count affected rows
            function countAffectedRows(){
                        if(!$rows=mysql_affected_rows($this->mysql->conId)){
                                   $this->mysql->isError('Error counting affected rows');
                        }
                        return $rows;
            }
            // get ID from last inserted row
            function getInsertID(){
                        if(!$id=mysql_insert_id($this->mysql->conId)){
                                   $this->mysql->isError('Error getting ID');
                        }
                        return $id;
            }
            // seek row
            function seekRow($row=0){
                        if(!mysql_data_seek($this->result,$row)){
                                   $this->mysql->isError('Error seeking data');
                        }
            }
}
// class Cache
class Cache{
            var $mysql;  // instance of MySQL object
            var $result; // instance of Result object
            var $expiry; // cache expire time in seconds
            var $cacheFile; // cache file
            var $data; // result set array
            // constructor
            function Cache(&$mysql,$expiry=86400,$cacheFile='default_cache.txt'){
                        $this->mysql=&$mysql;
                        (is_int($expiry)&&$expiry>0)?$this-
>expiry=$expiry:$this->mysql->isError('Expire time must be a
positive integer');
                        $this->cacheFile=$cacheFile;
                        $this->data=array();
            }
            // if cache is valid, perform query and return a
result set. Otherwise, get results from cache file
            function query($query){
                        // check if query starts with SELECT
                        if(!preg_match("/^SELECT/",$query)){
                                   $this->mysql->isError('Invalid
query. Must start with SELECT');
                        }
                        if(!$this->isValid()){
                                   // read from MySQL
                                   $this->result=$this->mysql-
>query($query);
                                   $this->data=$this->write();
                        }
                        else {
                                   // read from cache file
                                   $this->data=$this->read();
                        }
            }
            // write cache file
            function write(){
                        if(!$fp=fopen($this->cacheFile,'w')){
                                   $this->mysql->isError('Error
opening cache file');
                        }
                        if(!flock($fp,LOCK_EX)){
                                   $this->mysql->isError('Unable
to lock cache file');
                        }
                        while($row=$this->result->fetchRow()){
                                   $content[]=$row;
                        }
                        if(!fwrite($fp,serialize($content))){
                                   $this->mysql->isError('Error
writing to cache file');
                        }
                        flock($fp,LOCK_UN);
                        fclose($fp);
                        unset($fp,$row);
                        return $content;
            }
            // read cache file
            function read(){
                        if(!$content=unserialize
(file_get_contents($this->cacheFile))){
                                   $this->mysql->isError('Error
reading from cache file');
                        }
                        return $content;
            }
            // determine cache validity based on a time expiry
trigger
            function isValid(){
                        if(file_exists($this->cacheFile)
&&filemtime($this->cacheFile)>(time()-$this->expiry)){
                                   return true;
                        }
                        return false;
            }
            // fetch cache row
            function fetchRow(){
                        if(!$row=current($this->data)){
                                   return false;
                        }
                        next($this->data);
                        return $row;
            }
            // fetch all cache rows
            function fetchAll(){
                        if(count($this->data)<1){
                                   $this->mysql->isError('Error
accessing cache data');
                        }
                        return $this->data;
            }
            // count cache rows
            function countRows(){
                        if(!$rows=count($this->data)){
                                   $this->mysql->isError('Error
counting cache rows');
                        }
                        return $rows;
            }
}

Although the code is rather lengthy, keep in mind that each class can be included as a separated file, when they’re required. Having a wealth of classes that do the hard work for us, implementing a result set caching system is as simple as this:

// connect to MySQL
$db=new MySQL(array('host'=>'host','user'=>'user','password'=>'password',
'database'=>'databasename'));
// instantiate a new Cache object, valid for 24 hours (time
expiry triggered caching)
$cache=&new Cache($db,86400);
// perform query and store results in cache file
// if cache is not valid, force a new cache generation
$cache->query('SELECT * FROM users');
// loop over rows and display results
while($row=$cache->fetchRow()){
            echo $row['firstname'].'&nbsp;'.$row['firstname'].'<br />';
}
// count rows
echo 'Total number of rows ' .$cache->countRows();

The above example obtains a result set from MySQL, by getting rows from a sample “users” database table, and stores the data in the cache file for 24 hours. All of the requests that occur during that time will fetch data directly from the cache, avoiding query executions.

When the time expiry is reached, the data is again retrieved from MySQL and stored in the cache file. Since the “Cache” class allows you to perform additional tasks, we’ve counted the total number of rows, as you can see in the example.

Say that we want to fetch all of the rows at once. The process is accomplished like this:

// get all rows
$rows=$cache->fetchAll();
// display data or do something else
foreach($rows as $row){
            echo $row['firstname'].'&nbsp;'.$row
['firstname'].'<br />';

}

Isn’t that simple? Definitely. The “Cache” class is very flexible, as you’ll probably agree. Due to its extensible nature, it might be improved by adding some other methods, useful for manipulating cached data.

Finally, we’re finished. In this fourth part of the series, we’ve successfully made all of the required classes work together, implementing a time expiry based caching mechanism, useful for reducing the workload associated with multiple query execution.

Since the object-oriented solution has proven to be efficient in caching result sets, in the next part of the series we’ll port the whole code that we’ve worked with to PHP 5. Certainly, the topic is really exciting, so get ready for the next challenge. See you soon!