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'");
0

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;
}
  • 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 '18 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. – Greg Winiarski Dec 10 '18 at 11:29
  • sure, no problem :) – Greg Winiarski Dec 10 '18 at 11:33
  • would you help me in this issue too? wordpress.stackexchange.com/questions/321397/… – sh.dehnavi Dec 10 '18 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 '18 at 11:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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