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?

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);