I have created a JSON file from the database, which has two table semone with attributes id, semester, cname and table courses with attributes coname and credit.
Code I am writing in php is following.
main.php
<?php
$user = "root";
$password = "";
$database = "scheduler";
$con = mysqli_connect("localhost", $user, $password, $database) or die ("Unable to connect");
$query = "SELECT semone.userid AS sbuid, semone.semester AS semester,
semone.cname AS name, courses.credit AS value,
courses.progskill AS skill
FROM semone
INNER JOIN courses ON semone.cname = courses.coname" ;
$result = mysqli_query($con,$query)or die ("Unable to connect");
$info = array();
$test = array();
while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
$row['xyz'] = array(
'name'=> $row['name'],
'value'=> $row['value']
);
$info[$row['semester']]['children'][]= $row['xyz'];
$data = json_encode(array('id' => $row['sbuid'], 'children' => $info));
}
echo $data;
?>
I want to get JSON file as seen in the following code, but I am getting something like this.
**output.json**
{"id":"12345",
"children":
{"first":
{"children":
[{"name":"CSE101","value":"100"},
{"name":"CSE102","value":"100"}]},
"second":
{"children":
[{"name":"CSE103","value":"50"},
{"name":"CSE104","value":"100"},
{"name":"CSE105","value":"100"}]},
"third":
{"children":
[{"name":"CSE106","value":"50"}]}
}}
But this is what I am expecting.
**expected.json**
{
"id": 12345,
"children":
[{
"semester": "first",
"children":
[{
"name": "C101","value": 100},
{ "name": "C102","value": 100}]
},
{
"semester": "second",
"children":
[{
"name": "C103", "value": 50},
{"name": "C104","value": 100},
{"name": "C105","value": 100}]
},
{
"semester": "third",
"children":
[{"name": "C106","value": 50}]
}
}