Php random row help...

Question

I've created some code that will return a random row, (well, all the rows in a random order) But i'm assuming its VERY uneffiecent and is gonna be a problem in a big database...

Anyone know of a better way?

Here is my current code:

$count3 = 1;
$count4 = 1;
//Civilian stuff...
$query = ("SELECT * FROM `*Table Name*` ORDER BY `Id` ASC");
$result = mysql_query($query);
while($row = mysql_fetch_array($result)) {
$count = $count + 1;
$civilianid = $row['Id'];
$arrayofids[$count] = $civilianid;
//echo $arrayofids[$count];
}

while($alldone != true) {
$randomnum = (rand()%$count) + 1;
//echo $randomnum . "<br>";
//echo $arrayofids[$randomnum] . "<br>";
$currentuserid = $arrayofids[$randomnum];
$count3 += 1;

while($count4 < $count3) {
$count4 += 1;
$currentarrayid = $listdone[$count4];
//echo "<b>" . $currentarrayid . ":" . $currentuserid . "</b> ";
if ($currentarrayid == $currentuserid){
$found = true;
//echo " '" .$found. "' ";
}
}

if ($found == true) {
//Reset array/variables...
$count4 = 1;
$found = false;
} else {
$listdone[$count3] = $currentuserid;
//echo "<u>" . $count3 .";". $listdone[$count3] . "</u> ";
$query = ("SELECT * FROM `*Tablesname*` WHERE Id = '$currentuserid'");
$result = mysql_query($query);
$row = mysql_fetch_array($result);
$username = $row['Username'];
echo $username . "<br>";
$count4 = 1;
$amountdone += 1;
if ($amountdone == $count) { //$count
$alldone = true;
}
}
}

Basically it will loop until its gets an id (randomly) that hasnt been chosen yet. -So the last username could take hours :P

Is this 'bad' code? :P :(

Answer 1

You could delegate that to MySQL:

SELECT * FROM table_name ORDER BY RAND() LIMIT 1;

It will return a random row from your table, which I guess should be more efficient than your php solution. Nevertheless, it would still be slow within a large database.

You may be interested in checking out the following Stack Overflow posts for alternative solutions:

• Alerternative to MySQL Order By Rand()
• ORDER BY RAND() alternative

Answer 2

You could either change the query to give you all the results in a random order...

$query = ("SELECT * FROM `*Table Name*` ORDER BY RAND()");

Then just display them all in a simple loop.

Or you could get all the results into an array, then randomise the order in the array. You can use the php shuffle() function for that. http://www.php.net/manual/en/function.shuffle.php

I would assume that the first option would give you the best results, but the correct solution will be to measure the performance and try to optimise if it is "too slow".

However, picking a random element from your array, seeing if you have done that one already and trying again if you have is horrific. Do anything except that.