使用While循环在我的表格中的某个地方添加PHP

Question

Well, I have a table with some dates and hours. I get this data from my DB using a while loop.

I want to add 1 line after the date change to the next one, So I can calculate de day's total hours. I just need 1 row after every day change (picture below as example)

            $stmt5->execute();   
            while($row = $stmt5->fetch(PDO::FETCH_ASSOC)){
                if($gewerkt == 0){
                    $gewerkt = $row['HOURS_WORKED'];
                }else{
                    $gewerkt = $gewerkt + $row['HOURS_WORKED'];
                }             
                echo "
                <td>" .$row['TRANSACTION_ID']."</td>
                <td>" .$row['RESOURCE_ID']."</td>
                <td></td>
                <td>" .$row['DEPARTMENT_ID']."</td>
                <td>" .$row['WORKORDER_BASE_ID']."/".$row['WORKORDER_LOT_ID'].".".$row['WORKORDER_SPLIT_ID']."-".$row['WORKORDER_SUB_ID'].":".$row['OPERATION_SEQ_NO']."</td>
                <td>" .date('Y-m-d', strtotime($row['TRANSACTION_DATE']))."</td>
                <td>" .TimeValue($row['CLOCK_IN'])."</td>
                <td>" .TimeValue($row['CLOCK_OUT'])."</td>
                <td>" .floatval($row['HOURS_BREAK'])."</td>
                <td>" .floatval($row['HOURS_WORKED'])."</td>
                <td></td>
                <td><form action='' method='POST'>
                            <button class='btn btn-default' type='submit' name='delete'>Verwijderen</button>
                    </form></td>
            </tbody>
            ";
            }

The below photo is an example of my table. As you can see, I have the first 3 lines with the same date. after these 3 lines, I want just 1 extra line where I can calculate the day total.

Answer 1

You can aggregate all the rows and subtotals before actually displaying them. This approach might take up a bit of memory, but it is very readable.

// aggregate the rows first
while($row = $stmt5->fetch(PDO::FETCH_ASSOC)){
    $row['ROW_DATE'] = date('Y-m-d', strtotime($row['TRANSACTION_DATE']));
    $dateRows[$row['ROW_DATE']]['rows'][] = $row; // aggregate a 2D array
    $dateRows[$row['ROW_DATE']]['HOURS_WORKED'] = isset($dateRows[$row['ROW_DATE']]['HOURS_WORKED'])
        ? $dateRows[$row['ROW_DATE']]['HOURS_WORKED'] + $row['HOURS_WORKED']
        : $row['HOURS_WORKED'];
}

foreach ($dateRows as $date => $rows) {
    // loop through all the rows of the day
    foreach ($rows['rows'] as $row) {
        echo "
        <td>" .$row['TRANSACTION_ID']."</td>
        <td>" .$row['RESOURCE_ID']."</td>
        <td></td>
        <td>" .$row['DEPARTMENT_ID']."</td>
        <td>" .$row['WORKORDER_BASE_ID']."/".$row['WORKORDER_LOT_ID'].".".$row['WORKORDER_SPLIT_ID']."-".$row['WORKORDER_SUB_ID'].":".$row['OPERATION_SEQ_NO']."</td>
        <td>" .$date."</td>
        <td>" .TimeValue($row['CLOCK_IN'])."</td>
        <td>" .TimeValue($row['CLOCK_OUT'])."</td>
        <td>" .floatval($row['HOURS_BREAK'])."</td>
        <td>" .floatval($row['HOURS_WORKED'])."</td>
        <td></td>
        <td>
            <form action='' method='POST'>
                <button class='btn btn-default' type='submit' name='delete'>Verwijderen</button>
            </form>
        </td>
";
    }

    // display a subtotal row
    echo "
    <td colspan='9'>Subtotal:</td>
    <td>{$rows['HOURS_WORKED']}</td>
    <td></td>
    <td></td>
";
}