So before I tackle this I'd to just throw it out there and see if maybe there is a better way of doing it than what I'm currently thinking of.

 

Currently building LunchWatch.co.uk. Idea is fairly simple, users come in and submit individual meals with a rating out of 10 and the council that supplied it (amongst other things). What I want to do is pull some stats out for these councils. Rank their aggregate score against all other councils, show improvements/drops month on month. That sort of thing.

 

So what I currently have is two tables. One for MEALS, one for COUNCILS.

 

Each time a meal is submitted it creates a new row in MEALS with all the relevant information and I currently have it also adding that meals score to a running total for that council in the COUNCILs table, as well as adding 1 to a 'submissions count'. Idea being I can do a bit of maths and figure out the average score for each council with that information and track popularity etc.

 

So what I want to do is use that average to rank each council against each other, then keep a history of that score every day/week/month/whatever.

 

My current thinking is to run a CRON that does this calculation, then throw the number into the database. That would make it easy to pull and sort to display a list of best -> worst or whatever but as far as my PHP knowledge goes that would mean overwriting every time the CRON runs.

 

How would I go about writing a script that calculates the average then stores it in the database somewhere slightly different from last time?

I don't think you would need to store that data elsewhere. Just have a date column with a timestamp for each meal and you can just query it from that date range, e.g. if you want the rankings for a certain month, just pull each result where MONTH(date) = *whatever and then run your calculations on those results.