Caching MySql results with PHP

[aliasxneo]

Hello fellow programmers. I am here to describe to you how and why you should cache your MySql tables. I assume that you already have a MySql database and an application you would like to implement this feature on. Well, i'll try to make this quick and too the point.

Why should I cache my MySql tables?

What a dumb question! If you build your website using caches instead of directly querying your server everytime, you will not only decrease server load, but also greatly decrease the time it takes for your pages to load. Although caching is not for everyone, I would definently reccomend if for those of you who are constantly interacting with a large database, and would like to ease up all this querying.

What exactly is 'caching' anyways?

Well, caching is in this case taking a MySql table and saving it on the server somewhere. In this way next time you need information from that table you can instead use the cache instead of sending another query to the server.

What if my MySql data changes?

Well there is three ways to go about solving this problem:

1. Automatically update all the cache's based on a timestamp
2. Update cache's based on if any data has changed between the database
3. Update the cache everytime changes are made

The first option is quite simple. Create a timestamp and before every script execution compare it to the current time and if enough time has passed by automatically update the cache. In this way your cache is constantly being updated.

The second option can be done the same way the first or third was in, but it has a little twist. Instead of just updating the cache's automatically, go through each cache and compare it to the current data on the database and if anything has changed update the cache.

The last (third) option is the most effective, but it assumes that your script has full control of the database and that only your application can update information in it. Whenever your application updates the database (via an INSERT or UPDATE query) update the cache for whatever table you are editing.

I'm interested now, how do I cache my tables?

Well, it's quite simple. PHP contains a function called serialize(). This function takes an object as it's parameter and serializes it into one string keep all it's data and elements intact. In other words, you could serialize an array and still keep all it's elements in tact. Which brings me to the next function, unserialize(), which does just that, unserializes a string. To see how these two work together, try the following code:

 <?php

$content = array('one', 'two', 'three');
$serialized = serialize($content);
print $serialized;
$content = unserialize($serialized);
foreach ($content as $value)
{
    print $value . "\n";
}

?>  

As you can see we first create an array with three values. We then serialize it and print the serialized string. We then use the unserialize() function to unserialize the serialized string and then print all it's content out. As you can see, everything was kept in perfect order.

Okay, so how do we use serialize() to cache MySql tables?

Well, it's simple. Examine the following code:

 <?php

mysql_connect('host', 'user', 'pass');
mysql_select_db('mydb');

$sql = "SELECT * FROM `mytable`";
$result = mysql_query($sql);
while ($row = mysql_fetch_assoc($result))
{
    $rows[] = $row;
}

$cache = serialize($rows)
$fp = fopen("cache.txt","w");
fputs($fp, $cache);
fclose($fp);

?>  

First we make a connection to a database, you would of course replace the connection info with real details. We then send a query to 'mytable' and request all it's rows. Next we place all the rows into one array called $rows. Finally we serialize the array and then save it to 'cache.txt'. Now we have all the rows of that MySql table stored in one file.

How do I get the table information back?

Simple, all we have to do is read from the file and unserialize the content:

 $file = 'cache.txt';
$rows = unserialize(implode('',file($file))); 
  

Now you have all the information you retrieved from the MySql table when you first cached it in $rows. You can go ahead and even print some of the original content and see that it matches.

I'll leave the rest up to you. Once you have all this down it's really easy to implement on some of the most complex applications. Hope this tutorial has helped you out :) Comments and suggestions are welcome.

~ Alias

[Nick Presta]

What I suggest, if you're using a timestamp to generate new results after a set amount of time, is to generate a timestamp and incorporate that into your filename somewhere.

Something like this:

 $fileNameTime = time();
$fp = fopen("$fileNameTime.txt", "w"); 
  

Then, when you want to grab your data, just check:

 $timenow = time();
$refreshAmount = 120; // 120 seconds = 2 minutes
if($timenow - $fileNameTime >= $refreshAmount) {
  // grab new data redefine $fileNameTime and save again.
} 
  

[aliasxneo]

I didn't use timestamps, but I made the file name the same as the table name. Here was a simple class I wrote (and never expanded) along time ago that implement my above idea:

 <?php
require_once('mysql.class.php');

class L2_cache extends L2_mysql
{

    function _intialize() // This function has to exist in the class //
    {
        parent::_intialize(); // This must be called so all classes above can get a chance to setup //    
    }

    function update_cache($table, $server)
    {
        $cached = serialize($this->query("SELECT * FROM " . $table, $server, 'ARRAY'));
        $file = SYSTEM_PATH . '/cache/' . $server . '_' . $table . '.txt';
        $fp = fopen($file, "w"); // open file with Write permission
        fputs($fp, $cached);
        fclose($fp);
    }
    
    function get_cache($table, $server)
    {
        if (!file_exists(SYSTEM_PATH . '/cache/' . $server . '_' . $table . '.txt'))
        {
            $this->update_cache($table, $server);
        }
    
        return unserialize(implode('',file(SYSTEM_PATH . '/cache/' . $server . '_' . $table . '.txt')));
    }
        
}


?>  

I grabbed that straight from a framework I wrote, so some things won't make sense, but you can pretty much understand most of it. Just notice how I used the table name to keep track of the files.

[Mau]

Thanks for the tutorial!