I have a rating system for our products but there is an issue:

by the code, that will SELECT all comments in commentmeta table that have rating as meta_key. even those who do not have a rate (the user did comment but did not leave a rating for the product).
so I need to SELECT just those comments that the rating as meta_key is not NULL.
my current code:

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

Thanks for helping me

I have a rating system for our products but there is an issue:

by the code, that will SELECT all comments in commentmeta table that have rating as meta_key. even those who do not have a rate (the user did comment but did not leave a rating for the product).
so I need to SELECT just those comments that the rating as meta_key is not NULL.
my current code:

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

Thanks for helping me

• sql
• select
• meta-value

Share Improve this question edited Aug 8, 2019 at 11:31 Sh.Dehnavi asked Aug 8, 2019 at 11:05 Sh.DehnaviSh.Dehnavi 10933 silver badges1818 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

I founded myself my searching:

$sql = $wpdb->prepare( "
    SELECT meta_value 
    FROM {$wpdb->prefix}commentmeta 
    INNER JOIN {$wpdb->prefix}comments ON {$wpdb->prefix}commentmetament_id = {$wpdb->prefix}commentsment_ID 
    WHERE comment_post_ID = %d AND meta_key = 'rating' AND meta_value IS NOT NULL AND meta_value <> '' ", get_the_ID() 
);
$results = $wpdb->get_results( $sql );