Page 1 of 1
Get Average and show only 5 Stars - doesn't work :(
Posted: Fri Sep 03, 2010 5:24 am
by simonmlewis
hi - I thought this would be plain sailing, but it isn't.
People are voting by Stars on products. 1 - 5.
I want to show on this page, only those that are a 5 star average, (or 4 star average perhaps), and then select the product information to display it on screen.
Code: Select all
echo "<div class='sectionhomehead'>Recommended by you - our customers</div>";
$rating = mysql_query ("SELECT prodid, AVG(stars) FROM rating GROUP BY prodid") or die (mysql_error());
while ($rat = mysql_fetch_array($rating))
{
$starrating = number_format($rat['AVG(stars)']);
if ($starrating == "5") {
$product = mysql_query ("SELECT id, catid, subid, catname, subname, photoprimary, title, price FROM products WHERE id = $rat['prodid']");
while ($row = mysql_fetch_object($product))
{
echo "<div class='cat_prodlistbox'><a href='index.php?page=product&product=$row->id&s=$row->subid&c=$row->catid&cname=$row->catname&sname=$row->subname&menu=sub&head=$row->title' style='text-decoration: none'>";
if ($row->photoprimary == NULL) { echo "<img src='images/blank.gif' border='0' width='130px' height='101px' />";}
elseif ($row->photoprimary != NULL) { echo "<img src='images/productphotos/small/$row->photoprimary' width='130px' height='101px' border='0' />";}
echo "<br/>
$row->title<br/>Only ";
printf ("£%.2f", $row->price);
echo "<br/><img src='images/stars5.png' width='105px' border='0' /></a></div>";
}
} mysql_free_result($product);
} mysql_free_result($rating);
It produces this error. home.inc is the file with the code.
Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in C:\xampp\phpMyAdmin\site\includes\home.inc on line 42
EDIT: Or can you do a query that selects everything from the Ratings table where the AVERAGE is 5?
Re: Get Average and show only 5 Stars - doesn't work :(
Posted: Fri Sep 03, 2010 8:54 am
by mikosiko
seems that you have an unbalanced # of {}.... could be the one in this line if you posted your full code.
Re: Get Average and show only 5 Stars - doesn't work :(
Posted: Fri Sep 03, 2010 9:06 am
by simonmlewis
This is it at the moment. It is now pulling out the averages, but I have no control over it pulling out duplicates of products, nor the amount of products it withdraws. ie. I want to display just 8.
Apart from the connection to DB code - this is the full pizza!
Code: Select all
$rating = mysql_query ("SELECT prodid, AVG(stars) FROM rating GROUP BY prodid") or die (mysql_error());
while ($rat = mysql_fetch_array($rating))
{
$starrating = number_format($rat['AVG(stars)']);
if ($starrating == "5") { $prodid = $rat['prodid'];}
{
$product = mysql_query ("SELECT * FROM products WHERE id = '$prodid' ORDER BY RAND() LIMIT 0, 8") or die (mysql_error());
while ($prod = mysql_fetch_object($product))
{
echo "
<div class='cat_prodlistbox' style='height: 180px'>
<div class='cat_producttitle'>$prod->title<br/>Only ";
printf ("£%.2f", $prod->price);
echo "</div>
<a href='index.php?page=product&product=$prod->id&s=$prod->subid&c=$prod->catid&cname=$prod->catname&sname=$prod->subname&menu=sub&head=$prod->title' title='Look at the $prod->title'>";
if ($prod->photoprimary == NULL) { echo "<img src='images/blank.gif' border='0' />";}
if ($prod->photoprimary <> NULL) { echo "<img src='images/productphotos/small/$prod->photoprimary' border='0' />";}
echo "<img src='images/stars5.png' border='0' width='105px' /></a></div>";
} mysql_free_result($product);
}
} mysql_free_result($rating);
Re: Get Average and show only 5 Stars - doesn't work :(
Posted: Fri Sep 03, 2010 9:29 am
by mikosiko
personally I will implement this slightly different:
1) I will create a Database View like:
Code: Select all
CREATE VIEW v_avgrating AS (SELECT prodid, AVG(stars) AS avgstars FROM rating GROUP BY prodid);
2) Change your products select to this:
Code: Select all
$thesql = "SELECT a.*, b.avgstars
FROM products a , v_avgrating b
WHERE a.id = b.prodid
AND b.avgstars = 5
LIMIT 8";
$product = mysql_query ($thesql) or die (mysql_error());
while ($prod = mysql_fetch_object($product))
{
// here the rest of your code
}
Re: Get Average and show only 5 Stars - doesn't work :(
Posted: Fri Sep 03, 2010 9:32 am
by simonmlewis
Sadly I cannot change the DB now as it is well and truly in use by consumers.
But can I use that final query against my DB table as-is?
Can you run a query that says something like:
select prodid, stars from ratings where the average stars = '5', group by prodid
?
This would then give me a selection of all the product ids that do have that '5' average, and then i can query the Products table for the info on the item.
Re: Get Average and show only 5 Stars - doesn't work :(
Posted: Fri Sep 03, 2010 9:50 am
by mikosiko
a VIEW doesn't change anything that your user are using now... with that you are not changing any existent table.. it is just another object in your database and you can create as many as you want... that is the right solution IMHO.
why you don't try?
now... if you don't really want at least try and have another bullet for your gun

