MySQL select query and PHP convert as nested JSON object

I am try to retrieve unique object with nested object property and value, is there a possible way to retrieve as json object key and their child property and value

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)

php code for json encoding result of array

$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));

Result php code

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

I want to retrieve with unique object key for year like this below

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

Answer

Just change your code a bit:

$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)));

Advertisement

Answer