Counting Reviews?

[Tim_L]

Hi guys,

I've got a PRODUCTS table and a REVIEWS table.

The REVIEWS table contains a unique ID and a PID which relates to the appropriate item in the PRODUCTS table.

How would I be able to count the number of reviews there are on a certain product & then echo the result?

This would display on the 'Product Detail' page.

Thanks 🙂

[LordShryku]

LEFT JOIN

[fean0r]

#Take the ID from the post so you can use it to check through the reviews
$postID = $myrow['ID'];
#start count
$query_items = "SELECT * FROM reviews ORDER BY ID";
$items = mysql_query($query_items) or die(mysql_error());
$row_items = mysql_fetch_assoc($items);
$count = "0";
do {
if ($row_items['reviewID'] == $postID) { $count = $count+1; }
}
while ($row_items = mysql_fetch_assoc($items1));
echo $count;

[Tim_L]

Well that kinda worked - it counted the TOTAL number of reviews in the WHOLE reviews table.

Was I right to remove the "1" next to the "$items1" on the second to last line??

[fean0r]

yes you were (my mistake)

$postID = $myrow['ID']; < just make sure thats setting the correct ID

Now in my code I've assume you've already in the middle of a query (looping) with the product table and now your start a query with the review table to see how many times the product ID appears in the review table....

perhaps add some echos

if ($row_items['reviewID'] == $postID) { $count = $count+1; echo $postID; echo $row_items['reviewID']; }

This will help make sure its all working right

[Tim_L]

Sorry - I'm not that good at this:

This is my REVIEWS recordset:

$query_rs_reviews = "SELECT * FROM reviews WHERE PID=$product_id ORDER BY reviews.ID DESC";
$rs_reviews = mysql_query($query_rs_reviews, $foo) or die(mysql_error());
$row_rs_reviews = mysql_fetch_assoc($rs_reviews);
$totalRows_rs_reviews = mysql_num_rows($rs_reviews);

//My var names are:

$row_rs_reviews['ID'] //Review ID

Are there any variable names I need to change? 😕

[LordShryku]

I was thinking this would do all the work for you...

SELECT    p.product_id, count(r.id)
FROM      products p
LEFT JOIN reviews r
ON        p.pid=r.pid

[Tim_L]

What do I need to print to show the number of reviews then?

Also, I think you need to have a 'GROUP BY' for that to work?

[LordShryku]

Yeah, probably need a group by

$sql = "SELECT    p.product_id, count(r.id) AS cnt
        FROM      products p
        LEFT JOIN reviews r
        ON        p.pid=r.pid
        GROUP BY  p.product_id";

$result = mysql_query($sql);
if(mysql_num_rows($result)>0) {
   while($row=mysql_fetch_array($result)) {
      echo "Product: ".$row['product_id']
           ."<br>Count: ".$row['cnt']."<br><br>";
   }
}
else {
   echo "No results returned";
}

Give that a try

[Tim_L]

OK - that's counting them correctly except that it's displaying every single product in the DB with a count # below. How can I just display the count # of one specific record?

[LordShryku]

Add a
WHERE product_id = 'theProductyouWant'
in the SQL

[Tim_L]

Thanks again for your help LordShryku - It's working!!! Now I'm going to try and display the average rating next to the product. Expect me back again 😃

[LordShryku]

No problem 😃

For the average, just add an avg(r.rating) on the query 🙂

[Tim_L]

I've realised now that I could've just echoed:

$totalRows_rs_reviews 

to get the total number of reviews.

if I want to find the average of all reviews of a specific product what do I do?

[LordShryku]

Same concept really.

SELECT    p.product_id, avg(r.rating) AS rating 
FROM      products p 
LEFT JOIN reviews r 
ON        p.pid=r.pid 
WHERE     p.pid = 'theProductYouwant'
GROUP BY  p.product_id