I have a website that was launched a few months ago. I would like to write some code that will look through every month, since launch, and grab metrics for that month.
My question is: What is the best way to specify a start date, and then iterate by month, all the way up until the current month (or to another specified date).
Does anyone have any suggestions? I'm using PHP and a mySQL database.
分享 I don't know the schema of your stored metrics, so here's a generic example:
Select total pageviews per month from Aug 1 thru Nov 30 2009
The code for MySQL:
SELECT DATE_FORMAT(s.date, '%Y-%m') AS year_month , SUM( s.pageviews ) AS s.pageviews_total
FROM statistics s
WHERE s.date BETWEEN '2009-08-01' AND '2009-11-30'
GROUP BY DATE_FORMAT(s.date, '%Y-%m')
ORDER BY DATE_FORMAT(s.date, '%Y-%m')
Having that aggregated table output may be enough for you. If not, you can loop through it with PHP and perform other manipulations.
分享
