special queries

[tombot]

I learned in this thread how to find how many posts a user has in a specific forum.

Because of some contests I'm considering in the future, I would like to know how to do a few more.

1. Is there a way to do that one, but for multiple forums? Like say you wanted to know how many posts userid 6 has in in forumids 3, 7, an 9.

After looking at some SQL tutorials, I tried all kinds of variations such as...

SELECT count(*) AS count FROM post LEFT JOIN thread ON (thread.threadid = post.threadid) WHERE thread.forumid=3 OR thread.forumid=7 OR thread.forumid=9 AND post.userid=6

Tried them with AND and such, but never did get it to work.

2. Is there a way to do it for number of threads a user started in an individual forum?

3. Is there a way to make these in a specific date range?[/B] Like how many posts a user had in a specific forum from April 2, 2004 to May 4, 2004 for example.


Thanks in advance!

[Jake Bunce]

1) Use this query:

SELECT COUNT(*) AS count FROM post LEFT JOIN thread ON (thread.threadid = post.threadid) WHERE thread.forumid IN (X, Y, Z) AND post.userid=W

2) Use this query:

SELECT COUNT(*) AS count FROM thread WHERE forumid = X AND postuserid = Y

3) Use this query:

SELECT COUNT(*) AS count FROM post LEFT JOIN thread ON (thread.threadid = post.threadid) WHERE thread.forumid = X AND post.userid = Y AND (UNIX_TIMESTAMP('2004-04-02 00:00:00')) <= post.dateline AND post.dateline <= (UNIX_TIMESTAMP('2004-05-04 00:00:00'))

[tombot]

Thanks Jack! You rock and you are da man!

[Doc203]

SELECT COUNT(*) AS count FROM post LEFT JOIN thread ON (thread.threadid = post.threadid) WHERE thread.forumid = X AND post.userid = Y AND (UNIX_TIMESTAMP('2004-04-02 00:00:00')) <= post.dateline AND post.dateline <= (UNIX_TIMESTAMP('2004-05-04 00:00:00'))

This works beautifully and I am trying to add to it so that the end result is USERNAME, POSTCOUNT for all forums.
I used

SELECT COUNT(*) AS count FROM post WHERE post.userid = 1 AND (UNIX_TIMESTAMP('2004-04-02 00:00:00')) <= post.dateline AND post.dateline <= (UNIX_TIMESTAMP('2004-05-04 00:00:00'))

and can get the total post for a certain user in all forums, but I want to do it for all users and the result be USERNAME, POSTCOUNT. Any ideas? Thanks!

[Jake Bunce]

Something like this should work:

SELECT COUNT(post.postid) AS count, user.username
FROM post
LEFT JOIN user ON (user.userid = post.userid)
WHERE post.userid = 1
AND (UNIX_TIMESTAMP('2004-04-02 00:00:00')) <= post.dateline
AND post.dateline <= (UNIX_TIMESTAMP('2004-05-04 00:00:00'))
GROUP BY post.userid

[Doc203]

Jake, that worked great!!! Saved me a bunch of headaches, thanks!