CODEWITHSUNDEEP
phpmysqlarrayssorting
crashintoty
crashintoty

Reputation: 363

How do I grab, sort and display top commenters (PHP/MySQL)?

Its been two days and I still can't figure this out: how do I retrieve all the users who commented on a given article, sort them in descending order by the number of times they commented and then display their username and number of times they commented (i.e., michael (17), laurie (14), jenny (10), dennis (6), etc.)?

Upvotes: 0

Views: 130

Answers (1)

Tim Lytle
Tim Lytle

Reputation: 17624

Without seeing the database structure, it's hard to say. But assuming it's something like this:

Article

  • Id
  • Content

Comments

  • Id
  • ArticleId
  • UserId
  • Comment

The query would look something like this:

SELECT UserId, COUNT(*) as CommentCount FROM Comments WHERE ArticleId = 1 GROUP BY UserId ORDER BY CommentCount DESC;

Then you'll just need to do a JOIN on the user table to get the user's name.

Upvotes: 1

Related Questions