Require reasonably random results from an SQL SELECT query within a Joomla article (Cache enabled)
- by Shrinivas
Setup: Joomla website on LAMP stack
I have a MySQL table containing some records, these are queried by a simple SELECT on the Joomla article, as pasted below. This specific Joomla website has Caching turned on in Joomla's Global Configuration. I need to randomize the order in which I display the resultset, each time the page is loaded.
Regular php/mysql would offer me two approaches for this:
1. use 'order by RAND()' or any of a number of methods to allow a SELECT query to return reasonably random results.
2. once php gets the result from the SELECT into an array, shuffle the array to get a reasonably random order of array items.
However, as this Joomla instance has Caching turned ON in its Global Configuration, either of the above approaches fails. The first time I load the page the order is randomized, however any further reloads do not cause the order to change, as the page is delivered from cache. The instant the Cache is disabled, both approaches (shuffle/order by rand) work perfectly. What am I missing? How do I override the Global Cache for this specific article? A very simple requirement, that is met by both php and mysql reasonably well, is blocked by the Joomla Cache that I cannot turn off.
The php that returns results from the database.
<pre>
$db = JFactory::getDBO();
$select = "SELECT id FROM jos_mytable;"; //order by RAND()
$db->setQuery($select);
echo $db->getQuery(); //Show me the Query!
$rows = $db->loadObjectList();
//shuffle($rows);
foreach($rows as $row) {
echo "$row->id";
}
