Grabbing 5 random and unique rows from a database

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
BalSagoth
Forum Newbie
Posts: 4
Joined: Mon Nov 17, 2003 12:32 am

Grabbing 5 random and unique rows from a database

Post 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]
Paddy
Forum Contributor
Posts: 244
Joined: Wed Jun 11, 2003 8:16 pm
Location: Hobart, Tas, Aussie
Contact:

Post 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.
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post by markl999 »

You could also do:

Code: Select all

SELECT * FROM thetable ORDER BY RAND() LIMIT 5
Cruzado_Mainfrm
Forum Contributor
Posts: 346
Joined: Sun Jun 15, 2003 11:22 pm
Location: Miami, FL

Post by Cruzado_Mainfrm »

kool i learnt a new thing today thnx to mark!
Post Reply