PHP : Database Access - [10.15] Caching Queries and Results

10.15.1 Problem

You don't want to rerun potentially expensive database queries when the results haven't changed.

10.15.2 Solution

Use PEAR's Cache_DB package. It wraps the DB database abstraction layer with an object that has similar methods and that automatically caches the results of SELECT queries:

require 'Cache/DB.php';

$cache = new Cache_DB;
$cache->connect('mysql://test:@localhost/test');

$sth = $cache->query("SELECT sign FROM zodiac WHERE element LIKE 'fire'");

while($row = $sth->fetchRow()) {
    print $row['sign']."\n";
}

10.15.3 Discussion

Using Cache_DB is almost the same as using DB, but there are some crucial differences. First, Cache/DB.php is required instead of DB.php. The Cache/DB.php file then loads the appropriate DB classes. Instead of creating a database handle with the DB::connect( ) method, you instantiate a Cache_DB object with the new operator and then call the object's connect( ) method. The syntax of $cache->connect( ) is the same, however, so you just pass it the DSN that identifies the database. The query( ) method of Cache_DB works just like that of DB, however there are no prepare( ) and execute( ) methods in Cache_DB. query( ) returns a statement handle that supports fetchRow( ) and fetchInto( ), but the default fetch mode is DB_FETCH_ASSOC, not DB_FETCH_ORDERED.

The first time a particular SELECT statement is passed to $cache->query( ), Cache_DB executes the statement and returns the results, just like DB, but it also saves the results in a file whose name is a hash of the query. If the same SELECT statement is passed to $cache->query( ) again, Cache_DB retrieves the results from the file instead of running the query in the database.

By default, Cache_DB creates its cache files in a subdirectory of the current directory called db_query. You can change this by passing a directory name as part of an options array as a second argument to the Cache_DB constructor. This sets the cache directory to /tmp/db_query:

$cache = new Cache_DB('file',array('cache_dir' => '/tmp/'));

The first argument, file, tells Cache_DB what container to use to store the cached data. file is the default, but you need to include it here to specify the container options in the second argument. The relevant container option is cache_dir, which tells Cache_DB where to create the db_query subdirectory. Including a trailing slash is required.

By default, entries stay in the cache for one hour. You can adjust this by passing a different value (in seconds) when creating a new Cache_DB object. Here's how to keep entries in the cache for one day, 86,400 seconds:

$cache = new Cache_DB('file',array('cache_dir' => '.',
                                   'filename_prefix' => 'query_'),86400);

Because the expiration time is the third argument, you have to pass the defaults for the first two arguments as well.

The cache isn't altered if you change the database with an INSERT, UPDATE, or DELETE query. If there are cached SELECT statements that refer to data no longer in the database, you need to explicitly remove everything from the cache with the $cache->flush( ) method:

$cache->flush('db_cache');

It's very important to include the db_cache argument to flush( ). The PEAR Cache system supports dividing up the cached items into different groups, and the Cache_DB object puts everything it's keeping track of in the db_cache group. Leaving out the group argument results in deleting the files in the base cache directory (which is probably the directory you're running your script from).

The file container stores each result in a file whose name is based on an MD5 hash of the query that generated the particular result. Because MD5 is case-sensitive, the file container is case-sensitive, too. This means that if the results of SELECT * FROM zodiac are in the cache, and you run the query SELECT * from zodiac, the results aren't found in the cache, and the query is run again. Maintaining consistent capitalization, spacing, and field ordering when constructing your SQL queries results in more efficient cache usage.

Although this recipe focuses on the file container, the PEAR Cache system supports a number of other containers that hold cached data, such as shared memory, PHPLib sessions, databases via the dbx library, and msession sessions. To use a different container, pass the appropriate container name as the first argument when creating a new Cache_DB object:

$cache = new Cache_DB('shm');