Counting number of events per week in PHP

Question

I have a table which contains members signups and I would like to be able to start doing some charting and analysis on the data and look for signup trends etc so I would like to be able to plot a line graph which shows me how many people signed up each week for the last 52 weeks.

I have got graphs working OK as I am using them extensively already but am struggling to get my head around how to extract the data from MySQL using PHP which is annoying me as I know it shouldn't be difficult - the table has a field called datestamp with a format of Y-m-d. How best can I create a loop which prints the date and a count for sign ups that week and each week for 52 previous weeks?

Dissuasion

Answer 1

Assuming your timestamp column is called signup_date, you could do a query like:

SELECT
    WEEKOFYEAR(signup_date) AS weekno, 
    COUNT(1) AS signups 
FROM your_table 
GROUP BY WEEKOFYEAR(signup_date);

This would give you a result set of 2 columns, the week of the year, and how many signups there were in that week.

Edit 1:

To get the date of the week for each of these results, you could add the following to the query:

SELECT
    WEEKOFYEAR(signup_date) AS weekno, 
    COUNT(1) AS signups,
    SUBDATE(signup_date, INTERVAL WEEKDAY(signup_date) DAY) AS date_of_week
FROM your_table 
GROUP BY WEEKOFYEAR(signup_date);

Ailanthus

That works excellent but for one little thing - I would like to be able to look 52 weeks prior to today rather than looking at the 52 weeks of the year. Using this method this week is shown (correctly) as week 16 but, when plotting, I would like to have this week shown as week 1 (or 52) and then last week week 2 (or 51) and so on so it changes each week as time moves on. Also is is possible to show the actual date of the week e.g. 01/01/2012 4, 08/01/2012 14 and so on? – Dissuasion

See Edit 1 for the last part of your comment. – Ailanthus