Retrieving random records

tequilacat

Hello,

I've got a MySQL database and am trying to retrieve one random record and display it on my website. (Each time the page loads I want a different record to appear.) I know how to list all records but not just one at random. Any help would be appreciated. Here's the select and display all records code:

<?php 
$link = @mysql_connect("  ") or die('FAILED DATABASE CONNECTION');  

mysql_select_db("  ");    

$linkID = @mysql_connect("  ");  

mysql_select_db("  , $linkID);  


$resultID = mysql_query("SELECT DISTINCTROW tblMain.BusinessName,tblMain.Phone1   

FROM tblMain WHERE tblMain.State='AZ' ");
while (list($BusinessName, $Phone1) = mysql_fetch_row($resultID))


{
echo 
"$BusinessName<br> 
$Phone1<br>
<br>";


}


mysql_close($linkID);

?>

Thanks in advance!

BooRadLey

Try this out:

$resultID = mysql_query("SELECT DISTINCTROW tblMain.BusinessName,tblMain.Phone1 FROM tblMain WHERE tblMain.State='AZ' ORDER BY rand() LIMIT 1");

Hope this helps,

Brad

Sxooter

Note that order by rand() is fine as long as the table isn't huge. If it's a big table, you can get better results with

$count = select count() from table;
$offset = rand(0,$count);
select from table limit $offset,1

Note that's pseudo code. You'll have to put in the mysql_query and mysql_result stuff to make it work.

tequilacat

Thanks! I'll try both ways.

Sxooter...how many records would you consider big? Right now I've got about 300 but expect it to grow to 1000 or more eventually.