求助：如何基于MySQL数据构建Chart.js堆叠柱状图？

我正尝试基于MySQL表构建Chart.js堆叠柱状图，但生成的图表不符合预期，请求技术帮助。以下是我的实现代码及数据：

SQL查询代码 #

function getStudentMark()
{
    $query = "SELECT machine_type,line, COUNT(*) as linewise FROM dr_scan  GROUP BY machine_type,line";
    $result = $this->dbConn->select($query);
    return $result;
}

get_chart.php代码 #

<?php
use wacoal\Util;
use wacoal\Student;

header('Content-Type: application/json');

require_once __DIR__ . '/../lib/wacoal.php';
$student = new Machine();
$result = $student->getMachine();


$data = array();
$data2 = array();

require_once __DIR__ . '/../lib/Util.php';
$util = new Util();

$type = "bar";
if (! empty($_GET["chart_type"])) {
    $type = $_GET["chart_type"];
}

switch ($type) {
                
    case "vertical-bar":
        foreach ($result as $k => $v) {
            $data[$k] = array(
                "label" => $result[$k]["machine_type"],
                    "labels" => $result[$k]["line"],
                
                "data" => [
                    $result[$k]["linewise"]
                ],
                'backgroundColor' => $util->getRandomColor()
            );
        };
        
            
}
$data = json_encode($data);
echo str_replace("},
","},
",$data);

接口输出JSON #

[{"label":"3 th O\/L","labels":"1","data":[3],"backgroundColor":"#ac9f0d"},
{"label":"Button Attach","labels":"2","data":[1],"backgroundColor":"#74589b"},
{"label":"Button Attach","labels":"5","data":[2],"backgroundColor":"#3abea2"},
{"label":"Double Needle","labels":"1","data":[1],"backgroundColor":"#3aacb0"},
{"label":"Single Needle","labels":"1","data":[3],"backgroundColor":"#df9001"}]

Chart.js脚本 #

function showStackedVerticalChart() {
    {
        $.post("ajax-endpoint/get-chart-data.php?chart_type=vertical-bar",
            function(data) {
                    console.log(data);
                var name = [];
            
                for (var i in data) {
                    name.push(data[i].labels);
                
                }
                
                                
                var chartdata = {
                    labels: name,
                    datasets: data
                };

                var graphTarget = $("#stacked-vertical-chart");

                var graph = new Chart(graphTarget, {
                    type: 'bar',
                    data: chartdata,
                    options: {
                        
                        scales: {
                            xAxes: [{
                                barPercentage: 0.3,
                                stacked: true
                            }],
                            yAxes: [{
                                stacked: true
                            }]
                        }
                    }
                });
            });
    }
}

问题分析与修复方案 #

你的代码核心问题是数据格式完全不符合Chart.js堆叠柱状图的要求：

1. 相同machine_type的数据被拆成了独立dataset，每个dataset的data只有单个值，无法对应所有x轴标签；
2. x轴标签重复，且dataset没有为所有标签位置填充对应数值（缺失的位置需填0）。

步骤1：修正后端数据格式（get_chart.php） #

需要将同类型设备的数据合并为单个dataset，同时为所有存在的line（x轴标签）填充数值，不存在的填0。同时修复函数调用不匹配的问题（getStudentMark和getMachine）。

修改后的get_chart.php关键代码：

case "vertical-bar":
    // 先收集所有唯一的line作为x轴标签
    $lines = array_unique(array_column($result, 'line'));
    sort($lines); // 排序保证顺序一致
    
    // 按machine_type分组统计数据
    $machineGroups = [];
    foreach ($result as $item) {
        $type = $item['machine_type'];
        if (!isset($machineGroups[$type])) {
            $machineGroups[$type] = [
                'label' => $type,
                'data' => array_fill_keys($lines, 0), // 初始化所有line为0
                'backgroundColor' => $util->getRandomColor()
            ];
        }
        $machineGroups[$type]['data'][$item['line']] = $item['linewise'];
    }
    
    // 将分组后的数据转为数组，并把data转为有序数组
    $data = [];
    foreach ($machineGroups as $group) {
        $group['data'] = array_values($group['data']);
        $data[] = $group;
    }
    
    // 组装包含标签的完整响应
    $response = [
        'labels' => $lines,
        'datasets' => $data
    ];
    echo json_encode($response);

修改后接口输出的JSON格式会变成：

{
    "labels": ["1","2","5"],
    "datasets": [
        {"label":"3 th O\/L","data":[3,0,0],"backgroundColor":"#ac9f0d"},
        {"label":"Button Attach","data":[0,1,2],"backgroundColor":"#74589b"},
        {"label":"Double Needle","data":[1,0,0],"backgroundColor":"#3aacb0"},
        {"label":"Single Needle","data":[3,0,0],"backgroundColor":"#df9001"}
    ]
}

步骤2：修正前端Chart.js代码 #

直接使用后端返回的完整标签和数据集，同时适配Chart.js版本（v3+使用x/y代替xAxes/yAxes）：

function showStackedVerticalChart() {
    $.post("ajax-endpoint/get-chart-data.php?chart_type=vertical-bar", function(response) {
        var graphTarget = $("#stacked-vertical-chart");
        new Chart(graphTarget, {
            type: 'bar',
            data: response,
            options: {
                scales: {
                    x: {
                        barPercentage: 0.3,
                        stacked: true
                    },
                    y: {
                        stacked: true
                    }
                }
            }
        });
    });
}

额外注意 #

• 若使用Chart.js v2版本，保持xAxes和yAxes的写法；
• 后端需确保$student->getStudentMark()调用正确，原代码中getMachine是错误的。

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