Publishers of technology books, eBooks, and videos for creative people

Home > Articles > Web Design & Development > PHP/MySQL/Scripting

  • Print
  • + Share This
From the author of Retrieving and Aggregating Reviews

Retrieving and Aggregating Reviews

The second aspect of the review system is to retrieve reviews and aggregate the ratings. You can easily find the average of the ratings using this SQL command:

SELECT AVG(rating) FROM reviews WHERE product_type='actual_product_type' AND product_id=X

That query returns a single row with a single value.

To fetch all the reviews, just use:

SELECT review FROM reviews WHERE product_type='actual_product_type'
 AND product_id=X ORDER BY date_entered DESC

That query retrieves the reviews by in reverse chronological order. If you’re using a “helpful review” system, you might want to retrieve the reviews in order of most to least helpful:

SELECT COUNT(helpful) AS h, review FROM reviews INNER JOIN review_ratings ON
 (reviews.id=review_ratings.review_id) WHERE helpful=1 GROUP BY (review_id) ORDER BY h DESC

That query also returns the count of how many people found the review to be helpful. If you’d rather see the reviews in order of percentage of “usefulness” (i.e., what percentage of people who rated the review found it to be helpful as opposed to those that did not), you’d use:

SELECT review FROM reviews INNER JOIN review_ratings ON
 (reviews.id=review_ratings.review_id) GROUP BY (review_id) ORDER BY AVG(helpful) DESC

To let the user change the order of the reviews, just create links back to the page that pass key values along in the URL:

<a href="thispage.php?review_order=newest#reviews">View Newest Reviews</a>
<a href="thispage.php?review_order=helpful#reviews">View Most Helpful Reviews</a>

The reviews.php script would then alter the query’s ORDER BY clause based upon the presence and value of $_GET['review_order'].

  • + Share This
  • 🔖 Save To Your Account