Get most active users by the amount of comments

Question

I want to display most active users by the amount of comments they have left on my site. I can't figure out what would the mysql query look like for this. There is no table that holds the total amount of comments for each user. I need to count the total amount of comments for each user somehow and display for example top 10 users with most comments.

Can anyone please help?

Answer 1

I need to count the total amount of comments for each user somehow and display for example top 10 users with most comments.

Your query for 10 highest commentators should be:

SELECT COUNT(comment_author) AS comment_comments, comment_author
FROM table_name GROUP BY comment_author ORDER BY comment_comments DESC LIMIT 10

Answer 2

Your request to get the number of comments should look like that :

SELECT identity_commentator, COUNT(comments) as num FROM table_comments GROUB BY identity_commentator ORDER BY num DESC LIMIT 20;

Where comments is the column stocking the comments, table_comments the table where you stock them and identity_commentator the column identifying the user.

Then you change the "LIMIT" at the end of your request, according to the number of commentators you want to get. Then, your mysql_fetch_array will have an array with 2 columns, (identity_commentators and num), and that's simple PHP from this point.

edit : Dammit, I was too slow !