Ordering Database results by “random” in CodeIgniter
August 14th, 2007
A recent CodeIgniter bug report had got me looking into the depths of the database results functions of the framework. Essentially, the orderby() function of CI’s Active Record says that you can sort by ASC (ascending), DESC (descending) or RAND(). Imagine this:
$query = "SELECT * FROM table ORDER BY RAND()";
Anyone familiar with PHP probably looks and that and thinks of the native PHP rand() function. This is pretty neat actually, and I’d never really thought about randomly ordering things. But on further investigation, it became clear that this code wasn’t as nice as it initially seemed. Firstly, its non-standard SQL, reducing its portability greatly. While MySQL uses RAND(), other databases have their own way of doing things:
- MySQL uses ORDER BY RAND()
- PostgreSQL uses ORDER BY RANDOM()
- Oracle uses a subquery with ORDER BY dbms_random.value
- SQL Server users ORDER BY NEWID()
So then, how would one randomly order their database results? The easiest solution is probably to use a combination of CodeIgniter’s result_array() function, that returns your results into an array (surprised?) and my new favourite PHP function, shuffle(), which I seem to be finding a lot of use for lately.
Consider this:
$this->db->select('name');
$query = $this->db->get('table');
$shuffled_query = $query->result_array();
shuffle ($shuffled_query);
foreach ($shuffled_query as $row) {
echo $row['name'] . '<br />';
}
In PHP 5, you could even chain some of these together and save yourself a line or two:
$shuffled_query = $this->db->get('table')->result_array();
shuffle ($shuffled_query);
I played with trying to extend the database library to allow for this (opps, can’t extend the database libraries), then writing a quick helper, then finally realized that its only a few lines of code, and is probably something a developer should just do for themselves. For CodeIgniter to handle this random order, we could abstract out the “random” order into each database library that CI supports. At some point in the future that may happen, but it isn’t even on the radar for the time being.
One notable point about this is that you have to use the array notation for your results ($row[‘field’]) instead of my preferred object notation ($row->field); but this seems a small price to pay. In theory, one could write a function to remap the array into an object, but I’d be disinclined to do that much extra work for the sake of appearances.
Now the question… why would anyone want to randomize results? Well, I can see applications in drawing “random comments” from a blog, “articles of the moment” from a database of writing, and in fact several other day to day uses.
edit: through a totally stupid action on my own part, I lost the last half of this article, and it needed a quick re-write. While it still has most of the content of the original, it doesn’t retain any of my wonderful vocabulary or insightful wit… ;) This is probably the third time I’ve done this on my blog… is anyone out there feeling my pain?
This entry was made on August 14th, 2007 @ 5:15 and filed into CodeIgniter, How-To, PHP.
Eric wrote on August 14th, 2007 @ 6:06
I read on but didn’t see your solution. :)
I would probably use some thing like:
$this->db->orderby(“title”, “rand”);
Then in the active record helper look for the string rand and use the proper SQL.