1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

sql help please

Discussion in 'XenForo Development Discussions' started by TerminalAddict, Feb 8, 2011.

  1. TerminalAddict

    TerminalAddict Active Member

    ok .. I would like to be able to execute a query that give me every user that has 20 post.

    here's some background that I've got

    select distinct(username) from xf_post;
    Gives me all users that have posted.

    select count(username) from xf_post where username='TerminalAddict';
    gives me a the number of posts I have made.

    post_date is an int presumably with a timestamp.

    I'd like a query that does:
    select username, post count, and userid for all users that have a posted more than 20 posts between date 1 and date 2

    usage: tell me who has made 20 posts in the last month.

  2. ragtek

    ragtek Guest

    all users with 20 posts:
  3. TerminalAddict

    TerminalAddict Active Member

    nice .. duh I was looking the wrong table for post counts :)
    Still, how about dates? between date1 and date2

    this doesn't work btw:
    select u.username,u.user_id from xf_user as u join xf_post as p where post_date > $date1 and post_date < $date2;
  4. ragtek

    ragtek Guest

    p.post_date ;)
  5. Marc

    Marc Well-Known Member

    LOL ... and I just looked in the same table.

    Just incase your interested, could get it the way you were after like this

    SELECT username,count(username)
    FROM `xf_post`
    WHERE FROM_UNIXTIME(`post_date`) BETWEEN '20110101' AND '20110131'
    GROUP BY username
    HAVING count(username) >19

    There is probably a better way using the unix date, however not used to using these as I work with sql server rather than mysql.
  6. TerminalAddict

    TerminalAddict Active Member

    perfect !! thanks

    SELECT username,count(username) as post_count FROM `xf_post` WHERE FROM_UNIXTIME(`post_date`) BETWEEN '20110101' AND '20110201' GROUP BY username HAVING count(username) >19 order by post_count desc;

    it's the HAVING bit that I have not used a trillion years that did it ;)
  7. Marc

    Marc Well-Known Member

    No problem ... Would be very interested if anyone can tell me the more efficient ways of doing this rather than using the functions as Im sure there must be one.

Share This Page