top users -> comments -> php

I have a users table and a comments table and i want select from users the top users that have the big amount of comments from the comments table and order them by numbers of comments

table structure

users

id | username | password

comments

id | text | aut开发者_如何转开发hor_username

Use the following MySQL statement to list the users with the most comments. CommentCount tells you the number of comments made by a particular user.

SELECT
  users.username,
  COUNT(comments.id) AS CommentCount
FROM
  users
  INNER JOIN comments ON users.id = comments.author_userid
GROUP BY
  users.username
ORDER BY
  COUNT(comments.id) DESC

Please note that you will have to change author_userid into author_username first!

My SQL is a bit rusty, but something like this should give you what you're looking for (although as I mentioned, the user ID should be the only user identifier in the comments table.)

Select count(id), author_username from comments group by author_username

    select u.username,count(c.comments) as total
    from users as u
    left join comments as c
    on u.username = c.author_username
    group by u.username
    order by total desc

I would change join field as dutchie432 suggested. Add a limit clause in order to have your desired number of records.