Chart.js展示SQL统计结果异常：所有分段均显示1求助

你遇到的问题很典型——误用了获取结果行数的函数来取统计值，导致每个分段都显示1。咱们一步步拆解问题和解决方法：

问题根源 #

你现在用mysqli_num_rows($resultxxx)来输出数据，但这个函数的作用是返回查询结果集的行数。而你的每个SELECT COUNT(...)查询，不管统计出来的数值是多少，都会返回1行结果（因为COUNT聚合函数只会生成一行统计数据），所以每次输出的都是1，这就是图表全显示1的核心原因。

你真正需要的是从查询结果里取出COUNT计算后的实际数值，而不是结果集的行数。

修复方案 #

方案1：修改现有代码，正确获取统计值 #

把每个mysqli_num_rows()替换成mysqli_fetch_row()来提取统计结果：

<script>
var ctx = document.getElementById('myChart').getContext('2d');
var myChart = new Chart(ctx, {
    type: 'doughnut',
    data: {
        labels: ['Body Pump', 'Spin Class', 'Yoga', 'Body Tone', 'Legs, Bums & Tums'],
        datasets: [{
            label: 'Total Number of Bookings',
            data: [
                <?php
                // 获取Body Pump的统计值
                $resultbodypump = mysqli_query($db, "SELECT COUNT(SessionType) FROM bookings WHERE username = '" . $_SESSION['username'] . "' AND SessionType = 'bodyPump'");
                $row = mysqli_fetch_row($resultbodypump);
                echo $row[0] . ",";
                
                // 获取Spin Class的统计值
                $resultspinclass = mysqli_query($db, "SELECT COUNT(SessionType) FROM bookings WHERE username = '" . $_SESSION['username'] . "' AND SessionType = 'spinClass'");
                $row = mysqli_fetch_row($resultspinclass);
                echo $row[0] . ",";
                
                // 获取Yoga的统计值
                $resultyoga = mysqli_query($db, "SELECT COUNT(SessionType) FROM bookings WHERE username = '" . $_SESSION['username'] . "' AND SessionType = 'yoga'");
                $row = mysqli_fetch_row($resultyoga);
                echo $row[0] . ",";
                
                // 获取Body Tone的统计值
                $resultbodytone = mysqli_query($db, "SELECT COUNT(SessionType) FROM bookings WHERE username = '" . $_SESSION['username'] . "' AND SessionType = 'bodyTone'");
                $row = mysqli_fetch_row($resultbodytone);
                echo $row[0] . ",";
                
                // 获取Legs, Bums & Tums的统计值
                $resultlegsbumstums = mysqli_query($db, "SELECT COUNT(SessionType) FROM bookings WHERE username = '" . $_SESSION['username'] . "' AND SessionType = 'legsBumsTums'");
                $row = mysqli_fetch_row($resultlegsbumstums);
                echo $row[0];
                ?>
            ],
        }]
    }
});
</script>

方案2：优化SQL查询（推荐） #

你现在执行了5次数据库查询，其实可以用一次GROUP BY查询拿到所有数据，既减少数据库压力，也让代码更简洁：

<script>
var ctx = document.getElementById('myChart').getContext('2d');
var myChart = new Chart(ctx, {
    type: 'doughnut',
    data: {
        labels: ['Body Pump', 'Spin Class', 'Yoga', 'Body Tone', 'Legs, Bums & Tums'],
        datasets: [{
            label: 'Total Number of Bookings',
            data: [
                <?php
                // 一次查询获取所有SessionType的统计
                $query = "SELECT SessionType, COUNT(SessionType) AS count 
                          FROM bookings 
                          WHERE username = '" . $_SESSION['username'] . "' 
                          AND SessionType IN ('bodyPump', 'spinClass', 'yoga', 'bodyTone', 'legsBumsTums')
                          GROUP BY SessionType";
                
                $result = mysqli_query($db, $query);
                // 先初始化所有类型的计数为0，避免某个类型无数据时出错
                $counts = [
                    'bodyPump' => 0,
                    'spinClass' => 0,
                    'yoga' => 0,
                    'bodyTone' => 0,
                    'legsBumsTums' => 0
                ];
                
                // 遍历查询结果更新计数
                while ($row = mysqli_fetch_assoc($result)) {
                    $counts[$row['SessionType']] = $row['count'];
                }
                
                // 按顺序输出计数
                echo $counts['bodyPump'] . ",";
                echo $counts['spinClass'] . ",";
                echo $counts['yoga'] . ",";
                echo $counts['bodyTone'] . ",";
                echo $counts['legsBumsTums'];
                ?>
            ],
        }]
    }
});
</script>

额外提醒：防止SQL注入 #

你的代码里直接把$_SESSION['username']拼到SQL语句里，存在SQL注入风险。建议用预处理语句来替代，示例如下：

// 预处理查询Body Pump的计数示例
$stmt = mysqli_prepare($db, "SELECT COUNT(SessionType) FROM bookings WHERE username = ? AND SessionType = 'bodyPump'");
mysqli_stmt_bind_param($stmt, "s", $_SESSION['username']);
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);
$row = mysqli_fetch_row($result);
echo $row[0];

这样能有效避免SQL注入攻击，让代码更安全。

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