MySQL caching with PHP
If you have a lot of complex queries to run on your site this php class may be of use to you.
It kills two birds with one stone, it's a database connection class and caches the results for a variable amount of time.
First the class:
<?php
//class for handling db connects and queries
class database
{
private $cacheDirPath;
private $cacheFilePath;
public function __construct($cacheDirPath="cache/", $cacheFilePath="")
{
$this->cacheDirPath = $cacheDirPath;
$this->cacheDirPath = $cacheDirPath;
}
public function yourDB1()
{
//connection for db1
$db_host = "localhost";
$db_un = "username";
$db_pw = "password";
$db = "database";
if(!mysql_connect($db_host,$db_un,$db_pw)) echo "not connected to db";
if(!mysql_select_db($db)) echo "db not selected";
}
public function yourDB2()
{
//connection for db2
$db_host = "localhost";
$db_un = "username";
$db_pw = "password";
$db = "database";
if(!mysql_connect($db_host,$db_un,$db_pw)) echo "not connected to db";
if(!mysql_select_db($db)) echo "db not selected";
}
public function fetch($sql, $type="All", $expired=0, $name="")
{
$cacheName = $name."_".md5($sql).".cache";
if ($expired>0) $isCached = $this->getFromCache($cacheName, $expired, true);
if ($isCached)
{
return $isCached;
exit();
}
//returns resulting field as string
$result = mysql_query($sql);
if (!$result) die("Query failed: " . mysql_error());
switch ($type)
{
case "All":
while ($temporary = mysql_fetch_array($result))
{
if (count($temporary) > 0) $resultArray[] = $temporary;
}
break;
case "Row":
$resultArray = (!$result) ? array() : mysql_fetch_array($result);
break;
case "One":
$resultArray = mysql_result($result, 0, 0);
break;
case "Flat":
while ($test = @mysql_fetch_row($result))
{
if (!empty($test[0])) $resultArray[] = $test[0];
}
break;
case "Pairs":
while ($test = @mysql_fetch_row($result))
{
$resultArray[$test[0]] = $test[1];
}
break;
}
if ($expired>0 and $value != "") $this->writeToCache($cacheName, $resultArray);
if ($result) mysql_free_result($result);
return $value;
}
private function getFromCache($filename, $seconds=0, $isObject=false)
{
$this->_buildFilename($filename);
$return = false;
if ($seconds == 0)
{
if (file_exists($this->cacheFilePath))
{
$return = $this->_readFromCache();
}
else
{
return false;
}
}
else
{
$refresh_time = time() - (int) $seconds;
if (@filemtime($this->cacheFilePath) > $refresh_time)
{
$return = $this->_readFromCache();
}
else
{
$this->removeFromCache($filename);
return false;
}
}
if ($isObject)
{
$return = unserialize($return);
}
return $return;
}
private function setCacheDir($dir_path)
{
if (strlen(trim($dir_path)) == 0)
{
trigger_error(get_class($this)."No Cache directory Path set.", E_USER_ERROR);
return false;
}
else
{
if (substr(strtoupper(PHP_OS),0,3) != "WIN")
{
if (substr($dir_path, 0, -1) != "/")
{
$dir_path .= "/";
}
}
else
{
if (substr($dir_path, 0, -1) != "\")
{
$dir_path .= "\";
}
}
$this->cacheDirPath = $dir_path;
// Check if a real directory
if (!is_dir($this->cacheDirPath))
{
trigger_error(get_class($this)."Cache Directory does not exist.", E_USER_ERROR);
return false;
}
}
}
private function writeToCache($filename, $data)
{
if (is_array($data) || is_object($data))
{
$data = serialize($data);
}
$this->_buildFilename($filename);
if (!$file = fopen($this->cacheFilePath, "w"))
{
trigger_error(get_class($this)."::writeToCache(): Could not open file for writing.", E_USER_ERROR);
return false;
}
$len_data = strlen($data);
fwrite($file, $data, $len_data);
fclose($file);
rename($this->cacheFilePath, $this->cacheFilePath);
return true;
}
private function removeFromCache($file_name)
{
$this->_buildFilename($file_name);
if (!file_exists($this->cacheFilePath))
{
return true;
}
else
{
if (!unlink($this->cacheFilePath))
{
trigger_error(get_class($this)."Unable to remove from cache file", E_USER_ERROR);
return false;
}
else
{
clearstatcache();
return true;
}
}
}
private function _readFromCache()
{
$mq_setting = get_magic_quotes_runtime();
set_magic_quotes_runtime(0);
if (!$return_data = @ file_get_contents($this->cacheFilePath))
{
return false;
}
else
{
set_magic_quotes_runtime($mq_setting);
return $return_data;
}
}
private function _buildFilename($file_name)
{
$this->cacheFilePath = $this->cacheDirPath.$file_name;
}
}
?>
First include the code on in your script, then initalize the class.
<?php
//include class file
include_once("dbconnect.php")
//initialize class
$db = new database();
//select query
$query = "SELECT * FROM table WHERE something='this'";
//brings back all results in an array
$result = $db->fetch($query, 'All', 84600, 'getThis');
//brings back a single row result in an array
$result = $db->fetch($query, 'Row', 84600, 'getThis');
//brings back a single value
$result = $db->fetchOne($query, 'One', 84600, 'getThis');
?>
