MySQL选择查询和PHP转换为嵌套JSON对象

debugcn 发表于 Dev

凯南

我尝试使用嵌套的对象属性和值来检索唯一的对象，是否有可能以json对象键及其子属性和值来检索

SELECT id,name,year FROM `v_exam_schedule` ORDER BY year ASC;


+----+----------+----------+
| id | name     | year     |
+----+----------+----------+
|  3 | MID TERM | 2021     |
|  4 | Fina Exam| 2021     |
|  1 | Monthly  | 2022     |
+----+----------+----------+
3 rows in set (0.001 sec)

数组的json编码结果的php代码

$arr = array();
$q = $db->query("SELECT id,name,year FROM `v_exam_schedule` ORDER BY year ASC");

while ($rs = $q->fetch_assoc()) {
    $arr[] = array(
               $rs['year'] => array(
                    "id" => $rs['id'], 
                    "name" => $rs['name']
               )
       );
}
exit(json_encode($arr));

结果php代码

[
    {
        "2022": {
            "id": "1",
            "name": "Monthly"
        }
    },
    {
        "2021": {
            "id": "3",
            "name": "MID TERM"
        }
    },
    {
        "2021": {
            "id": "4",
            "name": "FINAL EXAM"
        }
    }
]

我想用下面的年份检索唯一的对象键

[
    {
        "2022": {
            "id": "1",
            "name": "Monthly"
        }
    },
    {
        "2021": [
                  {
                      "id": "4",
                      "name": "FINAL EXAM"
                  },
                  {
                     "id": "3",
                     "name": "MID TERM"
                  }
               ]
    }
]

生死

只需稍微更改一下代码即可：

$arr = [];//define array
while ($rs = $q->fetch_assoc()) {
   //Do year base assignments 
    $arr[$rs['year']][] = array(
               $rs['year'] => array(
                    "id" => $rs['id'], 
                    "name" => $rs['name']
               )
       );
}

exit(json_encode(($arr)));

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。