postwhore of the year :D

[dutchbb]

Does anyone know how to run a query to find out who has made the most posts in last year (2006). I would like to announce the winners on our board

[Jan Klerks]

Sounds like a great banning tool!

[Quillz]

I think you could simply list all of your members in the ACP, and then sort by post count.

[dutchbb]

How would that calculate most posts made in the past year?

[Chousho]

Someone with a little more knowledge could expand this, but you could run a query that selects the user by dates equal or greater to a year ago's date, then ascend it according to post.

Since I don't have my VB db open, I can't tell you exactly what table or even date format VB uses.

[mlx]

Try

SELECT COUNT( postid ) AS count, username
FROM vb3_post
WHERE dateline > 1136070000 AND dateline < 1167519600
GROUP BY username
ORDER BY count DESC
LIMIT 3

[dutchbb]

Try

SELECT COUNT( postid ) AS count, username
FROM vb3_post
WHERE dateline > 1136070000 AND dateline < 1167519600
GROUP BY username
ORDER BY count DESC
LIMIT 3

Thanks

How do you calculate the dateline from a date please?

-dutch

[MGM]

This might help: http://www.phpbuilder.net/columns/akent20000610.php3

MGM out

[slappy]

To convert the "dateline" from the "date" you need a "Unix Time Code Converter."

There are many of them available on the web. You will find one such at:

http://www.onlineconversion.com/unix_time.htm

It will convert a Unix time stamp, such as: 1136070000 to its calendar equilivant of "at, 31 Dec 2005 23:00:00 UTC"

and back the other way, with a place to enter any date you want and it will give you the Unix Time Code equilivant for your query.

Try "converting date to Unix timestamp" (without the quote marks) in your favorite search engine and you will find several similar tools available online.

Regards,

[dutchbb]

Thanks slappy

[Chousho]

If you want the date through a php file on your own server, try:

date("M d, Y", $date)

where $date is the Unix stamp.