How to get random ID

I want to get a random id from a list of all id's in my database table. For example, I have id's 1,2,4,5,6,7,9,12,14,16,17,etc... I want to get a random id from that group, how can I do it?

Here's some code I have:

$randq = mysql_query("SELECT * FROM urls WHERE status='Active'");
$maxn = mysql_num_rows($randq);

$randid = mt_rand(1, $maxn);

Code (markup):

$maxn is set to get any number basically but some of the id's are deleted (such as 3,8,10,11,13,15, etc. as shown in the example above). It really only needs to get those id's that exist in the table though.

Can anyone help?

 

If you want it straight from the db use

SELECT id FROM table
ORDER BY RAND()
LIMIT 1

Code (markup):

If you have the ids in an array, let me know and I can post for that.

 

Yeah, if they are in an array, how could I get it to work with something similar to the following:

$randid = mt_rand(1, $maxn);

 

you can use shuffle()

$randq = mysql_query("SELECT * FROM urls WHERE status='Active'");
$i=-1;
while($row=mysql_fetch_array($randq))
{
$i++;
$ids[$i]=$row['id'];
}

shuffle($ids);

$randID=$ids[0];

each time you will get random ID from $ids[0];

coderbari's homepage

 

It would be much more efficient to use my solution. Instead of pulling all ids and then letting php decide which one, just let mysql do the selecting and return 1 id.

SELECT * 
FROM urls 
WHERE status='Active' 
ORDER BY RAND()
LIMIT 1

Code (markup):

But that is only if you want the fastest and most scalable approach.

 

Here's my full code and may better help in a resolution, sorry for not posting it earlier:

<?
$randq = mysql_query("SELECT * FROM urls WHERE status='Active'");
$maxn = mysql_num_rows($randq);
?>

Code (markup):

<?
$i3 = 1;
$dispn = 10;
while($i3 < $dispn) {
$randid = mt_rand(1, $maxn);
$wrandq = mysql_query("SELECT * FROM urls WHERE id='$randid' AND status='Active'");
$wranda = mysql_fetch_array($wrandq); 
?>
<tr>
<td align="center" width="5%"><? echo $i3; ?></td>
<td align="center" width="15%"><? echo $wranda['date']; ?></td>
<td align="center" width="5%"><? echo $wranda['hits']; ?></td>
<td align="center" width="10%"><? echo $wranda['id2']; ?></td>
<?
$wrandaurl = $wranda['url'];
?>
<td align="center" width="65%"><a target='_blank' href='http://nuurl.us/<? echo $wranda['id2']; ?>'><? echo shrink_url( $wrandaurl ); ?></a></td>
</tr>
<?
$i3++;
}
?>

Code (markup):

So can someone help now? I believe the problem is probably with the $wrandq = mysql_query("SELECT * FROM urls WHERE id='$randid' AND status='Active'"); line because $randid could be equal to 4 in which there is no ID 4 since it was deleted in the database.

You can see the blank lines in the middle section on http://nuurl.us/stats.php if you refresh the page over and over.

Looking forward to the replies...

 

I'd go down the ORDER BY RAND() route, as suggested by ccasselman.

 

So how can I incorporate that into my code?

 

Replace this:
$randq = mysql_query("SELECT * FROM urls WHERE status='Active'");

with this:
$randq = mysql_query("SELECT * FROM urls WHERE status='Active' ORDER BY RAND() LIMIT 1");

This will return a single, random row from your 'urls' table.

 

$sql = mysql_query("SELECT * FROM urls WHERE status='Active' ORDER BY RAND()
LIMIT 1");

$result = mysql_query($sql);

Code (markup):

Then you can grab the values from that using mysql_fetch_array or whatever you want. It'll only return one row.

 

But it's selecting the actual data from these lines isn't it?

$randid = mt_rand(1, $maxn);
$wrandq = mysql_query("SELECT * FROM urls WHERE id='$randid' AND status='Active'");

 

No - the sql reply is 1 row only. That does away with the php need to randomly select a row. The mySQL query picks a single, random row from your database.

 

Well dang that was simple! Thanks for your help.