Datatables Dates At The Top And Data Cells Going From Left To Right

I am working on a system that caters services for small groups of registered members based on their locations (cells). The current issue I am having is displaying a list of service

Solution 1:

According to your result and your description. I have come up with this:

Please give it a try (EDITED):

<style>
    table th, table td {
        border: 1px solid #333;
    }
</style>
<?php
$result[] = array('Service Name' => 'Health', 'date' => '2017-04-04', 'Attendance' => 5);
$result[] = array('Service Name' => 'Payroll', 'date' => '2017-04-16', 'Attendance' => 5);
$result[] = array('Service Name' => 'Saturday Youth Meeting', 'date' => '2017-04-03', 'Attendance' => 1);
$result[] = array('Service Name' => 'Saturday Youth Meeting', 'date' => '2017-05-03', 'Attendance' => 3);
$result[] = array('Service Name' => 'Payroll', 'date' => '2017-05-03', 'Attendance' => 2);
$result[] = array('Service Name' => 'Payroll', 'date' => '2017-04-11', 'Attendance' => 3);

foreach ($result as $row) {
    $array[$row['Service Name']][$row['date']] = $row;
}

$start_date = '2017-04-03';
$end_date = '2017-05-03';
echo '<table><thead><tr><th>Service</th>';

$date = $start_date;
WHILE (strtotime($date) <= strtotime($end_date)) {
    echo '<th>' . $date . '</th>';
    $date = date('Y-m-d', strtotime($date . ' +1day'));
}
echo '</tr></thead>';
echo '<tbody>';

foreach ($array as $key => $value) {
    $date = $start_date;
    echo '<tr><td>'.$key.'</td>';
    WHILE (strtotime($date) <= strtotime($end_date)) {
        if (array_key_exists($date, $array[$key])) {
            echo '<td>' . $array[$key][$date]['Attendance'] . '</td>';
        } else {
            echo '<td>0</td>';
        }
    $date = date('Y-m-d', strtotime($date . ' +1day'));
    }
        echo '</tr>';
}

echo '</tbody></table>';

OUTPUT:

http://www.phpwin.org/s/ewbAS6

If you want the attendance to be accumulative, simply change the WHILE loop (ADDED)

foreach ($result as $row) {
    $array[$row['Service Name']][$row['date']] = $row;
}

$start_date = '2017-04-03';
$end_date = '2017-05-03';
echo '<table><thead><tr><th>Service</th>';

$date = $start_date;
WHILE (strtotime($date) <= strtotime($end_date)) {
    echo '<th>' . $date . '</th>';
    $date = date('Y-m-d', strtotime($date . ' +1day'));
}
echo '</tr></thead>';
echo '<tbody>';

foreach ($array as $key => $value) {
    $date = $start_date;
    echo '<tr><td>'.$key.'</td>';
    $attendance = 0;
    WHILE (strtotime($date) <= strtotime($end_date)) {
        if (array_key_exists($date, $array[$key])) {
            $attendance = $attendance + $array[$key][$date]['Attendance'];
        }
        echo '<td>' . $attendance .'</td>';
    $date = date('Y-m-d', strtotime($date . ' +1day'));
    }
        echo '</tr>';
}

echo '</tbody></table>';

OUTPUT

http://www.phpwin.org/s/5umFBA