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]
Grabbing 5 random and unique rows from a database
Moderator: General Moderators
-
Paddy
- Forum Contributor
- Posts: 244
- Joined: Wed Jun 11, 2003 8:16 pm
- Location: Hobart, Tas, Aussie
- Contact:
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.
$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.
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