SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    SitePoint Wizard billy_111's Avatar
    Join Date
    Jul 2009
    Posts
    1,683
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Find the most posts by users

    Hey,

    I was wondering if someone can help me with pulling out the 5 most active users of a forum. I have a table called "tbl_posts" where i have "member_id". So will it be a COUNT? I dont know the sql for this..

    Also i will have to LIMIT by 5 as i only want the top 5. how can i do this?

    Regards

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,398
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    First, this is a MySQL question. Next time post a question like this in the MySQL forum, and you'll have more possibilities of a fast and correct answer

    Second, you were almost there. You need COUNT, LIMIT 5 and GROUP BY and ORDER BY DESC:
    Code MySQL:
    SELECT 
        member_id
      , COUNT (*) AS posts
    FROM tbl_posts
    GROUP BY member_id
    ORDER BY member_id DESC
    LIMIT 5

  3. #3
    SitePoint Wizard
    Join Date
    Nov 2005
    Posts
    1,191
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    And probably some joins ... easier if you post the query you are having trouble with

  4. #4
    SitePoint Wizard billy_111's Avatar
    Join Date
    Jul 2009
    Posts
    1,683
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey,

    Thanks guys.

    I have the following query:-

    PHP Code:
            <?
            $sql_active 
    "SELECT member_id, username COUNT (*) AS posts FROM tbl_posts GROUP BY member_id ORDER BY member_id DESC LIMIT 5";     
                        
            
    $result_active mysql_query($sql_active) or mysql_error();
            
    $numrows_active mysql_num_rows($result_active);
            
            while(
    $row_active mysql_fetch_assoc($result_active))
            {
            echo 
    $row_active['username']."<BR>";
            }
            
    ?>
    I want to get the username out, but i keep getting this error:-

    Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /domains/thecaskaleclub.com/http/forum/index.php on line 513

    Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /domains/thecaskaleclub.com/http/forum/index.php on line 517

  5. #5
    SitePoint Wizard
    Join Date
    Nov 2005
    Posts
    1,191
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    There is a comma missing.

  6. #6
    SitePoint Wizard billy_111's Avatar
    Join Date
    Jul 2009
    Posts
    1,683
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey,

    unfortunately this is not the problem, i still get the same error message. It is actually relating to the following 2 lines:-

    $numrows_active = mysql_num_rows($result_active);

    while($row_active = mysql_fetch_assoc($result_active))

  7. #7
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,398
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    No, it means there's an error in your query.
    Change this

    $result_active = mysql_query($sql_active) or mysql_error();

    into

    $result_active = mysql_query($sql_active) or die(mysql_error());

    and see what error message appears.

  8. #8
    SitePoint Wizard billy_111's Avatar
    Join Date
    Jul 2009
    Posts
    1,683
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Its a syntax error, this is the error message:-

    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(*) AS posts FROM tbl_posts GROUP BY member_id ORDER BY member_id DESC LIMIT 5' at line 1
    Any ideas what may be wrong?

  9. #9
    SitePoint Member
    Join Date
    Sep 2009
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    totally wrong,how to judge a user if active? you must have a number for one user to describe how many post the send.
    this number youcan set in your usertable,also you can count at that time.but for performance i suject you put a feid in your usertable.for example 'post_number' so the sql code is:
    "select * from `tbl_posts` where 1 order by post_number desc"

    <snip>
    Last edited by Dan Grossman; Dec 14, 2009 at 00:44. Reason: Solicitation removed

  10. #10
    SitePoint Member
    Join Date
    Sep 2009
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    limit 0,5

  11. #11
    SitePoint Wizard billy_111's Avatar
    Join Date
    Jul 2009
    Posts
    1,683
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The error was actually the space, my new working query is:-
    SELECT member_id, username, COUNT(*) AS posts FROM tbl_posts GROUP BY member_id ORDER BY member_id DESC LIMIT 0,5

  12. #12
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,398
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by billy_111 View Post
    The error was actually the space
    Exactly
    See how useful it is to display the mysql errors while you're developing?

  13. #13
    SitePoint Wizard billy_111's Avatar
    Join Date
    Jul 2009
    Posts
    1,683
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I know,

    Thanks guido2004 i will keep that in mind

    Thanks again

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by billy_111 View Post
    my new working query is:-
    ORDER BY member_id DESC LIMIT 0,5
    this returns the 5 users with the highest names
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,398
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    True!
    Why did you have to tell him?


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •