Blog with PHP, MySQL and other tutorials. You can also download my scripts, report a bug or leave a feedback.
This will be intermediate tutorial about caching SQL query results. We will use interface to make support for extending available ways of caching your results. We will create cache that will use MySQL database and flat text files to store cache.
This is image of our folder structure.
Query Cache Folder Structure
So first we have two folders, cache and Cacher. Cacher folder will be used for storing classes that are used to cache our query and cache folder will be used for storing cached files when using Cacher_Txt. In Cacher folder we have two files, Sql.php and Txt.php. Those folders contain classes. Next we have cacherInterface.php that will have defined interface for classes that cache our results and queryCache.php that will be our main file and class.
Main ClassTop
< ?php
/**
* Class that is used to cache results retreived from database.
*
* @author Marijan Šuflaj <msufflaj32@gmail.com>
* @copyright (C) Marijan Šuflaj 2009
*/
class queryCache
{
[...]
};
This will be our main class that will contain methods for caching. Now we will define some variables and constants.
Variables And ConstantsTop
Every variable and constant has its PHPDoc comment and description so you know what it does and what type of value it should contain.
/**
* Constant for SQL cacher.
*
* @var int
*/
const SQL = 1;
/**
* Constant for TXT cacher.
*
* @var int
*/
const TXT = 2;
/**
* Connection resource.
*
* @var resource
*/
private $_conn = null;
/**
* Currently active cacher.
*
* @var int
*/
private $_type = queryCache::SQL;
/**
* Cache life time.
*
* @var int
*/
private $_lifeTime = 1800;
/**
* Array of available cachers.
*
* @var array
*/
private $_available = array(
queryCache::SQL,
queryCache::TXT
);
/**
* Extra params for each cacher.
*
* @var array
*/
private $_extraParams = array(
queryCache::SQL => array(),
queryCache::TXT => array(
'savePath' => './cache/'
)
);
/**
* Cachers classes.
*
* @var array
*/
private $_cachers = array(
queryCache::SQL => 'Cacher_Sql',
queryCache::TXT => 'Cacher_Txt'
);
Those are our variables and constants. Constants are for easier choosing our cache class so if you would like to choose cache that uses text files to store data you would assign it using type(queryCache::TXT);. Method type we will define later. Now we will create our methods that will call those classes for caching.
MethodsTop
This are our methods that we use to make our class working.
__constructTop
/**
* Constructor.
*
* @param resource $conn Connection resource
* @return
*/
public function __construct($conn)
{
$this->_conn = $conn;
$this->_extraParams[queryCache::SQL]['conn'] = $conn;
}
This function just stores connection resource that is passed to constructor and adds it to extra parameters that is needed in cache class that uses MySQL to store data.
lifeTimeTop
/**
* If $lifeTime = null returns current life time, otherwise sets new life time.
*
* @param int $lifeTime Life time in seconds
* @return int|bool True if life time set, current life time value if $lifeTime = null
*/
public function lifeTime($lifeTime = null)
{
if (is_null($lifeTime))
return $this->_lifeTime;
$this->_lifeTime = (int) $lifeTime;
return true;
}
This method is used for setting life time of our cache. If argument $lifeTime is null then current life time value is returned. If it is not null then new life time value is set.
queryTop
/**
* Executes a query and caches results if $cache = true.
*
* @param string $sql Cache to be executed
* @param bool $cache If true we will cache results
* @return resource|array If $cache = true array of results, otherwise result resource
* @throws Exception
*/
public function query($sql, $cache = true)
{
if ($cache) {
$cacher = new $this->_cachers[$this->_type]($this->_extraParams[$this->_type]);
$cacher->setSql($sql);
$cacher->setLifeTime($this->_lifeTime);
$cacher->cleanCache();
if(($temp = $cacher->cacheLoad()) !== false) {
return $temp;
}
else {
if (($rez = mysql_query($sql, $this->_conn)) === false)
throw new Exception('Unable to execute query.');
while (($row = mysql_fetch_object($rez)) !== false)
$tempRows[] = $row;
$cacher->cacheSave($tempRows);
return $tempRows;
}
}
else
return mysql_query($sql, $this->_conn);
}
Method that executes query. First argument is SQL query and second argument is boolean value that defines whether we use cache or not. If we do not use cache then function just returns MySQL result resource that is retrieved from mysql_query function. If we use cache then first we create object of class that is set as current cache class and pass extra parameters if any. Then we provide SQL and life time to that object. After that we invoke cache clearing proccess. Now we try to load cache if it exists. If method cacheLoad() does not return false, then we have cached results that are then returned. If it returns false then we do not have cache and we need to create one. First we try to execute query and if it fails we throw new exception. If it does not fail, then we create new array filled with results from SQL query and save it to cache. Now all we have to do is return those results.
typeTop
/**
* Sets cacher that is used. If $type = null, current cacher id is returned, otherwise
* new cacher is set.
*
* @param int $type Cacher id
* @return int|bool Int if $type = null, true if new cacher is set
* @throws Exception When cacher not available
*/
public function type($type = null)
{
if (is_null($type))
return $this->_type;
if (in_array($type, $this->_available)) {
$this->_type = $type;
return true;
}
throw new Exception('This type is not supported.');
}
This method is used to set currently active cache class or it $type is null it returns id of currently active cache class. If we try to set cache as active that does not exist, new exception is trhown.
cacherTop
/**
* Adds new cacher to available list. If $type = null, current cacher class is returned,
* otherwise new cacher is added.
*
* @param int $type Cacher id
* @param string $cacher Cacher class name
* @param array $extraParams Array with extra params (they are provided to constructor of $cacher
* @return string|bool String if $type = null, true if new cacher added
* @throws Exception If $cacher does not implement cacherInterface interface
*/
public function cacher($type = null, $cacher = null, $extraParams = array())
{
if (is_null($type))
return $this->_cachers[$this->_type];
$class = new ReflectionClass($cacher);
if ($class->implementsInterface('cacherInterface')) {
$this->_cachers[$type] = $cacher;
$this->_available[] = $type;
$this->_extraParams[$type] = $extraParams;
return true;
}
throw new Exception('Your cacher must implement cacherInterface interface.');
}
This last method adds custom cache class to available classes. If $type is null, then function returns class name of active cache engine. If it is not null we try to add it. If new class for caching does not implement cacherInterface, new exception is thrown. If it implements interface, it’s added to available cache classes and can be set as active.
AutoloadTop
/**
* Autoload function.
*
* @param string $class Class name
* @return
*/
function __autoload($class)
{
require_once dirname(__FILE__) . DIRECTORY_SEPARATOR . str_replace('_', DIRECTORY_SEPARATOR, $class) . '.php';
}
Simple autoload function that load file if class does not exist. If we call class Cacher_Sql and that class does not exist it tries to load file path/to/main/class/Cacher/Sql.php.
InterfaceTop
interface cacherInterface
{
/**
* Constructor
*
* @param array $params Array with custom params
* @return
*/
public function __construct($params);
/**
* Sets SQL string.
*
* @param string $sql SQL string
* @return
*/
public function setSql($sql);
/**
* Sets cache life time.
*
* @param int $lifeTime Cache life time
* @return
*/
public function setLifeTime($lifeTime);
/**
* Cleans cache.
*
* @return
*/
public function cleanCache();
/**
* Loads from cache.
*
* @return array|false Results from cache, false on error
*/
public function cacheLoad();
/**
* Saves result to cache.
*
* @param array $results Results array
* @return
*/
public function cacheSave($results);
}
Just simple interface that ensures that if we try to execute some method of our cache class, it exists. Every method that exists here must be implemented in class that implements this interface. Befeore method definition are PHPDoc comments so you can see what each of them does and what do they accept/return.
Cacher_SqlTop
This will be our first caching engine that will use MySQL to store data. It implements cacherInterface to make sure it has all methods required to work propertly.
class Cacher_Sql implements cacherInterface
{
[...]
}
VariablesTop
/**
* SQL string.
*
* @var string
*/
private $_sql = '';
/**
* Cache lifetime.
*
* @var int
*/
private $_lifeTime = 1800;
/**
* MD5 of SQL string.
*
* @var string
*/
private $_md5 = '';
/**
* Connection resource
*
* @var resource
*/
private $_conn = null;
Here are all required variables for this class to work.
MethodsTop
__constructTop
/**
* Constructor.
*
* @param array $params Extra params
* @return
*/
public function __construct($params)
{
if (isset($params['conn']))
$this->_conn = $params['conn'];
}
Simple construct function that sets connection resource.
setSqlTop
/**
* Sets SQL strung.
*
* @param string $sql SQL string
* @return
*/
public function setSql($sql)
{
$this->_sql = $sql;
$this->_md5 = md5($sql);
}
This function sets SQL code and md5 of it.
setLifeTimeTop
/**
* Sets cache life time.
*
* @param int $lifeTime Cache life time
* @return
*/
public function setLifeTime($lifeTime)
{
$this->_lifeTime = (int) $lifeTime;
}
Sets life time.
cleanCacheTop
/**
* Cleanes cache.
*
* @return
* @throws Exception If unable to execute query
*/
public function cleanCache()
{
$sql = "DELETE `cacheTut_sqlCache` "
. "FROM `cacheTut_sqlCache` "
. "WHERE UNIX_TIMESTAMP(`time`) + %d < UNIX_TIMESTAMP(NOW())";
if (!mysql_query(sprintf($sql, $this->_lifeTime), $this->_conn))
throw new Exception('Unable to execute query.');
}
Method used to clear cache. It just executes SQL query that deletes all records that are older then $_lifeTime. If there is error when executing query, new exception is thrown.
cacheLoadTop
/**
* Loads cache.
*
* @return array|bool Array if no errors, false on error
* @throws Exception If unable to execute a query
*/
public function cacheLoad()
{
$sql = "SELECT `data` "
. "FROM `cacheTut_sqlCache` "
. "WHERE `md5` = '%s' "
. "LIMIT 1";
if (($rez = mysql_query(sprintf($sql, $this->_md5), $this->_conn)) === false)
throw new Exception('Unable to execute query.');
if (mysql_num_rows($rez) !== 1)
return false;
$data = mysql_fetch_object($rez);
return unserialize($data->data);
}
Method used to load cache. It selects row where md5 column matches md5 of current SQL. If there is error new exception is thrown. If we do not have such row we return false, otherwise we return array with results
cacheSaveTop
/**
* Saves cache.
*
* @param array $results Results array that will be saved
* @return
* @throws Exception If unable to execute a query
*/
public function cacheSave($results)
{
$sql = "INSERT INTO `cacheTut_sqlCache` ( "
. "`md5`, `data` "
. ") VALUES ( "
. "'%s', '%s' "
. ")";
if (!mysql_query(sprintf($sql, $this->_md5, mysql_real_escape_string(serialize($results))), $this->_conn))
throw new Exception('Unable to execute query.');
}
This last methods is used to cache result. It just inserts new row with md5 of SQL query and serialized array with results. If there is error, new exception is thrown.
TestingTop
Now that we have it all set up, we can test it to see what are improvements. For this purpose I’ve downloaded sample databases from MySQL site and installed sakila database. To test is we will use following codes.
Select all recordsTop
$conn = mysql_connect('localhost', 'root', '');
mysql_select_db('sakila', $conn);
$sql = "SELECT * "
. "FROM `film`";
$start = array_sum(explode(' ', microtime()));
$rez = $cache->query($sql);
echo array_sum(explode(' ', microtime())) - $start;
When I’ve executed this code for fist time I’ve ended up with number 0.085674047470093 (that much time was required to select all records from film table and to transfer it to array. When I’ve run it second time I’ve ended up with number 0.036639928817749 (that much time is required to retrieve from cache).
Some compley query (JOIN)Top
$conn = mysql_connect('localhost', 'root', '');
mysql_select_db('sakila', $conn);
$sql = "SELECT `address`, `district`, `city`, `country` "
. "FROM `address` "
. "LEFT JOIN `city` "
. "ON `address`.`city_id` = `city`.`city_id` "
. "LEFT JOIN `country` "
. "ON `city`.`country_id` = `country`.`country_id`";
$start = array_sum(explode(' ', microtime()));
$rez = $cache->query($sql);
echo array_sum(explode(' ', microtime())) - $start;
When I’ve executed this code for fist time I’ve ended up with number 0.026587963104248 (that much time was required to select all records from address table, join city and country table and transfer it to array. When I’ve run it second time I’ve ended up with number 0.012687921524048 (that much time is required to retrieve from cache).
ConclusionTop
Using cache is very useful and can save your resources a lot. You can download source code from here. I’ve included table schema for SQL caching engine and I’ve also added text support so you can see how it’s used. Thanks for reading.
I am Marijan Šuflaj and I am 21 years old. Also I'm a student on FER (3rd year). I program in PHP for a long time but I am also learning other programming languages (like Java). This blog was made because I want to help PHP community by writing tutorials and publishing my PHP scripts so you can use them in your projects. Feel free to contact me and you can also ask for my help if you think I can help you.
