Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

I used the comment meta to add a simple rating system. The user can post a rate from the comment form from where I added 3 dropdown comment meta.

The rating is working well, reflecting the ratings together with the comment written by the user. My only problem now is this: how can I get the average of all the ratings posted? I need the average to be placed on the post content.

My rating system rates the following:

  • Price,
  • Packaging,
  • Quality.

I want an average for each rate:

  • Average Price Rate,
  • Average Packaging Rate, and
  • Average Quality Rate.

Thank you so much!

share|improve this question
    
And please: Don't forget to vote and later accept an answer as solution. –  kaiser May 9 '11 at 13:57
    
Did you able to get a solution? –  Sisir Dec 29 '11 at 20:52

4 Answers 4

Here's my version, based on both of the answers above. It runs on wp_set_comment_status changed to approve.

calc_avg_rating() counts comments having a field of rating (if a partuclar comment has no rating, it simply moves on), and when a new comment is approved, it updates the post meta value of avg_rating.

Then, for my template, I simply call get_product_rating, which looks at the post meta field of avg_rating, that way we're not calculating all of this every time the page is loaded.

add_action("wp_set_comment_status", "calc_average_rating");

    function calc_average_rating($comment_ID, $approved) {
        if ($approved = 'approve'){
            $commentdata=get_comment($comment_ID, ARRAY_A); 
            $parent_post=get_post($commentdata['comment_post_ID']);

            global $wpdb;
            $post_id = $parent_post->ID;
            $ratings = $wpdb->get_results("

                SELECT $wpdb->commentmeta.meta_value
                FROM $wpdb->commentmeta
                INNER JOIN $wpdb->comments on $wpdb->comments.comment_id=$wpdb->commentmeta.comment_id
                WHERE $wpdb->commentmeta.meta_key='rating' 
                AND $wpdb->comments.comment_post_id=$post_id 
                AND $wpdb->comments.comment_approved =1

                ");
            $counter = 0;
            $average_rating = 0;    
            if ($ratings) {
                foreach ($ratings as $rating) {
                    $average_rating = $average_rating + $rating->meta_value;
                    $counter++;
                } 
                //round the average to the nearast 1/2 point
                $rating = (round(($average_rating/$counter)*2,0)/2);  
            } else {
                //no ratings
                $rating = '';
            }
            update_post_meta($post_id, 'avg_rating', $rating);
        }
    }

    function get_product_rating() {
        $post_id = get_the_ID();
        $value = get_post_meta($post_id, 'avg_rating', true);
        return $value;
    }

Hope this helps someone!

share|improve this answer
    
Thanks alot! Somehow it stopped working, WP DEBUG throws an error in the backend saying " WordPress database error: [You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near >commentmeta.meta_value FROM $wpdb->commentmeta at line 1] SELECT $wpdb->commentmeta.meta_value FROM $wpdb->commentmeta INNER JOIN $wpdb->comments on $wpdb->comments.comment_id=$wpdb->commentmeta.comment_id WHERE $wpdb->commentmeta.meta_key="rating" AND $wpdb->comments.comment_post_id=$post_id AND $wpdb->comments.comment_approved =1 " –  okiedokey Dec 4 at 8:34

I've got something similar using a custom query to calculate the average on the fly - per Rabino's comment, it would be more efficient to store the result of this function as a meta value, but I'd want it triggered when a comment is approved, rather than when a comment is saved.

here's your function:

function average_rating() {
    global $wpdb;
    $post_id = get_the_ID();
    $ratings = $wpdb->get_results("

        SELECT $wpdb->commentmeta.meta_value
        FROM $wpdb->commentmeta
        INNER JOIN $wpdb->comments on $wpdb->comments.comment_id=$wpdb->commentmeta.comment_id
        WHERE $wpdb->commentmeta.meta_key='rating' 
        AND $wpdb->comments.comment_post_id=$post_id 
        AND $wpdb->comments.comment_approved =1

        ");
    $counter = 0;
    $average_rating = 0;    
    if ($ratings) {
        foreach ($ratings as $rating) {
            $average_rating = $average_rating + $rating->meta_value;
            $counter++;
        } 
        //round the average to the nearast 1/2 point
        return (round(($average_rating/$counter)*2,0)/2);  
    } else {
        //no ratings
        return 'no rating';
    }
}

In my context I have a 1-5 rating. no results of the query means no ratings provided.

Drop the following in the loop and you're good to go:

<?php echo average_rating(); ?>
share|improve this answer

If you need to show the averages in the content, you need to pre-calculate them (before showing the comments).

My approach would be having a custom meta in the post with the calculated averages and modify those metas every time a new comment (rating) is saved.

Something like

add_action("comment_post", "wpse16733_updateAVGs");

function wpse16733_updateAVGs($comment_ID, $approved){

    if ($approved){
        $commentdata=get_comment($comment_ID, ARRAY_A); 
        $parent_post=get_post($commentdata['comment_post_ID']);

        (... get your rating, get post meta, calc and save ...)

    }
}
share|improve this answer
    
Probably you should do the inverse on comment deletion, if that apply to your case. –  MZAweb May 9 '11 at 15:22
    
Probably faster than my solution. +1 :) –  kaiser May 9 '11 at 16:07
    
This should be accepted - its much more efficient doing the calculations only when comments are added/removed, rather than every time a particular page loads –  acSlater May 19 at 16:15

This is a solution which will display the average after the last comment. As a workaround you could simply do two have_comments() comments loops and calculate the average rating with the first loop and display the comments with the second loop.

/**
 * You need to place this function inside your comments callback function, so it get's
 * triggered with every displayed comment inside the have_comments() loop.
 */
function wpse16733_get_comment_meta_avrg()
{
$divider = (int) $GLOBALS['wp_query']->comment_count;

// initial static values - these get counted up everytime the function get's triggered
static $price = 0;
static $packaging = 0;
static $quality = 0;
static $current_comment = 0;

$current_comment = (int) $current_comment++;

// receive all comment meta data
$all_meta = get_comment_meta( get_comment_ID(), '' );

// Now get the ratings (it could also be `$avrg_xy = $all_meta->rating` if an object)
$price = (int) $price + (int) $all_meta['price'];
$packaging = (int) $packaging + (int) $all_meta['packaging'];
$quality = (int) $quality + (int) $all_meta['quality'];

// calculate within the last comment
if ( $current_comment == $divider ) 
{
    $average['price'] = $price / $divider;
    $average['packaging'] = $packaging / $divider;
    $average['quality'] = $quality / $divider;
}

// now do stuff with the $average array
foreach ( $average as $rating )
{
    echo 'This is the average rating: '.$rating;
}
}
share|improve this answer
    
Note that the function is not tested. If you try it and it fails, please write a detailed description in here. If you can solve it yourself, please edit my Q with the working code, so others searching for a solution will also profit from the A. –  kaiser May 9 '11 at 13:59

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.