I have table with messages, and i have users table, the are linked via PK UserID -> UserID, what i need is to select Messages for current month and calculate top 5 posters list for current month.
I have create following query but it's not complete:
SELECT Handle, Screen_Name from MBoard where Message_Date>='2003-11-00' ORDER BY Handle;
this gets me all messages posted this month ordered by userid
Have you read about JOINs at all? This is what you need to like the two tables together.
So you're query would be something like this:
SELECT m.Handle, m.Screen_Name, COUNT(*) cnt FROM MBoard m, Users u WHERE m.UserID = u.UserID AND Message_Date >='2003-11-00' GROUP BY m.UserID ORDER BY cnt DESC LIMIT 5
If you wanted to do something like Top 5 Posters for the last 30 days:
SELECT m.Handle, m.Screen_Name, COUNT(*) cnt FROM MBoard m, Users u WHERE m.UserID = u.UserID AND Message_Date > NOW() - INTERVAL 30 DAY GROUP BY m.UserID ORDER BY cnt DESC LIMIT 5
Hope that helps...
-- ---John Holmes...
Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/
php|architect: The Magazine for PHP Professionals – www.phparch.com
-- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
