How to get an hierarchical php structure from a db table, in php array, or JSON

Question

Possible Duplicate:
Turn database result into array

Hi guys, can you please help me. How to get an hierarchical php structure from a db table, in php array, or JSON, but with the following format:

[
  {
     "attributes":{
        "id":"111"
     },
     "data":"Some node title",
     "children":[
        {
           "attributes":{
              "id":"555"
           },
           "data":"A sub node title here"
        }
     ],
     "state":"open"
  },
  {
     "attributes":{
        "id":"222"
     },
     "data":"Other main node",
     "children":[
        {
           "attributes":{
              "id":"666"
           },
           "data":"Another sub node"
        }
     ],
     "state":"open"
  }
]

My SQL table contains the fields: ID, PARENT, ORDER, TITLE

Can you please help me with this? I'm going crazy trying to get this.

Many thanks in advance. Daniel

Answer 1

Two pass foreach does the trick. This will link all child to their parents recursively.

$structure = array();
foreach( $array as $row ) { //add rows to array by id
    $structure[ $row["id"] ] = $row + array( "children" => array() );
}
foreach( $structure as &$row ) { //link children to parents
    if( ! is_null( $row["parent"] ) ) {
        $structure[ $row["parent"] ]["children"][] =& $row;    
    }
}