php - Load hierarchical data from database into array -

i have table in database following structure/data:

n_id      n_parent_id      ... other fields ... ====      ===========      =========================  1         null            ...  2         null            ... ... 11            1            ... 12            1            ... ... 25            2            ... ... 65           11            ... 66           11            ... ... 

this table stores hierarchical data, can seen sample above. need load php array in tree-like fasion, array contain this:

array (     [1] => array         (             [n_id] => 1             [n_parent_id] =>              [other_data] => ...             [children] => array                 (                     [11] => array                         (                             [n_id] => 11                             [n_parent_id] => 1                             [other_data] => ...                             [children] => array                                  (                                     [65] => array                                         (                                             [n_id] => 65                                             [n_parent_id] => 11                                             [other_data] => ...                                         )                                  )    ... , on ... ) 

i can deal 1 level:

//ordering ensure parent row read before children rows //my data set in way. $query = "select n_id, n_parent_id, other_data hierarchy_table order n_parent_id, n_id"; if(($dbs = $dbh->query($query)) === false) {     $e = $dbh->errorinfo();     // ... deal error } $result = array(); while($row = $dbs->fetch(pdo::fetch_assoc)) {     if(is_null($row['n_parent_id'])) {         $result[$row['n_id']] = array(             'n_id' => $row['n_id'],             'n_parent_id' => null,             'other_data' => ...,             'children' => array()         );     }     elseif(isset($result[$row['n_parent_id']])) {         $result[$row['n_parent_id']]['children'][$row['n_id']] = array(             'n_id' => $row['n_id'],             'n_parent_id' => $row['n_parent_id'],             'other_data' => ...             children => array()         );     } } 

however can't seem head around extending multiple levels without having loop recursively on whole array every time need add row. naturally, had been java or c, store pointers data structures , solve issue, in php isn't easy. @ end of all, need send json_encode of client.

this question covers similar issue, don't have actual hierarchical information in database - parent id's.

any on appreciated.

edit: database table contains hundreds of thousands of rows, therefore performance important.

after struggling managed need using 1 pass on recordset (only reading each record once) - using references. memory reference support rather limited in php, there funny things required keep thins working (e.g. new variable name each row i'm reading db). anyway, here's code ended (this code deals id , parent_id - it's trivial read/store further data):

$dbh = new pdo(connect_string, username, password); $dbs = $dbh->query("select n_id, n_parent_id test_table order n_parent_id, n_id"); $elems = array();  while(($row = $dbs->fetch(pdo::fetch_assoc)) !== false) {     $row['children'] = array();     $vn = "row" . $row['n_id'];     ${$vn} = $row;     if(!is_null($row['n_parent_id'])) {         $vp = "parent" . $row['n_parent_id'];         if(isset($data[$row['n_parent_id']])) {             ${$vp} = $data[$row['n_parent_id']];         }         else {             ${$vp} = array('n_id' => $row['n_parent_id'], 'n_parent_id' => null, 'children' => array());             $data[$row['n_parent_id']] = &${$vp};         }         ${$vp}['children'][] = &${$vn};         $data[$row['n_parent_id']] = ${$vp};     }     $data[$row['n_id']] = &${$vn}; } $dbs->closecursor();  $result = array_filter($data, function($elem) { return is_null($elem['n_parent_id']); }); print_r($result); 

when executed on data:

mysql> select * test_table; +------+-------------+ | n_id | n_parent_id | +------+-------------+ |    1 |        null | |    2 |        null | |    3 |           1 | |    4 |           1 | |    5 |           2 | |    6 |           2 | |    7 |           5 | |    8 |           5 | +------+-------------+ 

the last print_r produces output:

array (     [1] => array         (             [n_id] => 1             [n_parent_id] =>              [children] => array                 (                     [3] => array                         (                             [n_id] => 3                             [n_parent_id] => 1                             [children] => array                                 (                                 )                          )                      [4] => array                         (                             [n_id] => 4                             [n_parent_id] => 1                             [children] => array                                 (                                 )                          )                  )          )      [2] => array         (             [n_id] => 2             [n_parent_id] =>              [children] => array                 (                     [5] => array                         (                             [n_id] => 5                             [n_parent_id] => 2                             [children] => array                                 (                                     [7] => array                                         (                                             [n_id] => 7                                             [n_parent_id] => 5                                             [children] => array                                                 (                                                 )                                          )                                      [8] => array                                         (                                             [n_id] => 8                                             [n_parent_id] => 5                                             [children] => array                                                 (                                                 )                                          )                                  )                          )                      [6] => array                         (                             [n_id] => 6                             [n_parent_id] => 2                             [children] => array                                 (                                 )                          )                  )          )  ) 

which looking for.