see which user got the most points in a MONTH

[jwk811]

in the user table there is a row called user_score. points get added to their score doing various things on the site.

 

theres no way of telling how many points they got in a month right now. and i dont know what the easiest way of doing that would be. maybe a new table keeping track but then how should it be updated.

 

any ideas? thanks.

[Psycho]

You should have a separate table to record each instance of an activity that adds to their points with: userID, date, points, activity. You can then get a user's total points by doing a JOIN between the user table and the points table. You can then also get a users points by some arbitrary value (such as a date span) or by activity via the WHERE clause

 

example:

SELECT u.name, u.id,
       SUM(p.point) as totalPoints
FROM users u
JOIN points p ON u.id = p.user_id
WHERE u.id = {$userID}
  AND p.date > {$startDate}
  AND p.date < {$endDate}