Rewrite manually created nested JSON to use PHPs built in functions

Question

I have in my table records that are related one to another by a parentId field. When fetching them from the DB I need to create JSON array of objects where the child records are added to 'children' property of parent objects :

[{
    //main object
    children : [
        {
            //#1st level children object
            children: [
                {
                    //#2nd level children object
                }
            ]
        }
    ]
},
{
(...)
]

But if there are no records with parentId equal to current record, then this property shouldn't be added to the object.

Right now I'm building the JSON string manually :

//first get the parents
$q = 'SELECT * FROM table WHERE parentId = 0';
$r = mysql_query($q);
$x=1;
$nr = mysql_num_rows($r);
while($e = mysql_fetch_array($r)){
        if($x==1){
                echo '[ {       ';
        }
        else{
                echo ' { ';     
        }

        if($e['leaf']==1){
                $leaf = 'true'; 
        } else {
                $leaf = 'false';
        }

        echo '"EndDate" : "'.str_replace('T00:00:00','',$e['EndDate']).'",
                  "Id" : '.$e['Id'].',
                  "Name" : "'.$e['Name'].'",
                  "BaselineStartDate" : "'.str_replace('T00:00:00','',$e['BaselineStartDate']).'"';
        if($leaf){
                echo ',"leaf": '.$leaf.'';
        }

        childs($e['Id'], $x, $nr);
        if($x<$nr){
                echo ',';
        }       
        $x++;
}
if($x>1){
        echo ']';
}



function childs($id, $x, $nr, $x2='', $nr2=''){
        //now see if there are childern
        $q2 = 'SELECT * FROM table WHERE parentId = "'.$id.'" ';
        $r2 = mysql_query($q2);
        $nr2 = mysql_num_rows($r2);
        if($nr2>0){
                echo ',"children": [ '; 
                $x2 =1;
                while($e2 = mysql_fetch_array($r2)){

                        if($e2['leaf']==1){
                                $leaf2 = 'true';        
                        }
                        else{
                                $leaf2 = 'false';
                        }

                        echo '{
                              "EndDate" : "'.str_replace('T00:00:00','',$e2['EndDate']).'",
                                  "Id" : '.$e2['Id'].',
                                  "Name" : "'.$e2['Name'].'",
                                  "BaselineStartDate" : "'.str_replace('T00:00:00','',$e2['BaselineStartDate']).'",
                                  "leaf" : "'.$leaf2.'"';

                        childs($e2['Id'],$x,$nr,'',$x2,$nr2);
                        if($x2<$nr2){
                                echo ',';       
                        }
                        $x2++;

                }
                echo '] }';
        }
        else{
                echo ',"children" : []}';       
        }            
}

Is there an easy way to make this code more robust using some built-in PHP features like fetch_assoc or something like that?

[`json_encode()`](http://php.net/manual/en/function.json-encode.php). — PeeHaa, Jun 29 '12 at 10:10
but using json_encode I won't be able to get this nested stucture right ? — DevAno1, Jun 29 '12 at 10:15
yes but you're assuming that children are already added. If you'll look at the code, I'm doing a query and add elements to children array if the id/parentId of records match — DevAno1, Jun 29 '12 at 11:01
Yeah about that: why do you need multiple queries to get that data? — PeeHaa, Jun 29 '12 at 11:10

score 1 · Accepted Answer · edited May 23 '17 at 12:20

1

You should rather..

Fetch the results from the database
Reformat as per the requirement (related code you can use with some alteration PHP Create a Multidimensional Array from an array with relational data
Then finally, json_encode the resulting array

edited May 23 '17 at 12:20

Community

1
1

answered Jun 29 '12 at 10:19

FatalError

922
12
31

Rewrite manually created nested JSON to use PHPs built in functions

1 Answers1