The most simple way to cache MySQL query results using PHP?

Question

Each time someone lands in my page list.php?id=xxxxx it requeries some MySQL queries to return this:

$ids = array(..,..,..); // not big array - not longer then 50 number records
$thumbs = array(..,..,..); // not big array - not longer then 50 text records
$artdesc = "some text not very long"; // text field

Because the database from which I make the queries is quite big I would like to cache this results for 24h in maybe a file like: xxxxx.php in a /cache/ directory so i can use it in include("xxxxx.php") if it is present. ( or txt files !? , or any other way )

Because there is very simple data I believe it can be done using a few of PHP lines and no need to use memcached or other professional objects.

Becasuse my PHP is very limited can someone just place the PHP main lines ( or code ) for this task ?

I really would be very thankfull !

I have modified the answer to add solution for 24 hour cache validity. See http://stackoverflow.com/a/15748442/369005 — Hamid Sarfraz, Apr 02 '13 at 14:55

score 9 · Answer 1 · answered Apr 01 '13 at 15:24

9

Caching a PHP array is pretty easy:

file_put_contents($path, '<?php return '.var_export($my_array,true).';?>');

Then you can read it back out:

if (file_exists($path)) $my_array = include($path);

You might also want to look into ADOdb, which provides caching internally.

answered Apr 01 '13 at 15:24

Adrian

42,911
6
107
99

Many thanks for placing this way to approach this issue. It did point me to build a simple solution for my case ! – Pedro P Apr 03 '13 at 10:11

Hamid Sarfraz · Answer 2 · 2013-04-02T14:59:48.913

Try using serialize;

Suppose you get your data in two arrays $array1 and $array2. Now what you have to do is store these arrays in file. Storing string (the third variable in your question) to file is easy, but to store an array you have to first convert it to string.

$string_of_array1 = serialize( $array1 );
$string_of_array2 = serialize( $array2 );

The next problem is the naming of cache files so that you can easily check if the relevant array is already available in cache. The best way to do this is to create an MD5 hash of your mysql query and use it as cache file name.

$cache_dir = '/path/cache/';

$query1 = 'SELECT many , fields FROM first_table INNER JOIN another_table ...';
$cache1_filename = md5( $query1 );

if( file_exists( $cache_dir . $cache1_filename ) )
{
    if( filemtime( $cache_dir . $cache1_filename ) > ( time( ) - 60 * 60 * 24 ) )
    {
        $array1 = unserialize( file_get_contents( $cache_dir . $cache1_filename ) );
    }
}

if( !isset( $array1 ) )
{
    $array1 = run_mysql_query( $query1 );
    file_put_contents( serialize( $array1 ) );
}

Repeat the above with the other array that should be stored in a separate file with MD5 of the second query used as the name of second cache file.

In the end, you have to decide how long your cache should remain valid. For the same query, there may change records in your mysql table that may make your file system cache outdated. So, you cannot just rely on unique file names for unique queries.

Important:

Old cache files have to be deleted. You may have to write a routine that checks all files of a directory and deletes the files older than n seconds.
Keep the cache dir outside the webroot.

Think I got it. Will try to implement it following the steps you describe. Many thanks Hamid. — Pedro P, Apr 01 '13 at 17:48

score 0 · Answer 3 · answered Apr 01 '13 at 15:26

0

Just write a new file with the name of the $_GET['id'] and contents of the stuff you want cached, and each time check to see if that file exists, else create one. Something like this:

$id = $_GET['id']

if (file_exists('/a/dir/' . $id)) {
  $data = file_get_contents('/a/dir/' . $id);
} else {
  //do mysql query, set data to result
  $handle = fopen('/a/dir/' . $id, 'w+');
  fwrite($handle, $data);
  fclose($handle);
}

answered Apr 01 '13 at 15:26

Lemon Drop

2,113
2
19
34

6

And then someone calls your script with `script.php?id=../../etc/passwd` or something else funny. Sanitize user input... – DCoder Apr 01 '13 at 15:39
Ty lemondrop; for what i understand the file itself does not have any extension, right? How can I put my 3 items ( 2 array + 1 variable ) in the $data ? Also if you could provide me the way to check for 24h cache that would be great !! – Pedro P Apr 01 '13 at 16:34

score 0 · Answer 4 · answered Sep 06 '22 at 11:23

Based on @hamid-sarfraz answer, here is a solution used in PDO extended class, using json_encode/decode instead of serialize :

function get_assoc_c($query, $lifetime = 60*60*24) {
    
    $c_dir = '/path/to/.cache/';
    $c_filename = md5($query);
    
    if(file_exists($c_dir . $c_filename)) {
        if(filemtime($c_dir . $c_filename) > (time() - $lifetime)) {
            return json_decode(file_get_contents($c_dir . $c_filename), true);
        }
    }
    
    if(!isset($content)) {
        
        if(!file_exists($c_dir))
            mkdir($c_dir);
    
        $stmt = $this->query($query);
        $content = $stmt->fetchAll(PDO::FETCH_ASSOC);

        file_put_contents($c_dir . $c_filename, json_encode($content));
        return $content;
    }
    
    return false;
}

! Be aware to not use queries with arguments passed as variables (SQL injection).

The most simple way to cache MySQL query results using PHP?

4 Answers4

Linked

Related