Post count within a date range

[Doc203]

On our board we give a prize away every month for the person with the most posts that month.

Right now I go into the database and manually extract the post counts and do the math. I would like to have a hack that would do that for me, and maybe even show under each users profile the number of posts they have so far each month. Then lastly I would like to have a seperate "stats" page that you could see who the top posters were every month for the past however many.

Thanks for any help!

[bigdaddy04]

bump, im interested in this as well..i have created a query for the overall post count but cannot figure out how to retrieve a post count for the current month

[bigdaddy04]

Anyone?

Here is what I have so far: (it returns top10 posters EVER, i want it for the current month)

PHP Code:

$records = $DB_site->query("SELECT username,posts FROM user ORDER BY posts DESC LIMIT 10"); 


thanks alot, if someone could just point me in the right direction, would be cool as well.

cheers

[Doc203]

We just need to modify that to a specific date range. I will see what I can find also.

[CarCdr]

You would need to query the 'post' table. search.php contains code to do a similar thing (i.e., when you specify a username and last 30 days). All posts are timestamped. You'll see in there how to manufacture a timestamp 30 days old.

If you do not know the user you are after, this would be very time-consuming, as you'd have to check each user against the post table. Yikes.

[Doc203]

Here is what I have come up with thus far:

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'))

[Doc203]

OK, with a little help I finally came up with this for a query, now on to the rest of it.

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]

Here is What I finall came up with so I could show all users and list them within a certain date range. Now I just need to figure out how to write a script that will let the user select the date range and how to make that work with this script or one similar to it.

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

[roundhost]

i am interested in this also, for vb3 though

[Doc203]

I am working on it for VB3. I have gotten the results that I want, I just have to get them to work correctly with a template etc.