0

i am trying to get and count the values of a meta key (rating) and then calculate the sum. but the following code get all of values in wp_commentmeta table but i want to get just for the current post. how should i solve that?

$results = $wpdb->get_results("SELECT meta_value FROM {$wpdb->prefix}commentmeta WHERE meta_key = 'rating' ");
foreach($results as $result){
    $rate = $result->meta_value;
    $sum +=$rate;
}
$res = $sum/count($results);
$res = number_format((float)$res,2,'.','');

and how to count the number of 5 star ratings (the special value) for the current post. the code:

$countstar = $wpdb->get_var("SELECT count(meta_value) FROM {$wpdb->prefix}commentmeta WHERE meta_key = 'rating' AND meta_value = '5'");

1 Answer 1

1

You should be able to do that by joining data from wp_comments table as this table contains information about which comments belong to which posts

$sql = $wpdb->prepare( "
    SELECT meta_value 
    FROM {$wpdb->prefix}commentmeta 
    INNER JOIN {$wpdb->prefix}comments ON {$wpdb->prefix}commentmeta.comment_id = {$wpdb->prefix}comments.comment_ID 
    WHERE comment_post_ID = %d AND meta_key = 'rating' ", get_the_ID() 
);
$results = $wpdb->get_results( $sql );

Note that MySQL can sum the values for you, you do not need to do that using PHP.

UPDATED:

The number of 5 star ratings for current post you can count with the below query

$sql = $wpdb->prepare( "
    SELECT count(meta_value) 
    FROM {$wpdb->prefix}commentmeta 
    INNER JOIN {$wpdb->prefix}comments ON {$wpdb->prefix}commentmeta.comment_id = {$wpdb->prefix}comments.comment_ID 
    WHERE comment_post_ID = %d AND meta_key = 'rating' AND meta_value = '5' ", get_the_ID() 
);
$wpdb->get_var( $sql );

although since you are iterating over the ratings in foreach loop anyway then inside the loop you can count 5 star ratings without running a second query like this

$rating5 = 0;
foreach($results as $result){
    if( $result->meta_value == '5' ) {
        $rating5++;
    }
    $rate = $result->meta_value;
    $sum +=$rate;
}
9
  • thanks my friend a lot. but i do not need the sum because i need to count($results) too. i want to get the average. did you get what i mean?
    – sh.dehnavi
    Dec 10, 2018 at 11:25
  • ohh sorry i missed that, i updated my code, now after the $results = ... line you can use your foreach loop from the first post. Dec 10, 2018 at 11:29
  • sure, no problem :) Dec 10, 2018 at 11:33
  • would you help me in this issue too? wordpress.stackexchange.com/questions/321397/…
    – sh.dehnavi
    Dec 10, 2018 at 11:38
  • oh my friend, how can do with this? it need just meta_value = '5' in this code and for every post id: $countstar = $wpdb->get_var("SELECT count(meta_value) FROM {$wpdb->prefix}commentmeta WHERE meta_key = 'rating' AND meta_value = '5'");
    – sh.dehnavi
    Dec 10, 2018 at 11:42

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