... try this... only one select instead of two... so replace accordingly.
Code: Select all
SELECT a.prodid, b.*
FROM ratings a JOIN products b ON a.prodid = b.id
GROUP BY a.prodid HAVING AVG(a.stars) = 5 LIMIT 8;
Re: Get Average and show only 5 Stars - doesn't work :(
Posted: Fri Sep 03, 2010 4:51 pm
by simonmlewis
I can see this isn't going to work with what I am rendering.
I need to select nearly all the fields in the products table for my result to work. How do I do that with your query?
Also - what is "HAVING" ?
Code: Select all
$product = mysql_query ("SELECT a.prodid, b.* FROM ratings a JOIN products b ON a.prodid = b.id GROUP BY a.prodid HAVING AVG(a.stars) = 5 LIMIT 8;") or die (mysql_error());
while ($prod = mysql_fetch_object($product))
{
echo "
<div class='cat_prodlistbox'>
<div class='cat_producttitle'>";
$position=43; //Defines how many characters will be displayed from content field.
$postcontent = substr($prod->title,0,$position);
echo "$postcontent ...</div>Only ";
printf ("£%.2f", $prod->price);
echo "<br/>";
if ($prod->photoprimary == NULL) { echo "<a href='index.php?page=product&product=$prod->id&s=$prod->subid&c=$prod->catid&cname=$prod->catname&sname=$prod->subname&menu=sub&head=$prod->title'><img src='images/blank.gif' border='0' /></a>";}
elseif ($prod->photoprimary != NULL) { echo"
<a href='index.php?page=product&product=$prod->id&s=$prod->subid&c=$prod->catid&cname=$prod->catname&sname=$prod->subname&menu=sub&head=$prod->title' title='Look at the $prod->title'><img src='images/productphotos/small/$prod->photoprimary' border='0' /></a>";}
if ($starrating == "5") { echo "<img src='images/stars5.png' width='105px' />";}
echo "</div>";
} mysql_free_result($product);
Re: Get Average and show only 5 Stars - doesn't work :(
Posted: Fri Sep 03, 2010 7:35 pm
by mikosiko
simonmlewis wrote:I can see this isn't going to work with what I am rendering.
I need to select nearly all the fields in the products table for my result to work. How do I do that with your query?
Also - what is "HAVING" ?
you can see it is not working?... how is that?... did you at least test the select that I gave to to?
seems to me that you don't understand the query... which is fine all of us learned one day
b.* is selecting ALL the fields of your table products.... so... what is the problem?
for HAVING.... well... maybe you need to read a little
http://dev.mysql.com/doc/refman/5.1/en/select.html
Re: Get Average and show only 5 Stars - doesn't work :(
Posted: Sat Sep 04, 2010 3:21 am
by simonmlewis
Well I'll be damned.
Apart from my giving you the wrong table name (rating, not ratings), it works!!
Where in there do I add "ORDER BY RAND" as at it is always coming up with the same 8 responses.
I have to admit that this is one of those answers on here, where I don't understand how it works - but would like to.