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..

Have you tried anything yourself? If not, you can [hire a paid freelancer to do it for you](http://www.freelancer.com). — h2ooooooo, May 26 '14 at 10:47
Posted a code snippet that I'm trying to use to get what I want done... — Jon Davis, May 26 '14 at 11:05
It seems from your question that you're looking for a way to do it all in the SQL statement, rather than loop and sum in PHP code. Is that correct? And welcome to SO! — davidethell, May 26 '14 at 11:10
@JonDavis: I don't think you would be able to do this via a single query. You can check my answer. It is a sample algorithm you could use. — Kanishk Dudeja, May 26 '14 at 11:11
@JonDavis: Oh u mean you've already implemented it and want an only SQL solution? Or you will try to implement it now? — Kanishk Dudeja, May 26 '14 at 11:16
This will be very difficult in sql, generating a series of dates within 2 date range itself is a complicated issue. http://stackoverflow.com/questions/2157282/generate-days-from-date-range — Abhik Chakraborty, May 26 '14 at 11:19
Coming to this a bit late... Where do you get 02/10/14 from? — Strawberry, May 26 '14 at 11:36
@KanishkDudeja: I have something similar and would like to see if there's an SQL only solution... — Jon Davis, May 26 '14 at 15:08
@Everyone: Thanks for your help and your input! I really appreciate it. The pure SQL version works better for me in this instance though... — Jon Davis, May 26 '14 at 17:18

score 1 · Accepted Answer · answered May 26 '14 at 11:45

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

fyi, to generate date ranges, i use a table which has one column 'id' with 500 rows, starting at 1 and incrementing. I use that to generate any date range needed. it serves the same purpose — Ryan Vincent, May 26 '14 at 11:49
Uisng the above query you can do it since I am taking the min() transaction date for the user till today or instead of till today it could be set to max() transaction date for the user and week range will be created with the data as shown in the demo. — Abhik Chakraborty, May 26 '14 at 11:50
was more about 'swings' and 'roundabouts', i.e. an alternative to the 'cross join', rather than being about your specific answer. — Ryan Vincent, May 26 '14 at 11:53
Ok in case you want to generate the weeks between min and max date of commission for an user you can use this http://sqlfiddle.com/#!2/6c13b/10 — Abhik Chakraborty, May 26 '14 at 15:18
@AbhikChakraborty: Would this still work if date was a DateTime? — Jon Davis, May 26 '14 at 15:57
Yes should work with some modification like conveying the datetime to date in the query http://sqlfiddle.com/#!2/652e6/7, it looks messy but its pretty simple. — Abhik Chakraborty, May 26 '14 at 16:26
You forgot to Date(date) some dates on t2 but it works perfectly! I hope it doesn't slow down when the records are more than a few... — Jon Davis, May 26 '14 at 17:12
Good it worked ! Well for few 100 thousands of record it wount be harmful but for large data indexing will play a good role in optimization. — Abhik Chakraborty, May 26 '14 at 17:25

Kanishk Dudeja · Answer 2 · 2014-05-26T11:06:23.893

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

Saad Bashir · Answer 3 · 2014-05-26T11:53:58.970

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

}

It is going to be hard to do with one query. I would recommend breaking it up. Just for your information that date_sub substracts days. In your case you want to add days so that will be date_add — Saad Bashir, May 26 '14 at 11:21
@JonDavis The above code should work like a charm. If you need any help with this chunk of code let me know. Don't forget to change the name of the table and columns — Saad Bashir, May 26 '14 at 11:55
Thanks for your help! I really appreciate it. The pure SQL version works better for me though... — Jon Davis, May 26 '14 at 17:17

score 0 · Answer 4 · answered May 26 '14 at 11:23

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.

MySQL + PHP: Display items per week?

4 Answers4