PDA

View Full Version : Randomly returning rows



Mike
02-07-2004, 11:14 AM
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
02-07-2004, 11:25 AM
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
02-07-2004, 11:37 AM
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
02-07-2004, 02:01 PM
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
02-07-2004, 02:58 PM
Hmm... do you know of a tutorial Chris please?

Thanks,
Mike

Chris
02-07-2004, 03:49 PM
An SQL tutorial covering where statements should show an example.

chromate
02-07-2004, 04:16 PM
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
02-07-2004, 04:33 PM
I'll try it tomorrow, thanks Chromate:)

Nick
02-07-2004, 08:28 PM
You could also put all the rows into an array, and use a random number to pull from the array.