Create Multidimentional json object from database

Question

i'm having some troubles creating a multidimentional JSON object in PHP.

My Postgresql database looks like this:

Id        Name        Parent
1         Active      NULL
2         Passive     NULL
3         Fixed       1
4         Dynamic     3
5         Taxes       2
6         Fair        2
...

The parent column is linked with the Id in the first column

What i want to accomplish is this:

[
    {
     "name": "Active",
     "children": [
       {
         "name": "Fixed",
         "children": [
           {
             "name": "Dynamic",
             "children": NULL
           }
         ]
       }
     ]
   },
    {
     "name": "Passive",
     "children": [
       {
         "name": "Taxes",
         "children": NULL
       },
       {
         "name": "Fair",
         "children": NULL
       }
     ]
   }
]

So first of all i FETCH the data out of our database with

$result = fetchAll(PDO::FETCH_OBJ); // fetches every row in an object

i could send this result to the frontend (javascript) and convert this data to JSON there but then i send the column names with it and i don't think that is a good idea in security terms.

First of all i want to make the top level of the JSON file. With the help of this topic Object to array in PHP i manage to put that together:

$mainArray = [];
foreach ($result as $value) {
    if ($value['Parent'] === NULL) {
        $object = new stdClass();
        $object->name = $value['Name'];
        $object->children = [];
        $mainArray[] = $object;

    }
}

This is my result:

[
   {
      name: "Actief",
      children: [ ]
   },
   {
      name: "Passief",
      children: [ ]
   }
]

But i'm stuck adding children to the correct parent. I just can't seem to find how to do it.

I need to do something like this:

Add Fixed to Object where Object->Name is 1 = Active.

score 1 · Accepted Answer · edited May 23 '17 at 11:59

This will do the job. Assuming your child data won't come before your parent data.

$mainArray = [];
foreach ($result as $key=>$value) {
      if ($value['Parent'] === NULL) {
        $object = new stdClass();
        $object->name = $value['Name'];          
        $mainArray[$value['Id']] = $object;    
      }else{
         $childOBJ = new stdClass();
         $childOBJ->name = $value['Name'];
         $mainArray[$value['Parent']]->children[] = $childOBJ; 

       }

    }
 $finalArray = array_values($mainArray); // changes to indexed array for json purpose

UPDATE: WITH RECURSION and refrence

function makeTree($result, $parentId=NULL){
   $tree = [];
   foreach ($result as $value) {
      if($value['Parent'] == $parentId){
        $object = new stdClass();
        $object->name = $value['Name'];  
        $child = makeTree($result, $value['Id']);
          if($child){
            $object->children=$child;                          
        }
       $tree[] = $object;
   }               
  }     
  return $tree;              
}
$finalArray = makeTree($result);

Thx! It works for 75% because as you can see in my example some childs can have childs to and these can have childs to and ... You know where i want to go it can go x times deep. Now it gives "All" the childs at the same level. — Peter, Mar 14 '16 at 19:47

Create Multidimentional json object from database

1 Answers1