PHP Cookbook

10.14. Caching Queries and Results

Problem

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

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";
}

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 ...