有帖子mysql的热门用户

Question

I have 2 tables users | posts.

The posts structure is:

[post_id | post_user_id ]

The users structure is:

[user_id | user_name| user_password ]

Now i want to get the top 5 user with posts

edit 1:

       $cod_sql = "select u.user_name, count(*) as 'TotalNumberofPosts'
from users u
  join posts p
    on u.user_id=p.post_user_id
group by user_name
order by count(*) desc 
limit 5";
        $top10 = mysql_query($cod_sql1) or die (error_sql(mysql_error(),__LINE__,__FILE__));

            echo '<div class="head_panel">Members</div> 
                    <div class="body_panel">
                <table border="1" width="100%">
                    <tr>
                    </tr>   
                    <tr class="tr3">
                Top5 : 
                    </tr>';

    if ( $top10 === FALSE ) 
    { 
        // An error has occured 
        print "There was an error running the query" . mysql_error(); 
    } else { 
        while ( $row = mysql_fetch_assoc($top10) ) 
        { 
            print "This is some data from a row<BR />"; 
            print "Name: ($row) <BR />"; 
        } 
    }  

          }
        break;

Answer 1

The following code will calculate the total number of posts for each user and display only the top 5 posters. SQL Fiddle demonstration.

select u.user_name, count(*) as 'TotalNumberofPosts'
from users u
  join posts p
    on u.user_id=p.post_user_id
group by user_name
order by count(*) desc 
limit 5