Page 1 of 1

Grabbing 5 random and unique rows from a database

Posted: Mon Nov 17, 2003 12:32 am
by BalSagoth
Hello,

I want to grab 5 randomly selected rows from a table and output it in HTML. I wrote a script that counted the number of rows in a table (somewhere around 200 of them). Then, I used the rand() function to generate random numbers between 0 and the number of rows in the table, so I could then pick a random field and output it. However, I am relying on the "id" field, because it is numerically indexed. There are a good 20 numbers that are missing because they have been deleted. This means that my results sometimes show blanks, because the randomly selected field does not exist. Any ideas on how I can only select tables randomly that actually exist? Knowing this, can I select 5 unique ones so I don't have any duplicates?[/mysql_man]

Posted: Mon Nov 17, 2003 12:54 am
by Paddy
I'll give you some psuedocode for you to think about. row[0] is id row[1] is a possible null function

$count = your script to count the rows
$inarray = 0; //no values in result array just yet
$results[] = "";
$row[1] = "";
while ($inarray < 6)
{
while($row[1] == "")
{
$id = some rand function
$result = mysql_query("select * from table where id='".$id."'") or exit();
$row = mysql_fetch_row($result);
}
if (!(in_array($row[0],$results))
{
$results[$inarray] = $row[0];
$inarray++;
}
}

Do whatever code you want with your five results here.

Posted: Mon Nov 17, 2003 2:45 am
by markl999
You could also do:

Code: Select all

SELECT * FROM thetable ORDER BY RAND() LIMIT 5

Posted: Mon Nov 17, 2003 3:15 pm
by Cruzado_Mainfrm
kool i learnt a new thing today thnx to mark!