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
ORDER BY RAND() LIMIT 0,3 : Will this ALWAYS get three?
Moderator: General Moderators
-
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?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.
Re: ORDER BY RAND() LIMIT 0,3 : Will this ALWAYS get three?
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?
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.
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.
All the best from the United Kingdom.
Re: ORDER BY RAND() LIMIT 0,3 : Will this ALWAYS get three?
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?
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.
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;'> 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;'> 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.
All the best from the United Kingdom.
Re: ORDER BY RAND() LIMIT 0,3 : Will this ALWAYS get three?
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
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