Lets say I have a table that has a record of levels that represents a tree heirarchy
id group parent_group_id
--------- ---------- ---------------
1 Parent 1 NULL
2 Parent 2 NULL
3 Parent 3 NULL
4 Child 1 1
5 Child 2 2
6 Child 3 2
7 Child 4 6
I need to build a recursive function to build a multidimensional nested array so that it starts out at the "top" by first building the top level arrays of rows that have parent_group_ids of NULL. Fastforward several iterations, im expecting to end up with an object like so
$result = array(
[0] => array(
'id' => 1,
'group' => 'Parent 1',
'parent_group_id' => NULL,
'children' => array(
[0] => array(
'id' => 4,
'group' => 'Child 1'
'parent_group_id' => 1,
'children' => NULL)),
[1] => array(
'id' => 2,
'group' => 'Parent 2',
'parent_group_id' => NULL,
'children' => array(
[0] => array(
'id' => 5,
'group' => 'Child 2'
'parent_group_id' => 2,
'children' => NULL),
[1] => array(
'id' => 6,
'group' => 'Child 3'
'parent_group_id' => 2,
'children' => array(
[0] => array(
'id' => 1,
'group' => 'Child 4'
'parent_group_id' => 6,
'children' => NULL)))
What is the best way to build something like this? I need to ensure that it traverses down each "branch". I'm guessing when it gets the id's of the top level parents, it then proceeds to check to see if any rows exist that have a parent_group_id that equals each of the id's from the first run. Then if it finds children, gets the id's of those children and then checks again to see if children exist. And so on and so forth until it runs of out id's to check against.
I am not well-versed with foreach loops to pull off something like this.