[MySQL/PHP] Avoid using RAND()
- by Andrew Ellis
So... I have never had a need to do a random SELECT on a MySQL DB until this project I'm working on. After researching it seems the general populous says that using RAND() is a bad idea. I found an article that explains how to do another type of random select. Basically, if I want to select 5 random elements, I should do the following (I'm using the Kohana framework here)? If not, what is a better solution?
Thanks,
Andrew
<?php
final class Offers extends Model
{
/**
* Loads a random set of offers.
*
* @param integer $limit
* @return array
*/
public function random_offers($limit = 5)
{
// Find the highest offer_id
$sql = '
SELECT MAX(offer_id) AS max_offer_id
FROM offers
';
$max_offer_id = DB::query(Database::SELECT, $sql)
->execute($this->_db)
->get('max_offer_id');
// Check to make sure we're not trying to load more offers
// than there really is...
if ($max_offer_id < $limit)
{
$limit = $max_offer_id;
}
$used = array();
$ids = '';
for ($i = 0; $i < $limit; )
{
$rand = mt_rand(1, $max_offer_id);
if (!isset($used[$rand]))
{
// Flag the ID as used
$used[$rand] = TRUE;
// Set the ID
if ($i > 0) $ids .= ',';
$ids .= $rand;
++$i;
}
}
$sql = '
SELECT offer_id, offer_name
FROM offers
WHERE offer_id IN(:ids)
';
$offers = DB::query(Database::SELECT, $sql)
->param(':ids', $ids)
->as_object();
->execute($this->_db);
return $offers;
}
}
