Nested JSON array from PHP

[hawkeye15]

 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}]

     }

    }

 

[Ch0cu3r]

Try

$info = array();        
while ($row = $result->fetch_array(MYSQLI_ASSOC))
{
    if(!isset($info[$row['semester']]))
    {
        $info[$row['semester']] = array(
            'semester' => $row['semester'], 
            'children' => array()
        );
    }

    $info[$row['semester']]['children'][] = array(
        'name'  => $row['name'],
        'value' => $row['value']    
    );
}

$data = json_encode(array('id' => $row['sbuid'], 'children' => array_values($info)));
echo $data;

Edited September 14, 2015 by Ch0cu3r