I want get top commented user with user role

global $wpdb;

$amount = 5;

$blog = 2;

$arg = “SELECT COUNT(comment_author_email) AS comments_count,comment_author, comment_author_email, comment_author, comment_author_url FROM {$wpdb->base_prefix}{$blog}_comments

WHERE comment_approved = 1

AND comment_type = ”

GROUP BY comment_author_email

ORDER BY comments_count DESC

LIMIT {$amount}”;

$results = $wpdb->get_results($arg);

foreach ($results as $result)

echo $result->comment_author.”
“;

This query will get top commented user

I want to sort top commented subscriber and top commented author Separate

i want add a condition in this query like

WHERE user_role = author

  • Vaughan
    • Support/SLS MockingJay

    hiya

    thanks for posting.

    you need to use an SQL join or something in the query to get the user role from the wp_usermeta table for the comment authors user_id

    so something similar to this might work.

    SELECT COUNT(comment_author_email) AS comments_count,comment_author, comment_author_email, comment_author, comment_author_url, user_id FROM {$wpdb->base_prefix}{$blog}_comments AS comments, {$wpdb->base_prefix}{$blog}_usermeta AS usermeta
    WHERE comments.comment_approved = 1
    AND comments.comment_type = ''
    AND usermeta.user_id = comments.user_id
    AND usermeta.meta_key = $wpdb->base_prefix}{$blog}_capabilities
    AND usermeta.meta_value LIKE '%author%'

    that might not be quite correct, but it’s pretty much what you’d need to do.

    hope this helps

Thank NAME, for their help.

Let NAME know exactly why they deserved these points.

Gift a custom amount of points.