CODEWITHSUNDEEP
phpmysql
user2080921
user2080921

Reputation: 27

Handling monthly occurances with PHP

I have a website where people record (or log) the distance of their runs. I want to create a leader board that will automatically reset to zero after the month is over. If I could save their total distance for that month as well, that would be ideal. Every run is tied to IDs and I have a variable that adds up the monthly distance, but obviously when they log a new run that changes. I don't know how I would make it record this month only and not freak out if they log in advance.

Any help would be appreciated.

I have tried making a monthly distance value for MySQL so each time they log, if it is that month, it will add to it. But how should I make it reset?

Upvotes: 0

Views: 56

Answers (1)

grossvogel
grossvogel

Reputation: 6782

You're tracking the date of each run, right? Then you should be able to do something like this and avoid having to store the totals:

SELECT SUM(Runs.Miles) as MonthlyTotal
FROM Runs
where MONTH(Runs.Date) = MONTH(CURDATE()) and YEAR(Runs.Date) = YEAR(CURDATE())

(Presumably you'd also filter by or group by the UserID. Also, I suspect it may be more efficient to pre-calculate the beginning and end of the month and use BETWEEN in the where clause. Consult our friend EXPLAIN.)

If your read load is too high to make this practical, you could store the monthly total in a different table and update it with a trigger every time a run is added or updated, and via cron at the start of every month. This kind of de-normalization always makes things more complicated, so I avoid it until I have reason to believe it's necessary.

Upvotes: 2

Related Questions