Need a little help

[MuSuL]

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?

[filburt1]

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

...theoretically.

[MuSuL]

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.

[filburt1]

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

[MuSuL]

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

[MuSuL]

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