How to get data from mysql and group them in weeks, also separating by month

Question

I have mysql database and is full of over 2 years of data. How can I make a query in a way that it will get the result as below?:

January
   Week 1
      ...
      data rows here
      ....
   Week 2
      ...
      ...
   Week 3
      ...
      ...
   Week 4
      ...
      ...

February (same as above)

These are the field structures:

- date (in yyyy-mm-dd format)
- job (integer autoincrement)
- person (varchar)

Answer 1

Try this, it will generate output pretty similar to one required by you

SELECT MONTH(date) AS MONTH, WEEK(date) AS WEEK, DATE_FORMAT(date, %Y-%m-%d) AS DATE, job AS JOB, person AS PERSON GROUP BY WEEK(date);