CODEWITHSUNDEEP
phpmysqlwordpress
Tom Green
Tom Green

Reputation: 305

(Wordpress/WPDB/WP Customer Reviews) Counting number of 1*, 2*, 3*, 4*, and 5* reviews?

I successfully added an "average rating" to my site from a snippet I found, but my PHP isn't great and I'd ideally like to show how many 1*, 2*, 3*, 4*, and 5* reviews each have been made, if that makes any sense.

So I could show it like this on my site:

Anyone with decent PHP knowledge will probably be able to figure this out from the code I already used for the average rating, which is below:

<?php global $wpdb;
    $pId = $post->ID; //if using in another page, use the ID of the post/page you want to show ratings for.
    $row = $wpdb->get_results("SELECT COUNT(*) AS `total`,AVG(review_rating) AS `aggregate_rating`,MAX(review_rating) AS `max_rating` FROM wp_wpcreviews WHERE `page_id`= $pId AND `status`=1");
    $max_rating = $row[0]->max_rating;
    $aggregate_rating = $row[0]->aggregate_rating; 
    $total_reviews = $row[0]->total;
    $totl = $aggregate_rating * 20;
    $wpdb->flush(); 
?>

<div id="overall-rating">
    <div class="sp_rating" id="wpcr_respond_1">
        <div class="base">
            <div style="width: <?php echo $totl;?>%" class="average"></div>
        </div>
        <br />
        <p>From <?php echo ' ' . $total_reviews;?> reviews</p>
    </div>
</div>

As you can see the ratings from 1 to 5 are stored in a column called "review_rating" inside the "wp_wpcreviews" table, so is there a way for me to count each rating respectively?

The values in the "review_rating" table are just stored as "1", "2", "3", "4", or "5".

Thanks in advance for any responses, been stuck on this for a while!

Upvotes: 1

Views: 638

Answers (1)

The Humble Rat
The Humble Rat

Reputation: 4696

From the looks of it you should be able to tweak the mysql slightly to get what you need. Try the following:

global $wpdb;
$pId = $post->ID; //if using in another page, use the ID of the post/page you want to show ratings for.
$row = $wpdb->get_results("SELECT COUNT(*) AS `total`,review_rating FROM wp_wpcreviews WHERE `page_id`= $pId AND `status`=1 GROUP BY review_rating");

foreach($row as $r)
{
    echo $r->review_rating . " Star Ratings: (" . $r->total . ")<br/>";
}

I'm pretty certain this will either work or get you 90% of the way there. I am assuming that you only rate thing in whole numbers, so no 4.5 ratings etc, I also presume it saves the rating in the db as 5 for 5, 4 for 4 etc, which I am sure it would.

If this does not work, maybe a few rows from the table for us to look at may help.

Another thing to consider is to uses parameters, for example:

$results = $wpdb->get_results(
                  $wpdb->prepare(
                                "SELECT COUNT(*) AS `total`,review_rating FROM wp_wpcreviews WHERE `page_id`= %d AND `status`=1 GROUP BY review_rating",$pId

                              )
                            );

The difference being the prepare method and %d in your query as opposed to using a variable. I may be right or I may be wrong but using this will stop any sql injection issues etc.

Update

The issue with the Mysql is that it will only get ratings that exist, so if you have say something with one one star rating only, the above will only print info about the one star rating. If you wish to show all stars regardless of whether a review exists or not something like below should do this for you (just replace the foreach in the above example):

foreach($row as $r)
{
    $stars[$r->review_rating] = $r->total;
}

$a = 1;
while($a < 6)
{
    echo $a . " Star Ratings: (" . (!empty($stars[$a]) ? $stars[$a] : 0) . ")<br/>";
    $a++;
}

You can then say have images ie 1-star.png, 2-star.png and change this line:

echo "<img src='http://www.example.com/$a-star.png'> : (" . (!empty($stars[$a]) ? $stars[$a] : 0) . ")<br/>";

Upvotes: 1

Related Questions