If I understand you correctly, you want to build a nested array for your entire menu structure, which you can do by issuing one single query to retrieve the entire page table, and then build your nested array in one pass.
Example where the data is allready in an array
/* Menu test data. Corresponds to this structure
Main Music
Start Minimal
Contact Us Len Faki
Kraft Und Licht
BX3
Ben Klock
Singer Songwriter
Beth Orton
*/
$page = array(
array(
'id' => 1,
'name' => 'Main',
'parent' => -1
),
array(
'id' => 3,
'name' => 'Music',
'parent' => -1
),
array(
'id' => 2,
'name' => 'Start',
'parent' => 1
),
array(
'id' => 4,
'name' => 'Contact Us',
'parent' => 1
),
array(
'id' => 5,
'name' => 'Minimal',
'parent' => 3
),
array(
'id' => 6,
'name' => 'Singer Songwriter',
'parent' => 3
),
array(
'id' => 7,
'name' => 'Len Faki',
'parent' => 5
),
array(
'id' => 9,
'name' => 'Ben Klock',
'parent' => 5
),
array(
'id' => 8,
'name' => 'Beth Orton',
'parent' => 6
),
array(
'id' => 10,
'name' => 'Kraft Und Licht',
'parent' => 7
),
array(
'id' => 11,
'name' => 'BX3',
'parent' => 7
)
);
/* Function to put your data in a nested array
* Important!
* The input array MUST be ordered with ascending parent ids:
* SELECT * FROM page ORDER BY parent ASC
*/
function nestIt($arr, $id = 'id', $pid = 'parent')
{
$out = array();
$refs = array();
while (null !== ($current = array_shift($arr)))
{
# top level
if ($current[$pid] == -1)
{
# Put each top level element into the array
$out['children'][] = $current;
# Keep a reference to the above element in the non-nested array $refs
$refs[$current[$id]] = &$out['children'][count($out['children'])-1];
}
else
{
# Use the array of references to insert your current element
# into the proper place in the nested array.
# This works since the elements are ordered ascendingly on parent,
# so this elements parent has allready had it's reference put into $refs
$refs[$current[$pid]]['children'][] = $current;
# put a reference of this element into the reference array
$refs[$current[$id]] = &$refs[$current[$pid]]['children'][count($refs[$current[$pid]]['children'])-1];
}
}
return $out;
}
$nested = nestIt($page);
printf('<pre>%s</pre>', print_r($nested,1));
By keeping a non-nested array that contains references into the nested array for each element that has been inserted in the nested array, it is possible to instantly find where to put a new element by referring to the array of references by using the current elements parent id as key.
In reality, the input data doesn't have to be ordered by ascending parent ids, but it does need to have each parent element inserted before a child element is inserted. In the example data above, the code would still work if Beth Orton, parent 6 (Singer songwriter) came before Ben Klock & Len Faki, parent 5 (Minimal), as long as the Singer Songwriter element has allready been inserted.
However, ordering by ascending parent ids takes care of the problem and is easy to achieve.
Also note that since this function does the work in one pass, you don't need an array as input. You could work directly on a db query resource, e.g. by replacing
while (null !== ($current = array_shift($arr)))
with
while ($current = mysqli_fetch_assoc($query_result))
