Random row from your MySQL table with PHP, alternative to ORDER BY rand()

Working with MySQL and PHP is awesome. Its one of the best way to manage your data, but once in a while you may want to do some randomizing. One problem I came across recently was how to select a random row from your MySQL table with PHP. Using the ORDER BY rand() method in your query maybe a bit slow so here's what we've got.

The Premade Method

As I mentioned in the beginning. MySQL actually has a premade method that allows you to select a random row by querying for it.

mysql_query("SELECT * FROM table ORDER BY rand() LIMIT 0,1");

This method is great but if you are randomizing from a large table this method is not very efficient and with a high volume of queries it really puts a strain on your DB.

The New Method – Randomizing 1 Entry

Now here's the new solution by selecting a random row with the offset. This method will query exactly to the random entry; thus, making this process much faster.

$query = "SELECT * FROM table"
$num_rows = mysql_num_rows( mysql_query( $query ) );

//Random Number
$rand_row = rand(0, $num_rows – 1);

//Selecting the Random Row
$rand_row = $query .= " LIMIT $rand_row, 1";

Now you can modify the $query line to include your filters and sorting stuff, whatever you need.

The New Method – Randomizing More than 1 Entry

The above method right now can only select a single row. What if you want to randomized more than one item. Here's a method to select multiple items without overlap.

//Set the Amount of Random Rows you want
$rand_amount = 5;

$query = "SELECT * FROM table"
$num_rows = mysql_num_rows( mysql_query( $query ) );

//Make sure you have more than the amount of random count you want
if ($num_rows <= $rand_amount) {

//An Array of Numbers Choosen
$rand_array = array();

//Do IT!!
for ($i = 1; $i >= $rand_amount; $i++ ) {
do {
$rand_added = FALSE;
$rand_num = rand(0, $num_rows – 1);

//Should We Add to Array
if (!in_array($rand_num, $rand_array)) {

$rand_array[] = $rand_num;
$rand_added = TRUE;
}

} while (!$rand_added);
}

//You now have a set of random row numbers to choose from
//Display The Result of What you have
foreach ($rand_array as $rand_num) {
$new_query = $query . " LIMIT $rand_num, 1";

//Displaying What you have
$current_row = mysql_fetch_array( mysql_query( $new_query ) );
}
}

To be honest you can use the multiple randomizer and just set the random amount to 1 but the other method saves you like 20 lines of code. Imagine how much faster that could be!

Share It

Tags: MySQL, PHP Script, Random

This entry was posted on Thursday, April 22nd, 2010 at 9:28 pm and is filed under MySQL, PHP. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.