如何从MYSQL表中显示3个随机值

问题描述：

How would I go about displaying 3 random values each time a page is loaded? At the minute I have it displaying one at a time using the following code:

<?php
$sSQLQuery = "SELECT careername FROM careers ORDER BY RAND() LIMIT 1";  
$aResult = mysql_query($sSQLQuery);  
$aRow = mysql_fetch_array($aResult, MYSQL_ASSOC);  
$sQuoteOfTheDay = $aRow['careername']; 
echo $sQuoteOfTheDay;   
?>

I have tried doing LIMIT 5 but that had no effect on the result. Thanks

每次加载页面时，如何显示3个随机值？在那一刻，我使用以下代码一次显示一个： p>

 ＆lt;？php 
 $ sSQLQuery =“SELECT careername FROM careers ORDER BY RAND（）LIMIT  1" ;  
 $ aResult = mysql_query（$ sSQLQuery）;  
 $ aRow = mysql_fetch_array（$ aResult，MYSQL_ASSOC）;  
 $ sQuoteOfTheDay = $ aRow ['careername'];  
echo $ sQuoteOfTheDay;  
？＆gt; 
  code>  pre> 
 
 我尝试过LIMIT 5，但这对结果没有影响。 谢谢 p> 
  div>

答

You'll have to call mysql_fetch_assoc() again for every single record your are fetching.

So, use LIMIT 3 and then simply loop through the result set. Like this:

$sSQLQuery = "SELECT careername FROM careers ORDER BY RAND() LIMIT 3";  
$aResult = mysql_query($sSQLQuery);  
while($aRow = mysql_fetch_array($aResult, MYSQL_ASSOC)) {
    $sQuoteOfTheDay = $aRow['careername']; 
    echo $sQuoteOfTheDay; 
}

Refer to the manual page of mysql_fetch_assoc(). You'll find more examples there.

答

You are on the right track. Change your LIMIT to '3' and then loop through all of the results:

while ($aRow = mysql_fetch_array($aResult, MYSQL_ASSOC)) {
    echo $aRow['careername']; 
}

答

You shall change the LIMIT to 3 and iterate through the result rows.

$sSQLQuery = "SELECT careername FROM careers ORDER BY RAND() LIMIT 3";  
$aResult = mysql_query($sSQLQuery);  
while ($aRow = mysql_fetch_array($aResult, MYSQL_ASSOC))
{
    $sQuoteOfTheDay = $aRow['careername']; 
    echo $sQuoteOfTheDay; 
}
$aResult->free();

Don't forget to free $aResult at the end.

答

ORDER BY RAND() LIMIT n is a horror performance killer: it will calculate RAND() for all rows in the table, then throw away most. Imagine what this does to a table with a million rows.

The best way to do this, is to get the rowcount (e.g. run SELECT COUNT(*) FROM tablename;), then use this value to calculate three indices, then run SELECT * FROM tablename ORDER BY primarykey LIMIT $index,1 three times.

The break even for this on a CPU-bound server is at ca. 100 rows!

如何从MYSQL表中显示3个随机值

相关推荐