MySQL + PHP：每周显示项目？

Question

This has me a stummped...

If I have a this MySQL table:

UserId | Commission | Date Of Commission
   1   |  200.00    |   2014-02-12
   1   |  50.00     |   2014-04-01
   2   |  10.00     |   2014-04-05

and I would like to display the Total Commission for a specific user per week starting from his/her first record, and display 0 for that range if there's no record.

how would I go about it?

Sample Output

UserId |     Date Range      | Total Commission
   1   | 02/10/14 - 02/16/14 |     200.00
   1   | 02/17/14 - 02/23/14 |      0.00
  ...
   1   | 03/31/14 - 04/06/14 |     50.00

I'm not a seasoned coder so any help will be much appreciated.

Thanks!

Edit:

I have tried this:

SELECT IFNULL(SUM(Commisssion),0) Total ,DATE_SUB(`DateOfCommission`,INTERVAL 7 DAY) 
  AS RangStart,DATE_SUB(`DateOfCommission`,INTERVAL 1 DAY) AS RangeEnd 
FROM `comms` WHERE `UserId` = '$UserID' GROUP BY DATE(`DateOfCommission`) DESC

but it starts the week with whatever date the first record was entered..

Answer 1

This is very tricky to accomplish. Here is what I managed to do with small modifications it should work they way it needs to be. I have done it for userid = 1 and this could be done for other users as well.

In the query I have 2 lines

 where a.Date BETWEEN (select min(date) from transactions where UserId = 1) AND NOW()

and

  WHERE date BETWEEN (select min(date) from transactions where UserId = 1) AND NOW()

The query will try to generate the list of dates using the min() date of transaction for the user till today. Instead of now() this could be used as max() date of transaction for the user as well.

select 
t1.date_range,
coalesce(SUM(t1.Commission+t2.Commission), 0) AS Commission
from
(
  select 
  a.Date as date,
  concat(
    DATE_ADD(a.Date, INTERVAL(1-DAYOFWEEK(a.Date)) +1 DAY),
    ' - ',
    DATE_ADD(a.Date, INTERVAL(7- DAYOFWEEK(a.Date)) +1 DAY)
  ) as date_range,
  '0' as  Commission
  from (
    select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as date
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
  ) a
  where a.Date BETWEEN (select min(date) from transactions where UserId = 1) AND NOW()
)t1
left join
(
  SELECT date ,
  coalesce(SUM(Commission), 0) AS Commission
  FROM transactions
  WHERE date BETWEEN (select min(date) from transactions where UserId = 1) AND NOW()
  AND UserId = 1
  GROUP BY date
)t2
on t2.date = t1.date
group by t1.date_range
order by t1.date_range asc

DEMO