datatables dates at the top and data cells going f

Question

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 services against the count of those present on a particular date. The query is correct but I am having issues formatting the table correctly

the data should display as so

SERVICE | DATE1 | DATE2 | etc

---

HEALTH | 5 | 3 | etc

---

DENTIST | 10 | 20 | etc

This is currently what I have done:

<div class="col-sm-12">         
                <div class="box-body no-padding">
                        <table class="table cell-border" id="members">
                            <thead>
                                <th>Service</th>
                                    <?php foreach($result as $g){?>
                                            <th><?php echo $g['date'];?></th>
                                    <?php }?>

                            </thead>
                            <tbody> 
                                <tr>
                                    <td><?php echo $g['Service Name'];?></td>
                                    <?php $i=1; foreach($result as $g){ ?>
                                    <td> <?php if($g['Attendance'] == 'NULL') {echo 0;} else { echo $g['Attendance'];}?> </td>
                                    <?php }?>
                                </tr>
                            </tbody>
                        </table>
                        </div>

Here is the query:

SELECT record_attendance_cell.id, record_attendance_cell.`company_id`,record_attendance_cell.`cell_id`,record_attendance_cell.`member_id`, COUNT(record_attendance_cell.`present`) as `Attendance`, record_attendance_cell.`service_id`,record_attendance_cell.`date`, setting_company.name `Company Name`, setting_cell_group.`location`, `setting_service`.title as `Service Name`
FROM `record_attendance_cell`
LEFT JOIN setting_company
on setting_company.id = record_attendance_cell.company_id
LEFT JOIN setting_cell_group
on setting_cell_group.id = record_attendance_cell.cell_id
LEFT JOIN setting_service
on setting_service.id = record_attendance_cell.service_id
WHERE record_attendance_cell.`present` = 1 AND record_attendance_cell.`date` BETWEEN '$date_from' AND '$date_to'
group by record_attendance_cell.service_id
order by record_attendance_cell.date asc

Answer 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