How to Cache a MySQL query result?

flashdeveloper

I have been looking at PEAR Cache to minimize the load on our MySQL database when the data does not have to be that fresh.

So far I have had no luck getting the PEAR classes to work consistantly for me, and I was hoping for an alternative solution to caching MySQL query results.

bastien

what about writing to a text file and then reading the file when needed...unfortunately I don't beleive that PHP has application level variables similar to what ASP has to be able to cache the results like that

superwormy

MySQL does some query caching on its own, to cache an entire result set you could print out the result set into some format you can use later. The best option might be to put it into a multi-dimensional array... then serialize() ( www.php.net/serialize ) the resulting array to store for later.

$result = mysql_query ("SELECT * FROM table");
while ($array = mysql_fetch_array ($result)) {

$resultset_array[] = $array;

}

$string = serialize ($resultset_array);

$open = fopen ("mycachefile.txt", "w+");
fwrite ($string, $open);
fclose ($open);

flashdeveloper

That certainly solves one peice of the puzzle. Now we need to create a way to check the age of the file and read from the serialized data if < expire time, or re-run the query and replace the file with fresh results.

I was hoping somebody had created a class for this, because I a very lazy 🙂

It seems like something common that someone would have conquered already. Maybe I'll try the PEAR cache again.

superwormy

For that you might be able to keep a real simple table in your database...

ID, QUERY_STRING, datetime

And every query, run a quick query against that page to see if its cached or not and if the cache is too old, then decide what to do...

Don't be a lazy PHP developer, do it yourself :-)