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:

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.