数据表的日期位于顶部，数据单元从左至右 [英] 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 services against the count of those present on a particular date. The query is correct but I am having issues formatting the table correctly

数据应显示为

服务| DATE1 | DATE2 |等

SERVICE | DATE1 | DATE2 | etc

健康| 5 | 3 |等等

HEALTH | 5 | 3 | etc

牙医| 10 | 20 |等等

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>

这里是查询：

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

推荐答案

根据您的结果和描述。我想出了这个：

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>';

输出：

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

如果您希望累积出勤，只需更改WHILE循环（ADDED）

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>';

输出

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

这篇关于数据表的日期位于顶部，数据单元从左至右的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！