Format query result

Question

I have a (work)shifts table like this:

shifts
s_id   e_id    start      end        date
  1      1   06:00:00   08:00:00   2017-05-04
  2      1   09:00:00   13:00:00   2017-05-02
  3      1   06:20:00   15:00:00   2017-05-03
  4      2   02:00:00   05:15:00   2017-05-02
  5      2   12:00:00   13:00:00   2017-05-05
  7      1   08:00:00   17:00:00   2017-05-01
  8      1   08:00:00   17:00:00   2017-05-05
  9      1   08:00:00   17:00:00   2017-05-06
 10      1   08:00:00   17:00:00   2017-05-07
 11      1   08:00:00   17:00:00   2017-04-30

An employee table

employees
id    name
1   Employee 1
2   Employee 2
3   Employee 3
5   Employee 4
6   Employee 5

With the query

select    employees.id as 'emp_id', 
          employees.name, 
          shifts.date, 
          shifts.start, 
          shifts.end 
from      employees 
left join shifts 
on        ((employees.id = shifts.e_id) 
and        (shifts.date between '2017-05-01' and '2017-05-02'))
order by  employees.id, shifts.date

My result looks like:

emp_id   name        date     start     end
   1   Employee 1 2017-05-01 08:00:00 17:00:00
   1   Employee 1 2017-05-02 09:00:00 13:00:00
   2   Employee 2 2017-05-02 02:00:00 05:15:00
   3   Employee 3    NULL      NULL    NULL
   5   Employee 4    NULL      NULL    NULL
   6   Employee 5    NULL      NULL    NULL

The desired final result (formatted with php) should look like this:

             2017-05-01        2017-05-02
Employee 1  08:00 - 17:00     09:00 - 13:00
Employee 2     NULL           02:00 - 05:15
Employee 3     NULL              NULL
Employee 5     NULL              NULL
Employee 6     NULL              NULL

I just can't think of a way to format it like this without querying every single day or adding a shift for every employee every day (= many empty entries as not every employee has to work every day). If it helps it is still possible to change the table setup.

Answer 1

You can store it in 2 dimensional array

$shifts[employeename][date]

Here is an example code for this.

foreach($rows as $row)
{
    $shifts[$row['name']][$row['date']] = $row['start'] . ' ' . $row['end'];
}