如何在表格中显示值为0的字段？PHP+MySQL数据统计问题

你的核心问题是原有SQL查询仅返回存在数据的尺码分组，导致无数据的尺码列缺失。要实现所有尺码展示、无数据显示0的效果，需要从SQL查询补全尺码和PHP固定顺序输出两方面调整：

1. 优化SQL查询：补全所有尺码 #

通过构建包含全部尺码的临时数据集，再与Uniforms表左连接，确保所有尺码都被保留，无数据的分组用COALESCE将NULL转为0。

T恤统计查询 #

SELECT 
    s.size AS tshirt_size,
    COALESCE(SUM(u.tshirt_quantity), 0) AS TotalTshirts
FROM (
    SELECT 'XS' AS size UNION ALL
    SELECT 'S' UNION ALL
    SELECT 'M' UNION ALL
    SELECT 'L' UNION ALL
    SELECT 'XL' UNION ALL
    SELECT 'XXL' UNION ALL
    SELECT 'XXXL'
) s
LEFT JOIN Uniforms u ON s.size = u.tshirt_size
GROUP BY s.size
ORDER BY FIELD(s.size, 'XS', 'S', 'M', 'L', 'XL', 'XXL', 'XXXL')

裤子统计查询 #

SELECT 
    s.size AS pant_size,
    COALESCE(SUM(u.pant_quantity), 0) AS TotalPants
FROM (
    SELECT 'XS' AS size UNION ALL
    SELECT 'S' UNION ALL
    SELECT 'M' UNION ALL
    SELECT 'L' UNION ALL
    SELECT 'XL' UNION ALL
    SELECT 'XXL' UNION ALL
    SELECT 'XXXL'
) s
LEFT JOIN Uniforms u ON s.size = u.pant_size
GROUP BY s.size
ORDER BY FIELD(s.size, 'XS', 'S', 'M', 'L', 'XL', 'XXL', 'XXXL')

说明：

• 子查询用UNION ALL生成所有尺码的基础列表
• LEFT JOIN确保即使Uniforms表中无对应尺码数据，也会保留该尺码行
• COALESCE把SUM返回的NULL（无数据时）转换成0
• ORDER BY FIELD强制尺码顺序和表头一致，避免输出混乱

2. 调整PHP展示逻辑：固定顺序输出 #

先定义和表头一致的尺码数组，将查询结果转为关联数组方便取值，最后循环尺码数组输出每一列，确保列顺序和数量完全匹配表头。

完整PHP代码示例：

// 定义固定尺码顺序，与表头保持一致
$sizes = ['XS', 'S', 'M', 'L', 'XL', 'XXL', 'XXXL'];

// --------------------------
// 获取T恤统计数据
// --------------------------
$sqlTshirt = "SELECT 
    s.size AS tshirt_size,
    COALESCE(SUM(u.tshirt_quantity), 0) AS TotalTshirts
FROM (
    SELECT 'XS' AS size UNION ALL
    SELECT 'S' UNION ALL
    SELECT 'M' UNION ALL
    SELECT 'L' UNION ALL
    SELECT 'XL' UNION ALL
    SELECT 'XXL' UNION ALL
    SELECT 'XXXL'
) s
LEFT JOIN Uniforms u ON s.size = u.tshirt_size
GROUP BY s.size
ORDER BY FIELD(s.size, 'XS', 'S', 'M', 'L', 'XL', 'XXL', 'XXXL')";
$qTshirt = $connect->prepare($sqlTshirt);
$qTshirt->execute();
$resultTshirt = $qTshirt->fetchAll(PDO::FETCH_ASSOC);

// 转换为以尺码为键的关联数组，方便后续取值
$tshirtData = [];
foreach ($resultTshirt as $row) {
    $tshirtData[$row['tshirt_size']] = $row['TotalTshirts'];
}

// --------------------------
// 获取裤子统计数据
// --------------------------
$sqlPant = "SELECT 
    s.size AS pant_size,
    COALESCE(SUM(u.pant_quantity), 0) AS TotalPants
FROM (
    SELECT 'XS' AS size UNION ALL
    SELECT 'S' UNION ALL
    SELECT 'M' UNION ALL
    SELECT 'L' UNION ALL
    SELECT 'XL' UNION ALL
    SELECT 'XXL' UNION ALL
    SELECT 'XXXL'
) s
LEFT JOIN Uniforms u ON s.size = u.pant_size
GROUP BY s.size
ORDER BY FIELD(s.size, 'XS', 'S', 'M', 'L', 'XL', 'XXL', 'XXXL')";
$qPant = $connect->prepare($sqlPant);
$qPant->execute();
$resultPant = $qPant->fetchAll(PDO::FETCH_ASSOC);

$pantData = [];
foreach ($resultPant as $row) {
    $pantData[$row['pant_size']] = $row['TotalPants'];
}

// --------------------------
// 输出完整表格
// --------------------------
echo '<table class="s-table">';
// 表头
echo '<thead><tr>';
echo '<th>Uniform</th>';
foreach ($sizes as $size) {
    echo '<th>' . htmlentities($size) . '</th>';
}
echo '</tr></thead>';

// 表体
echo '<tbody>';
// T恤行
echo '<tr>';
echo '<td>T-Shirts</td>';
foreach ($sizes as $size) {
    $quantity = $tshirtData[$size] ?? 0; // 无数据则用0
    echo '<td>' . htmlentities($quantity) . '</td>';
}
echo '</tr>';

// 裤子行
echo '<tr>';
echo '<td>Pants</td>';
foreach ($sizes as $size) {
    $quantity = $pantData[$size] ?? 0;
    echo '<td>' . htmlentities($quantity) . '</td>';
}
echo '</tr>';
echo '</tbody></table>';

额外优化提示 #

你的表结构中pant_size设置为NOT NULL，但表单处理逻辑中用户未选择裤子时pant_size可能为空，会导致插入失败。建议：

• 要么调整表单处理，未选裤子时给pant_size设一个默认值（比如选择一个不常用的尺码，或调整表结构允许pant_size为NULL）
• 要么修改表结构：pant_size SET('XS','S','M','L','XL','XXL','XXXL') NULL

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