Simple Query Caching

Hi folks,

In my previous post I had discussed about Memcache in CakePHP. In this I have come up with my own solution of caching queries. It took me around 30 minutes to come up with the following class, so you can imagine it might not be the best one. This exactly is not memcaching, but simply query caching. But it will speed up your queries anyways. This following class can be implemented with simple PHP. For other frameworks or open sources you need to customize the class accordingly.

So here is the class,"class.memcache.php":

<?php
/****************************************************************************


                      Q U E R Y    C A C H I N G    (version: 1.0)


                      This class will let you cache the various database queries, the main
                      idea is to store the output of a query in cache for the first time
                      and later the output is fetched from the cache rather than the
                      database, thus optimizing the performance


                      Developer: Md Murtuza Hasan Mallick     


                      Developed on: 19-February-2011                    
                      Last Modified: 19-February-2011


****************************************************************************/




class memCache
{
 var $_cache_path="";
 var $_host="";
 var $_user="";
 var $_password="";
 var $_db="";
 var $_duration="";


 function __construct($db_host,$db_user,$db_password,$db_db,$cache_dir)
 {
  $this->_host=$db_host;
  $this->_user=$db_user;
  $this->_password=$db_password;
  $this->_db=$db_db;
  $this->_cache_path=$cache_dir."/";
  $this->_duration=10;
  $this->_connect();
 }
 public function cache($query,$cache_name,$duration_f=null)
 {
  if($duration_f!=null){ $_duration=$this->duration_f; }
  $result_set_cache=array();
  if(!($result_set_cache=$this->_read($cache_name)))
  {
   $result_set_cache=$this->_query($query);
   $this->_write($cache_name,$result_set_cache);
  }
  return $result_set_cache;
 }
 private function _query($query)
 {
  $query_output=mysql_query($query);
  $output_array=array();
  $output_array_index=0;
  while($record_set=mysql_fetch_object($query_output))
  {
   $output_array[$output_array_index]=$record_set;
   $output_array_index++;
  }
  return $output_array;
 }
 private function _write($cache_name,$output)
 {
  $duration=$this->_duration;
  $file=$this->_cache_path.$cache_name;
  $fp=fopen($file,"w");
  $content[]=$duration."|".time();
  $content[]=$output;
  $content=serialize($content);
  fwrite($fp,$content);
  fclose($fp);
 }
 private function _read($cache_name,$isduration=false)
 {
  $file=$this->_cache_path.$cache_name;
  if(!file_exists($file)){
   return array();
  }
  $fp=fopen($file,"r");
  $content=unserialize(fread($fp,filesize($file)));
  fclose($fp);
  $time=explode("|",$content[0]);
  $tot_to_spend=(int)$time[0] + (int)$time[1];
  if($tot_to_spend < time() ){
   return array();
  }else{
   if(!$isduration){return ($content[1]);}
  else{ return ($content[0]); }
  }
 }
 private function _connect()
 {
  mysql_pconnect($this->_host,$this->_user,$this->_password) or die();
  $this->_selectdb();
 }
 private function _selectdb()
 {
  mysql_select_db($this->_db) or die();
 }




}


?>

save the above class. Create another php file, "example.php" with the following content:

<?php

$query="select * from categories";

include("class.memcache.php");
$obj=new memCache("<database_host>","<database_user>","<database_password>","<name_of_database>","<name_of_cache_directory>");
$ouput=$obj->cache($query,"new_test_cache",60);

echo '<pre>';
print_r($ouput);
echo '</pre>';
?>

The above code will perform query caching for the query, $query. Whenever the cache() is called, the query results are fetched using the caching technique. The arguments of the function are:

1. SQL query
2. Name of the cache(need to be unique for every query)
3. Duration of refreshing the records(not mandatory, default is 60 seconds) respectively.

Since this is a custom code, so you might find bugs. So post in your comments if you do and also if you don't ;)

Enjoy!