MySQL分组fruit_tb表数据生成Chart.js可用JSON格式问题求助

需要将MySQL中fruit_tb表的数据按ng_fy字段分组，把对应分组下的cat_item和ng_value整理为指定JSON格式，用于Chart.js绘制柱状图。当前使用的SQL查询如下：

$query = "SELECT ng_fy, cat_item, ng_value FROM fruit_tb GROUP BY ng_fy, cat_item ORDER BY ng_fy ASC";

通过PHP循环处理后未得到预期结果，原表数据如下：

预期输出的JSON格式：

{
  "2019/20": [
    {"cat_item": "Apples", "ng_value": 5},
    {"cat_item": "Mangoes", "ng_value": 14},
    {"cat_item": "Oranges", "ng_value": 3},
    {"cat_item": "Pears", "ng_value": 2}
  ],
  "2020/21": [
    {"cat_item": "Apples", "ng_value": 11},
    {"cat_item": "Mangoes", "ng_value": 4},
    {"cat_item": "Oranges", "ng_value": 13},
    {"cat_item": "Pears", "ng_value": 0}
  ],
  "2022/23": [
    {"cat_item": "Apples", "ng_value": 1},
    {"cat_item": "Mangoes", "ng_value": 4},
    {"cat_item": "Oranges", "ng_value": 18},
    {"cat_item": "Pears", "ng_value": 21}
  ]
}

1. 优化SQL查询 #

原SQL中的GROUP BY ng_fy, cat_item属于冗余操作，因为表中每个ng_fy与cat_item的组合都是唯一的，直接查询并排序即可：

$query = "SELECT ng_fy, cat_item, ng_value FROM fruit_tb ORDER BY ng_fy ASC, cat_item ASC";

添加cat_item排序可确保每个分组内的水果顺序与预期输出一致。

2. PHP分组处理逻辑 #

遍历数据库查询结果，按ng_fy分组构建目标数组，再转为JSON：

// 假设已通过mysqli/PDO执行SQL并获取结果集$results
$groupedData = [];

foreach ($results as $row) {
    $fy = $row['ng_fy'];
    // 初始化年份分组
    if (!isset($groupedData[$fy])) {
        $groupedData[$fy] = [];
    }
    // 向对应分组添加数据，强制ng_value为数字类型
    $groupedData[$fy][] = [
        'cat_item' => $row['cat_item'],
        'ng_value' => (int)$row['ng_value']
    ];
}

// 生成格式化的JSON输出
$jsonOutput = json_encode($groupedData, JSON_PRETTY_PRINT);

echo $jsonOutput;

3. 关键注意事项 #

• 若数据库中ng_value存储为字符串类型，需通过(int)强制转换为数字，避免JSON中出现字符串形式的数值；
• 使用JSON_PRETTY_PRINT参数可生成与预期格式一致的格式化JSON，便于调试。

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