ORDER BY RAND() LIMIT 0,3 : Will this ALWAYS get three?

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
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

ORDER BY RAND() LIMIT 0,3 : Will this ALWAYS get three?

Post by simonmlewis »

Hi

This seems a stupid question, as it's such a basic query, but we are getting problems where the system has 5-10 items to be chosen, but all too often it picks only 2.

Does this code say "limit up to 3" or "limit to exactly 3". And if it is 'up to', how do I make it to be exactly 3??

Simon
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: ORDER BY RAND() LIMIT 0,3 : Will this ALWAYS get three?

Post by josh »

Its the same as 'limit 3', and yes it should always return 3. This took 2 seconds to test out. Run the query in mysql repeatedly in a short time span, you'll prove to yourself it always returns 3 rows - unless the table has less than 3 rows total, in which case it will return how many rows the table has maximum.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: ORDER BY RAND() LIMIT 0,3 : Will this ALWAYS get three?

Post by simonmlewis »

For me limit 0,3 often produces two rows! Hence the question.
Will try it without the 0, but from what you say, it shouldn't matter at all.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: ORDER BY RAND() LIMIT 0,3 : Will this ALWAYS get three?

Post by josh »

Then your table must have less than 3 rows
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: ORDER BY RAND() LIMIT 0,3 : Will this ALWAYS get three?

Post by simonmlewis »

I assure you, it doesn't.

This very often produces just TWO products. But I assure you there are 5 or more in the syste, for the category.

Code: Select all

if ($row->accessoriesid != NULL || $row->accessoryoverride != NULL) {
echo "<div class='productdescription_main'><div class='sectionhomehead'>Accessories for this item [not included]</div></div>";}

echo "<div class='productgallerybox'>";

if ($row->accessoryoverride == "yes")
{
  $cataccessories = mysql_query ("SELECT * FROM accessories WHERE catid = '$c' ORDER BY RAND() LIMIT 0,3");
  while ($accslist = mysql_fetch_object($cataccessories))
  {
    $accessoriesp = mysql_query ("SELECT id, subid, catid, rcstock, catname, subname, title, photoprimary, price FROM products WHERE id = '$accslist->prodid'");
    while ($accsp = mysql_fetch_object($accessoriesp))
    {
 $title = "$accsp->title"; 
 $findtitle ="/ /"; 
 $replacetitle ="-"; 
 $titlereplace = preg_replace ($findtitle, $replacetitle, $title); 

 $categ = "$accsp->catname"; 
 $findcateg ="/ /"; 
 $replacecateg ="-"; 
 $categreplace = preg_replace ($findcateg, $replacecateg, $categ); 

 $subcateg = "$accsp->subname"; 
 $findsubcateg ="/ /"; 
 $replacesubcateg ="-"; 
 $subcategreplace = preg_replace ($findsubcateg, $replacesubcateg, $subcateg); 
 
      echo " <div class='productsmallbox'><a href='/product/$accsp->catid/$categreplace/$accsp->subid/$subcategreplace/$accsp->id/$titlereplace' title='Look at the $accsp->title' style='text-decoration: none'><img src='/images/productphotos/small/$accsp->photoprimary' border='0' style='border: 1px solid #000000'/></a><br/>
      <img src='/images/imageshadow.png' border='0' class='shadowmedium' /><br/>
      $accsp->title  
      <script language=\"javascript\">
function buy()
{ document.buynow$accslist->id.submit();}
</script>
<form action='domain' method='post' name='buynow$accslist->id'>
<input type=hidden name=itemcode value='$accsp->code'>
<input type=hidden name=storeid value='****'>";
printf ("£%.2f", $accsp->price);    
  if ($accsp->rcstock == 'in stock') { echo " <a href=\"javascript:buy();\" style='text-decoration: none; font-weight: bold; color: #FF7C09;'>&nbsp;&nbsp;&nbsp;BUY NOW</a>
";}
  if ($accsp->rcstock == 'out of stock')  { echo " Out of stock";}
      echo"</form></div>";
  }}
}
else
{
$string = "$row->accessoriesid";
      $token = strtok($string,"|");
      while($token) {
            $accessories = mysql_query ("SELECT id, price, catname, catid, subname, subid, photoprimary, code, title, rcstock FROM products WHERE id = '$token'");
            while ($accslist = mysql_fetch_object($accessories))
            {
            
 $title = "$accslist->title"; 
 $findtitle ="/ /"; 
 $replacetitle ="-"; 
 $titlereplace = preg_replace ($findtitle, $replacetitle, $title); 

 $categ = "$accslist->catname"; 
 $findcateg ="/ /"; 
 $replacecateg ="-"; 
 $categreplace = preg_replace ($findcateg, $replacecateg, $categ); 

 $subcateg = "$accslist->subname"; 
 $findsubcateg ="/ /"; 
 $replacesubcateg ="-"; 
 $subcategreplace = preg_replace ($findsubcateg, $replacesubcateg, $subcateg); 
 
      echo " <div class='productsmallbox'><a href='/product/$accslist->catid/$categreplace/$accslist->subid/$subcategreplace/$accslist->id/$titlereplace' title='Look at the $accslist->title'>";
      if ($accslist->photoprimary != NULL) { echo "<img src='/images/productphotos/small/$accslist->photoprimary' border='0' height='145px;' style='border: 1px solid #000000'/><br/>
      <img src='/images/imageshadow.png' border='0' class='shadowmedium' />";}
      if ($accslist->photoprimary == NULL) { echo "<img src='/images/blank.gif' border='0' height='145px;'>";}
      echo "</a><br/>$accslist->title<br/>
      <script language=\"javascript\">
function buy()
{ document.buynow$accslist->id.submit();}
</script>
<form action='http://www.cart.com/cart.asp' method='post' name='buynow$accslist->id'>
<input type=hidden name=itemcode value='$accslist->code'>
<input type=hidden name=storeid value='44175'>";
printf ("£%.2f", $accslist->price);    
  if ($accslist->rcstock == 'in stock') { echo " <a href=\"javascript:buy();\" style='text-decoration: none; font-weight: bold; color: #FF7C09;'>&nbsp;&nbsp;&nbsp;BUY NOW</a>
";}
  if ($accslist->rcstock == 'out of stock')  { echo " Out of stock";}
      echo"</form></div>";
}
$token = strtok("|");
}
}
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: ORDER BY RAND() LIMIT 0,3 : Will this ALWAYS get three?

Post by josh »

I didn't read your PHP code, you're in the database forum. I think you'll find it easier to take just the SQL query, goto the command line or phpmyadmin and run it in mysql. I bet you'll get consistent results each time you run it.

Also post the SQL here in case there's something you haven't shown us.

PS > I skimmed the first 5 lines of your code, I already see that the number of rows depends on the value of $c. Exactly why I told you to look at the final SQL not the code ;-)
Post Reply