Page 1 of 1

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

Posted: Thu Feb 16, 2012 4:41 am
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

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

Posted: Thu Feb 16, 2012 10:24 am
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.

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

Posted: Thu Feb 16, 2012 10:31 am
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.

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

Posted: Thu Feb 16, 2012 11:15 am
by josh
Then your table must have less than 3 rows

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

Posted: Thu Feb 16, 2012 11:34 am
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("|");
}
}

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

Posted: Thu Feb 16, 2012 8:23 pm
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 ;-)