MySQL + PHP: Display items per week?

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

The easiest way I can think of doing this as follows

Step 1: Get the date of the first record

"SELECT dateofcommission FROM comissionstable WHERE id='userid' ORDER BY dateofcommission ASC LIMIT 1"

The above query will return the first date of commission only

Step 2: Create a loop which starts from the date you got in Step 1 and continue the loop till the date is greater than or equal to today's date. Increment this date using PHP date function.

date('Y-m-d', strtotime($dateofcommission. ' + 7 days'));

Step 3: In this loop you can get the commission with-in the starting date and ending date. Starting date will be the date before adding 7 days and ending date will be the one after you have added 7 days.

SELECT SUM(commission) FROM commissiontable WHERE dateofcommission>= startingdate AND dateofcomission < endingdate AND id='userid'

The above logic should work. If you end up having some issues with this logic feel free to post in comments. I would be happy to help

The following is another solution

function getStartAndEndDate($week, $year) {
      $time = strtotime("1 January $year", time());
      $day = date('w', $time);
      $time += ((7*$week)+1-$day)*24*3600;
      $return[0] = date('Y-n-j', $time);
      $time += 6*24*3600;
      $return[1] = date('Y-n-j', $time);
      return $return;
}


$query = mysqli_query($con, "SELECT userid, COALESCE( SUM( commission ) , 0 ) AS thecommission , YEARWEEK( doc ) AS TheWeek FROM commission GROUP BY userid, TheWeek ORDER BY userid, TheWeek");
while ($array = mysqli_fetch_array($query)) {
  $test = $array['TheWeek'];
  $store_array = getStartAndEndDate(substr($test,4,2), substr($test,0,4));
  echo $array['userid']."-".$array['thecommission']."-".$store_array[0]."/".$store_array[1]."<br>";

}

Answer 2

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

Answer 3

SELECT UserId, COALESCE(SUM(Commission),0), YEARWEEK(DateOfCommission) AS TheWeek
GROUP BY UserId, TheWeek
ORDER BY UserId, TheWeek;

This will not print the nice date range, but should get you started in a SQL-only direction where the sum is broken down by the week of the year. I think you could take it from this point to add the nicer formatting of the Year/Week column. YEARWEEK() should give you pretty fast results.

Answer 4

So, this is sort of an algorithm you could use:

$Result = select distinct userid from table(this will fetch all userids from table)
while(There are rows in $Result)
{
    $Userid = $Result['userid']
    $StartDateRes = mysql_query(select Date, WEEKOFYEAR(Date) as week from table where userid = Userid order by date asc limit 1)
    $StartDateRow = mysql_fetch_assoc($StartDateRes)
    $StartDate = $StartDateRes['Date']
    $StartWeekNumber = $StartDateRes['week']

    $EndDateRes = mysql_query(select Date, WEEKOFYEAR(Date) as week from table where userid = Userid order by date desc limit 1)
    $EndDateRow = mysql_fetch_assoc($EndDateRes)
    $EndDate = $EndDateRes['Date']
    $EndWeekNumber = $EndWeekRes['week']

    for($i=$StartWeekNumber; $i<=$EndWeekNumber; $i++)
    {
        $StartDateOfWeek = FuncToFindStartDateOfWeek($i)
        $EndDateOfWeek = FuncToFindEndDateOfWeek($i)

        $Result2 = mysql_query(select sum(commission) as sum from table where date between StartDateOfWeek and EndDateOfWeek group by userid)

        $Row2= mysql_fetch_assoc($Result2)

        $Sum = $Row2['sum']

        mysql_query("insert into OutputTable values($UserId, $StartDateOfWeek. '-'. $EndDateOfWeek ,$Sum");
    }
}