Get Average and show only 5 Stars - doesn't work :(

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Get Average and show only 5 Stars - doesn't work :(

Post 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?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Get Average and show only 5 Stars - doesn't work :(

Post by mikosiko »

seems that you have an unbalanced # of {}.... could be the one in this line if you posted your full code.

Code: Select all

} mysql_free_result($rating);
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Get Average and show only 5 Stars - doesn't work :(

Post 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);
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Get Average and show only 5 Stars - doesn't work :(

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

Re: Get Average and show only 5 Stars - doesn't work :(

Post 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.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Get Average and show only 5 Stars - doesn't work :(

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

Re: Get Average and show only 5 Stars - doesn't work :(

Post 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); 
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Get Average and show only 5 Stars - doesn't work :(

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

Re: Get Average and show only 5 Stars - doesn't work :(

Post 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.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
Post Reply