Saving Resources with PHPCache

Caching your Database Queries

You must do more than simply put your database queries in the caching code block for them to be cached. You must still specifically identify the information you want cached, and to do that we use the cache_variable() function.

The cache_variable() function takes one argument: the name of the variable you want cached. It can be used like this:

cache_variable(“variablename”); // the variable $variablename    
has been cached

This, of course, needs to be placed within the caching code block.

Technically, this is all the information you need for this script to work. However, as some of you may have problems making the jump from caching a single variable to caching multiple query results, I’d like to share my particular method for doing so.

To cache a database query that returns one row is very easy -- you can simply cache each field independently -- but it gets more complicated when your database returns multiple rows. Surely you don’t want to cache every variable in a 100 row result set independently? To cache the results that consist of multiple rows, we make use of arrays -- in particular, two wonderful functions called array_push() and array_walk().

Let’s say you have an article-driven site and want to cache a list of all the different authors, along with their emails and whatever you use as a primary key (we’ll just say id). To cache such a query, you might do something like this:

$result_authors = mysql_query("SELECT first_name, last_name, email, id    
FROM authors ORDER BY last_name“, $db);  
if (!$result_authors) {  
 echo("<p>Error performing author query: “ . mysql_error() . "</p>");    
 exit();  
} // run the query and do error checking  
$authors = array(); // create the array  
// extract the query data into array $main  
while ($main = mysql_fetch_array($result_authors)){    
 // use array push to insert a result set row into our array  
 array_push($authors, $main);    
}  
cache_variable("authors"); // cache your array  
mysql_free_result($result_authors);

Most of that should be familiar to you. If you need some help with how to connect to do a database query with PHP, I suggest you read Kevin Yank’s article series, which covers the subject in ample detail.

What I am going to explain is the array_push() function. This function takes two arguments, the first being the name of the array you wish to use, and the second being the data you wish to put in the array. All the function really does is take that data, and place it in the next available line in the array. What I do is push each result row into our $authors array, and, as all the database information is then contained in one variable -- our array -- we can easily cache it using the cache_variable() function.

Now we come to the second array function I mentioned, array_walk(). While array_push() is great for inserting one row into an array at a time, array_walk() is perfect for running a function on one row in the array at a time.

To extract the data from your cached array, you’ll need to use array_walk() where you want the data to appear on your page:

if($authors){ // if the array exists  
print("<p><h2>Authors</h2>");  
// array walk the array $authors through the function print_authors();  
array_walk($authors, 'print_authors');    
print("</p>");  
}