How to select data in multiple table and view it in day-wise?

Question

I have developed an Employees Lunch Break-Time Tracker where the employee has to punch in/out before and after their lunch break time. I calculated the total break in minutes and saved it in the DB. I provided a report to the HR department of all the break time. The challenging part for me here was to provide a report where evening shift employees took a lunch break the next day should appear on the previous day. What I did was, I created an 8 character code for each day and that code will be saved for each employee.

What I need to provide is a summary report where it will show the employee and the rest of the columns are the days for that month.

I have two tables now.

Table CODE:

ID   | CODE     | DATE_CREATED |
1    | 9VIHQIHE | 2019-03-30   |
2    | 6JFGGU9P | 2019-03-31   | 
3    | UYVEWVN0 | 2019-04-01   |

Table BREAKTIME:

ID   | EMPLOYEE   | CODE     | TOTAL_BREAKTIME 
1    | JOHN       | 9VIHQIHE | 00:59:01 
2    | RYAN       | 9VIHQIHE | 01:01:20
3    | JOHN       | 6JFGGU9P | 01:15:08
4    | RYAN       | 6JFGGU9P | 00:58:12
5    | JOHN       | UYVEWVN0 | 00:57:32
6    | RYAN       | UYVEWVN0 | 01:05:16

Now, I need a report that will give me a summary of total break of each employee month-wise.

The report should look like this when my search parameter is March 2019.

EMPLOYEE  |   01   |  02  | .......... |    30    |    31
JOHN      |   -    |  -   | .......... | 00:59:01 | 01:15:08
RYAN      |   -    |  -   | .......... | 01:01:20 | 00:58:12

I have not written any code because I don't know where to start with.

Please, I need your help.

Answer 1

You can accomplish this with a single JOIN:

SELECT 
    DATE_FORMAT(c.date_created, '%d') AS date,
    b.employee,
    b.total_breaktime
FROM
    code c
    JOIN breaktime b ON b.code = c.code
WHERE
    DATE_FORMAT(date_created, '%Y-%m') = '2019-03'
ORDER BY employee , date_created;

will get you a dataset like

30  JOHN    00:59:01
31  JOHN    01:15:08
30  RYAN    01:01:20
31  RYAN    00:58:12

You could then first loop through this data and restructure it into a 2D array with employee as the key so each "employee" can be its own row in the final output:

while($b = $result->fetch_assoc()){
    $employee = $b["employee"];
    $date = $b["date"];
    $time = $b["total_breaktime"];

    $BreakTime[$employee][$date] = $time;
}

results in:

$BreakTime = array(
    'JOHN' => array(
        '30' => '00:59:01',
        '31' => '01:15:08',
    ),
    'RYAN' => array(
        '30' => '01:01:20',
        '31' => '00:58:12',
    );
);

then finally you can loop through that data to output it to the page.

echo "<table><tbody>";

foreach($BreakTime as $key => $val) {
    echo "<tr><td>" .$key. "</td>";
    for($d=1; $d<=31; $d++) {
        echo "<td>";
        echo isset($BreakTime[$key][$d]) ? $BreakTime[$key][$d] : '00:00';
        echo "</td>";
    }
    echo "</tr>";
}

echo "</tbody></table>";