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

Answer 2

The method you're using in storing your data is called Adjacency List model. To be able to achieve what you require. Follow these steps.

1) Retrieve the parent elements and save them to an array / hash.

2) Iterate through the parent array and retrieve child elements using the id of the parent. Save the result to an array and append as element of the current parent array using "children" as key.

3) JSON encode the resulting array.

<?php
    $sql    = "SELECT * FROM yourtable WHERE PARENT is NULL or PARENT = 0";
    $result = $db->query($sql);  //a valid MySQL database adapter with a 
                                 //"query" method which returns the full result set.
    $arr = array();
    foreach($result as $row) {
       $sql = "SELECT * FROM yourtable WHERE PARENT = {$row['id']}";
       $result2 = $db->query($sql);
       $row["children"] = $result2;
       $arr[] = $row;
    }
    echo json_encode($arr);
?>

For more info about retrieving data hierarchy on these type of table, read Rum's post on Retrieving Data Hierarchies on a SQL Table.

Also, take precaution in this implementation. Although it looks easy to implement, watch out for the number of iterations involving external resource calls, in this case your database server. Iteratively calling queries beats the crap out of it causing performance problems in the future. If that is the case, you can apply a technique similar to Kendall Hopkins (though I'm not sure why he used by-ref call on $row). More info about iterative external resource calls here.

<?php
$sql = "SELECT * FROM yourtable";
$result = $db->query($sql);
$arr = array();
//re-index the result array based on their actual IDs
foreach ($result as $row) {
    $arr[$row['ID']] = $row;
}
foreach ($arr as $item) {
    if (!empty($item["PARENT"]) && $item["PARENT"] != 0) {
       $arr[$item["PARENT"]]["children"][] = $item;
       //unset the discovered child item to clean-up array and release memory allocation 
       unset($arr[$item["ID"]]);
    }
}
echo json_encode($arr);
?>