Recipe 10.14. Caching Queries and Results

10.14.1. Problem

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

10.14.2. Solution

Use PEAR's Cache_Lite package. It makes it simple to cache arbitrary data. In this case, cache the results of a SELECT query and use the text of the query as a cache key. Example 10-39 shows how to cache query results with Cache_Lite.

Caching query results

<?php require_once 'Cache/Lite.php'; $opts = array(    // Where to put the cached data    'cacheDir' => 'c:/tmp',    // Let us store arrays in the cache   'automaticSerialization' => true,   // How long stuff lives in the cache   'lifeTime' => 600 /* ten minutes */); // Create the cache $cache = new Cache_Lite($opts); // Connect to the database $db = new PDO('sqlite:c:/data/zodiac.db'); // Define our query and its parameters $sql = 'SELECT * FROM zodiac WHERE planet = ?'; $params = array($_GET['planet']); // Get the unique cache key $key = cache_key($sql, $params); // Try to get results from the cache $results = $cache->get($key); if ($results === false) {     // No results found, so do the query and put the results in the cache     $st = $db->prepare($sql);     $st->execute($params);     $results = $st->fetchAll();     $cache->save($results); } // Whether from the cache or not, $results has our data foreach ($results as $result) {     print "$result[id]: $result[planet], $result[sign] <br/>\n"; } function cache_key($sql, $params) {     return md5($sql .                implode('|',array_keys($params)) .                implode('|',$params)); } ?>

10.14.3. Discussion

Cache_Lite is a generic, lightweight mechanism for caching arbitrary information. It uses files to store the information it's caching. The Cache_Lite constructor takes an array of options that control its behavior. The two most important ones in Example 10-39 are automaticSerialization, which makes it easier to store arrays in the cache, and cacheDir, which defines where the cache files go. Make sure cacheDir ends with a /.

The cache is just a mapping of keys to values. It's up to us to make sure that we supply a cache key that uniquely identifies the data we want to cache'in this case, the SQL query and the parameters bound to it. The cache_key function computes an appropriate key. After that, Example 10-39 just checks to see if the results are already in the cache. If not, it executes the query against the database and stuffs the results in the cache for next time.

Note that you can't put a PDO or PDOStatement object in the cache'you have to fetch results and then put the results in the cache.

By default, entries stay in the cache for one hour. You can adjust this by passing a different value (in seconds) as the lifeTime option when creating a new Cache_Lite object. Pass in null if you don't want data to automatically expire.

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_Lite::clean( ) method. You can also remove an individual element from the cache by passing a cache key to Cache_Lite::remove( ).

The cache_key( ) function in Example 10-39 is case sensitive. 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.

10.14.4. See Also

Documentation on Cache_Lite found at

PHP Cookbook, 2nd Edition
PHP Cookbook: Solutions and Examples for PHP Programmers
ISBN: 0596101015
EAN: 2147483647
Year: 2006
Pages: 445

Similar book on Amazon © 2008-2017.
If you may any questions please contact us: