Randomly returning rows

[Mike]

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

[chromate]

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?

[Mike]

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?

[Chris]

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,...)

[Mike]

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

Thanks,
Mike

[Chris]

An SQL tutorial covering where statements should show an example.

[chromate]

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

[Mike]

I'll try it tomorrow, thanks Chromate:)

[Nick]

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