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'].