Need a little help
 

I'm trying to code a little hack to display the top 10 posters for the month. I'm a little stuck on the SQL code. Actually, the dateline is what is the problem

This is the sql statement

SELECT count( * )
as postcount, username, userid
FROM post
where dateline
between startdate
and enddate
GROUP BY userid
ORDER BY postcount DESC
LIMIT 0, 10

It returns no record. I know it's because the date is stored in that millisecond format but I am having problems in finding out how to select it. Can anyone give me a hand?

[sql]
SELECT COUNT(p.postid) AS postcount, u.username, p.userid FROM post p, user u
WHERE p.dateline > [last month]
AND u.userid = p.userid
GROUP BY u.userid ORDER BY postcount DESC LIMIT 10
[/sql]
...theoretically.

I don't see why I have to go into the user table as post has the username and userid fields.

Also from what I've learned, count(*) should execute faster than a count on a field.

Lastly that
WHERE dateline > [last month]
I understand your logic on this but how do I format [last month] so it will select something. The format for vb is in milliseconds that is hard to understand by looking at it.
I would like to do something like
WHERE dateline > 2003-04 AND dateline <= 2003-05
so I can refer to a specific month in the future.

It's the formatting of the date for the WHERE clause that I'm stuck on.

The username is cached in the post table. In the user table, it's perpetually up to date.

Look up http://us3.php.net/mktime

Thanks...will do some more reading with the link you posted :)

Ok, I think I got it. This is what I did and it seems to return the proper values.

SELECT count( * ) as posts, username, userid
FROM post
where date_format( from_unixtime( dateline ) , '%Y-%m' ) = '2003-04'
GROUP BY userid
ORDER BY posts DESC LIMIT 10