Randomly returning rows

Hi all,

I would like to randomly return rows from a basic database. I would do this through rand() but it would take quite a while with all the if statements imho.

So can anyone think of a quicker way?

Thanks,
Mike

if statements?

Just count the number of rows returned and set the range of the rand function from 0 to count_num_rows($result). Then use the number returned to pull that row from the results.

Is this what you mean?

Sorry, I should have explained better. I want about ten results to be returned, that's why I said if statements.

Or would you just do what you said ten times?

If you knew the total number of rows you can generate 10 random numbers and then query the DB with those in a single query (where rowid in (55, 56, 87,...)

Hmm... do you know of a tutorial Chris please?

Thanks,
Mike

An SQL tutorial covering where statements should show an example.

PHP Code:

---

for ($i=0; $i < 10; $i++) {
   $rnd_row = rand(0, $total_rows);
   if ($i = 0) $row_ids = "rowid = '$rnd_row' ";
   $row_ids .= "OR rowid = '$rnd_row' ";
}

$sql = "SELECT * FROM blah WHERE $row_ids";
$result = mysql_query($sql, $db); 


---

Something like that... Probably wont work right first time... I just bashed it out quickly cause I don't have much time

I'll try it tomorrow, thanks Chromate:)

You could also put all the rows into an array, and use a random number to pull from the array.