Get data by week with sql and php

Question

I have a problem and I have no idea how to do that. Please help me with an idea. So I have a table : test, with columns :

id        begin                    end     
1       2016-06-06 15:30:30     2016-06-08 16:40:40

Now I need to get the count of id by week The result need to be : For example if this week is the week number 6 I need to get :

Week           count
 4               10
 5               10
 6               12
 7               19
 8               27

Thx four you help

Answer 1

Well, what happens if the begin is week 4, and end is week 5? which week is calculated?

Anyway, if I understood you, you want two previous weeks and 2 future weeks :

SELECT s.* FROM (
    SELECT YEAR(`start`) as year_col,WEEK(`start`) as week_col,count(*)
    FROM YourTable t
    GROUP BY year_col,week_col) s
WHERE s.week_col between WEEK(now())-2 and WEEK(now())+2

I've also added year into the consideration, since week 4 from 2015 will be group together with week 4 from 2016. If its not possible, exclude it from the query.