php - 按 JSON 数组对 SQL 数据进行分组

Question

我正在为教师开发一个显示学生考试成绩的页面。

要显示图表，我想使用 Highcharts JavaScript 库。

到目前为止，我有一个使用 PDO 创建 JSON 数据的 PHP 脚本，稍后可以将这些数据从不同的页面提供给 Highcharts。

我的问题:如何将来自同一 student 的所有数据分组到一个数组中？请参阅最后一个示例，了解我希望实现的目标。另外:我希望将所有数据包含在一个总体 JSON 数组中。

我想要这个:

[{
  "student": "Andreas",
  "level" : [4, 3]
}, {
  "student": "Eivind",
  "level" : [4, 5]
}, {
  "student": "Ole",
  "level" : [4, 3]
}]

这是我的 PHP 的样子:

<?php

require("config.inc.php");

$school = $_GET["school"];
$class = $_GET["class"];

//initial query
$query = 'SELECT student, taskid, level FROM task
            WHERE school=' . '"' . $school . '"' .  ' AND class=' . '"' . $class . '" ORDER BY student';

//execute query
try {
    $stmt   = $db->prepare($query);
    $result = $stmt->execute();
}
catch (PDOException $ex) {
    $response["success"] = 0;
    $response["message"] = "Database Error!";
    die(json_encode($response));
}

// Finally, we can retrieve all of the found rows into an array using fetchAll 
$rows = $stmt->fetchAll();

if ($rows) {
    $response["posts"]   = array();

    foreach ($rows as $row) {
        $post               = array();
        $post["student"]       = $row["student"];
        $post["level"]         = $row["level"];


        //update our repsonse JSON data
        array_push($response["posts"], $post);
    }

    // echoing JSON response
    echo json_encode($response, JSON_NUMERIC_CHECK);


} else {
    $response["success"] = 0;
    $response["message"] = "No Post Available!";
    die(json_encode($response));
}

?>

这是我从 PHP 得到的:

{
  "posts": [
    {
      "student": "Andreas",
      "level": 4
    },
    {
      "student": "Andreas",
      "level": 3
    },
    {
      "student": "Eivind",
      "level": 4
    },
    {
      "student": "Eivind",
      "level": 5
    },
    {
      "student": "Ole",
      "level": 4
    },
    {
      "student": "Ole",
      "level": 3
    }
  ]
}

Answer 1

var data=[ 
 { "category" : "Search Engines", "hits" : 5, "bytes" : 50189 },
 { "category" : "Content Server", "hits" : 1, "bytes" : 17308 },
 { "category" : "Content Server", "hits" : 1, "bytes" : 47412 },
 { "category" : "Search Engines", "hits" : 1, "bytes" : 7601 },
 { "category" : "Business", "hits" : 1, "bytes" : 2847 },
 { "category" : "Content Server", "hits" : 1, "bytes" : 24210 },
 { "category" : "Internet Services", "hits" : 1, "bytes" : 3690 },
 { "category" : "Search Engines", "hits" : 6, "bytes" : 613036 },
 { "category" : "Search Engines", "hits" : 1, "bytes" : 2858 } 
];

var res = alasql('SELECT category, sum(hits) AS hits, sum(bytes) as bytes \
FROM ? \
GROUP BY category \
ORDER BY bytes DESC',[data]);

您将得到如下输出:

[{"category":"Search Engines","hits":13,"bytes":673684},
 {"category":"Content Server","hits":3,"bytes":88930},
 {"category":"Internet Services","hits":1,"bytes":3690},
 {"category":"Business","hits":1,"bytes":2847}]