php Cache control

I want to do a query to select 20 items to display on my homepage.

Example:

mysql_query("SELECT * FROM vids ORDER BY rand() LIMIT 20");

PHP:

But I would like it to cache the results and show those 20 random items for 24 hours and then select a new set of results.

Is there a simple way to do this with cache?

Thanks

 

MySQL in itself has a caching mechanism already, however if you think it would help you, something like this shortly after the first query executes:

keep in mind not all hosting supports file_get_contents and file_put_contents so you may have to turn to fopen

$row = mysql_fetch_array($result);
$refresh = time() + (24 * 60 * 60); // unix time stamp 24 hours from now
$data = array("expire" => $refresh, "data" => $row);
$data_to_store = serialize($data);

file_put_contents('mycache.txt', $data_to_store);

PHP:

Then where you would normally do the query:

$cache = file_get_contents('mycache.txt');
$cache = unserialize($cache);
if($cache['expire'] < time())
{
// do a new query and update cache
}
else
{
$row = $cache['data'];
}

PHP:

Now your $row is just like it was when it would have come out of a database query.

Question to ask yourself though... does accessing a file within PHP take more resources than accessing the database?

 

Whats the mysq caching mechanism? I haven't used that before.

 

You use it if you use MySQL, its automated (as the server runs it keeps certain queries in memory so that the predictable results can more quickly be retreived). Though it does have a "Query Cache" http://dev.mysql.com/doc/refman/5.0/en/query-cache.html

Trying to cache on that level can be quite a bit technical and is usually not needed unless you're running some kind of high level enterprise website, and usually going with something like memcached is a better route (since mysql, php, apache, etc can use a memcached server)

 

I'm not sure the query cache would work for ORDER BY rand() queries.. The first method that kblessinggr suggested is more than good enough for your purposes at the moment.

 

You can add a column to the table, "rand_id". Setup a cron job which updates this column every 24 hours.

UPDATE table SET rand_id = RAND()

Code (markup):

Then it's just a simple query.

SELECT * FROM vids ORDER BY rand_id $direction LIMIT 20

Code (markup):

If you have something you can depend on to give you an alternating result for a modulus of 2, you can run the cron job every 48 hours and just toggle the sort direction.

 

Well joebert, wouldn't that result in the same rows always being selected? Those that have the lowest or highest ids will remain selected.

 

Yes it would, until the cron job runs again 24 hours later. Doing it like this lets the query cache save it though.

Unless I read it wrong, it looks like the intended result is to have the same results for 24 hours.

 

Oh, I misread things a little. Sorry

 

If you really want to make a difference, you can look to something like memcached to store the result of the query.