PHP: Cache SQL results on file system.

This is a simple tutorial on how to use PHP to cache the results of an SQL query to the file system. In this tutorial, we will be using the PDO object to query MySQL – IF a valid cached result does not already exist. This is particularly handy for developers that do not have access to memory object caching systems such as Memcached.

Take a look at the PHP code below:

<?php

//Our SQL query.
$sql = "SELECT id, title, date_posted FROM posts ORDER BY date_posted DESC LIMIT 2";

//Generate an MD5 hash from the SQL query above.
$sqlCacheName = md5($sql) . ".cache";


//The name of our cache folder.
$cache = 'cache';


//Full path to cache file.
$cacheFile = $cache . "/" . $sqlCacheName;


//Cache time in seconds. 60 * 60 = one hour.
$cacheTimeSeconds = (60 * 60);

//Our results array.
$results = array();

//If the file exists and the filemtime time is larger than
//our cache expiry time.
if(
    file_exists($cacheFile) && 
    (filemtime($cacheFile) > (time() - ($cacheTimeSeconds)))
){
    echo 'Cache file found. Use cache file instead of querying database.';
    //Get the contents of our cached file. 
    $fileContents = file_get_contents($cacheFile);
    //Decode the JSON back into an array.
    $results = json_decode($fileContents, true);
} else{
    echo 'Valid cache file not found. Query database.';
    //Cache file doesn't exist or has expired.
    //Connect to MySQL using PDO.
    $pdo = new PDO('mysql:host=localhost;dbname=test', 'root', '');
    //Prepare the SQL statement.
    $stmt = $pdo->prepare($sql);
    //Execute.
    $stmt->execute();
    //Fetch the results.
    $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
    //Convert the results into JSON so that we can save them to our cache file.
    $resultsJSON = json_encode($results);
    //Save the contents to our cache file.
    file_put_contents($cacheFile, $resultsJSON);
}


//Loop through the $results array, etc.
foreach($results as $row){
    var_dump($row);
}

An explanation of what is happening in the code above:

  1. We constructed the SQL query that we want to cache.
  2. We create the filename for our cache file by hashing the SQL query in question. This ensures that each query will have its own unique filename. Note that changing the query will change the hash and “invalidate” the cache.
  3. We specify the folder that our files will be stored in. If you are retrieving and displaying sensitive data, then you will need to make sure that this folder is not publicly accessible.
  4. We create the full path to the cache file.
  5. We set the number of seconds that the file cache should last for. In the example above, we set it to 60 x 60 seconds, which is 3600 seconds (one hour).
  6. We create an empty array called $results.
  7. We check to see if our cache file exists and that it was last modified less than 3600 seconds ago. To get the last modified time of a given file, we use the PHP function filemtime. Note that filemtime returns a UNIX timestamp.
  8. If a valid cache file is found, we load the file and decode the JSON content into a PHP associative array ($results).
  9. If a valid cache file is not found, we connect to MySQL using PDO before executing our query. After the query is executed, we encode the results to a JSON string before saving it to the cache file.