如何用PHP+mysqli将tbl_meals数据转换为指定统计表格格式

现有数据 #

已成功连接week_meal数据库，tbl_meals表的结构及数据如下：

+-----+---------+---------+---------+---------+----------+
| id  | receiver|  bfast  |  lunch  |  dinner |   date   |
+-----+---------+---------+---------+---------+----------+
|  1  | smith   | served  |         | served  | 04-18-23 |
+-----+---------+---------+---------+---------+----------+
|  2  | philip  |         |  served | served  | 04-18-23 |
+-----+---------+---------+---------+---------+----------+
|  3  | mercede | served  |         | served  | 04-19-23 |
+-----+---------+---------+---------+---------+----------+
|  4  | annie   |         |  served | served  | 04-20-23 |
+-----+---------+---------+---------+---------+----------+

目标格式 #

需要生成如下格式的HTML表格：

+---------+----------+----------+----------+
|   Date  | April 18 | April 19 | April 20 | and so on, until the last tbl_meals of each week
+---------+----------+----------+----------+
|Breakfast|   1      |    1     |  None    |
+---------+----------+----------+----------+
|  Lunch  |   1      |  None    |   1      |
+---------+----------+----------+----------+
|  Dinner |   2      |    1     |   1      |
+---------+----------+----------+----------+

当前进度 #

作为PHP、mysqli新手，目前仅能通过以下代码获取date列的去重值：

$sql = "SELECT DISTINCT date 
        FROM tbl_meals 
        ORDER BY date ASC";
$res = mysqli_query($connection, $sql); 

具体实现步骤 #

1. 优化SQL查询，统计每日三餐数据 #

用一条SQL直接按日期分组，统计每天的早餐、午餐、晚餐供应人数，减少PHP端的处理量：

SELECT 
    date,
    COUNT(CASE WHEN bfast = 'served' THEN 1 END) AS bfast_count,
    COUNT(CASE WHEN lunch = 'served' THEN 1 END) AS lunch_count,
    COUNT(CASE WHEN dinner = 'served' THEN 1 END) AS dinner_count
FROM tbl_meals
GROUP BY date
ORDER BY date ASC;

2. PHP处理数据并生成HTML表格 #

将查询结果整理成数组，把日期格式从MM-DD-YY转换成Month DD形式，再循环生成表格：

// 假设$connection已经是成功连接数据库的mysqli对象
$sql = "SELECT 
            date,
            COUNT(CASE WHEN bfast = 'served' THEN 1 END) AS bfast_count,
            COUNT(CASE WHEN lunch = 'served' THEN 1 END) AS lunch_count,
            COUNT(CASE WHEN dinner = 'served' THEN 1 END) AS dinner_count
        FROM tbl_meals
        GROUP BY date
        ORDER BY date ASC";
$res = mysqli_query($connection, $sql);

// 整理数据到数组
$mealData = [];
$dates = [];
while ($row = mysqli_fetch_assoc($res)) {
    // 转换日期格式：04-18-23 → April 18
    $dateObj = DateTime::createFromFormat('m-d-y', $row['date']);
    $formattedDate = $dateObj->format('F j');
    $dates[] = $formattedDate;
    
    $mealData[$formattedDate] = [
        'bfast' => $row['bfast_count'] ?: 'None',
        'lunch' => $row['lunch_count'] ?: 'None',
        'dinner' => $row['dinner_count'] ?: 'None'
    ];
}

// 生成HTML表格
echo '<table border="1">';
// 表头行
echo '<tr>';
echo '<th>Date</th>';
foreach ($dates as $date) {
    echo "<th>$date</th>";
}
echo '</tr>';

// 早餐行
echo '<tr>';
echo '<th>Breakfast</th>';
foreach ($dates as $date) {
    echo "<td>{$mealData[$date]['bfast']}</td>";
}
echo '</tr>';

// 午餐行
echo '<tr>';
echo '<th>Lunch</th>';
foreach ($dates as $date) {
    echo "<td>{$mealData[$date]['lunch']}</td>";
}
echo '</tr>';

// 晚餐行
echo '<tr>';
echo '<th>Dinner</th>';
foreach ($dates as $date) {
    echo "<td>{$mealData[$date]['dinner']}</td>";
}
echo '</tr>';

echo '</table>';

代码说明 #

• SQL用CASE+COUNT统计每日三餐的有效供应数，空值会被统计为0，PHP中用?:转换为None
• DateTime::createFromFormat解析原始日期格式，转换成目标的月份+日期样式
• 先把数据整理成以格式化日期为键的数组，方便后续生成表格时按顺序取值

内容的提问来源于stack exchange，提问作者Jephoy