PHP MySQL开发Line Chart时无法显示月份且未按月份分组问题求助

问题原因 #

你当前的代码不符合Chart.js的配置逻辑：全局labels是X轴所有月份的统一配置，不是写在每个dataset里；且你每次循环数据库行都生成一个独立的dataset，每个dataset只有1个数据点，自然无法按月份分组展示。

优化方案 #

1. 调整SQL查询

增加月份数字排序，保证X轴月份按时间顺序排列：

SELECT 
  decease_name, 
  DATE_FORMAT(date, '%M') as month_name,
  MONTH(date) as month_num,
  count(*) as consultations 
FROM prescription 
WHERE decease_name !='Reconsultation Need' 
  AND date BETWEEN ADDDATE(NOW(),-365) AND NOW()  
GROUP BY decease_name, month_name, month_num
ORDER BY month_num ASC

2. PHP层提前整理数据结构

先把月份、疾病对应就诊数的结构整理好，避免渲染时逻辑混乱：

<?php
$stmt3 = $conn->prepare("SELECT 
  decease_name, 
  DATE_FORMAT(date, '%M') as month_name,
  MONTH(date) as month_num,
  count(*) as consultations 
FROM prescription 
WHERE decease_name !='Reconsultation Need' 
  AND date BETWEEN ADDDATE(NOW(),-365) AND NOW()  
GROUP BY decease_name, month_name, month_num
ORDER BY month_num ASC");
$stmt3->execute();
$result3 = $stmt3->get_result();

// 存储所有去重后的月份（X轴标签）
$all_months = [];
// 存储每个疾病对应各月份的就诊数
$disease_map = [];

while($row = mysqli_fetch_assoc($result3)){
    $month = $row['month_name'];
    $disease = trim($row['decease_name']);
    $cnt = $row['consultations'];
    
    // 月份去重加入全局标签
    if(!in_array($month, $all_months)){
        $all_months[] = $month;
    }
    // 记录疾病对应月份的就诊数
    if(!isset($disease_map[$disease])){
        $disease_map[$disease] = [];
    }
    $disease_map[$disease][$month] = $cnt;
}

// 补全每个疾病所有月份的数值，无数据的月份填0，避免数据错位
foreach($disease_map as $disease => $month_cnt){
    foreach($all_months as $month){
        if(!isset($month_cnt[$month])){
            $disease_map[$disease][$month] = 0;
        }
    }
    // 按全局月份顺序排序数据
    ksort($disease_map[$disease]);
}
?>

3. 渲染Chart.js配置

{
  // 全局X轴标签统一配置
  labels: [<?php echo "'".implode("','", $all_months)."'"; ?>],
  datasets: [
    <?php foreach($disease_map as $disease_name => $count_list): ?>
    {
      label: "<?php echo $disease_name; ?>",
      data: [<?php echo implode(',', $count_list); ?>],
      borderColor: getRandomColor(),
      fill: false
    },
    <?php endforeach; ?>
  ]
}

改动说明 #

• 统一把所有月份放在全局labels属性里，X轴即可正常显示月份
• 每种疾病对应一个dataset，数据顺序和X轴月份完全对应，没有数据的月份补0，不会出现数据错位
• SQL增加月份排序，保证X轴不会出现月份乱序的问题

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