Creating an array using recursive php from mysql

Question

I need to create an array from a mysql database organized like so

id description parentId 
1 Level 1 0 
2 Level 2 0 
3 Level 1a 1 
4 Level 1b 1 
5 Level 1a1 3
6 Level 1a1a 5

So that the output is like this:

Level 1
 Level 1a
 Level 1a1
 Level 1a1a
 Level 1b
Level 2

However my current code only outputs to the second level and then makes every other child it's own parent. Below is the current code:

$query = "SELECT * FROM pB_test ORDER BY parentId ASC";
$result = mysql_query($query) or die ('Database Error (' . mysql_errno() . ') ' . mysql_error());
$tree = array();
while($row = mysql_fetch_assoc($result)) 
{
 if($row['parentId'] == 0) 
 {
 $row['Children'] = array();
 $tree[$row['id']] = array(
 'id' => $row['id'], 
 'description' => $row['description'], 
 'parentId' => $row['parentId']
 );
 } 
 else 
 {
 $tree[$row['parentId']]['Children'][$row['id']] = $row['description'];
 }
}
$count = array_keys($tree);
foreach ($count as $array)
{
 ksort($tree[$array]['Children']);
}
echo print_r($tree, true);

Any help or nudge in the right direction would be great. Cheers

Update: Working Code

 $results = array();
while($row=mysql_fetch_assoc($dbresult)) 
{ 
 $results[]=$row;
 $tree = null;
foreach($results as $result)
{
 $thisref = &$refs->{$result['id']};
 foreach($result as $k => $v)
 {
 $thisref->{$k} = $v;
 }
 if ($result['parentId'] == 0) {
 $tree->{$result['id']} = &$thisref;
 } else {
 $refs->{$result['parentId']}->children->{$result['id']} = &$thisref;
 }
}
$tree; // contains the newly sorted tree.
}
print_r($tree);

• Keiran Lovett: Added. Sorry for the hold up there.

Answer 1

I found this code for grouping parent child arrays to be amazing. I have tested in 4 depths with no issue what so ever. It isn't a recursive function though.

$tree = null;
foreach($results as $result)
{
 $thisref = &$refs->{$result['id']};
 foreach($result as $k => $v)
 {
 $thisref->{$k} = $v;
 }
 if ($result['parentId'] == 0) {
 $tree->{$result['id']} = &$thisref;
 } else {
 $refs->{$result['parentId']}->children->{$result['id']} = &$thisref;
 }
}
$tree; // contains the newly sorted tree.

You may have to do some modification for it to fully work with your situation. But basically it loops through all the results and combines them by reference.

Do note that the ending $tree data type is an object and not an array

Good Luck

UPDATE

You can create the array as such

$query = "SELECT * FROM pB_test ORDER BY parentId ASC";
$dbresult = mysql_query($query) or die ('Database Error (' . mysql_errno() . ') ' . mysql_error());
$results = array();
while($row=mysql_fetch_assoc($dbresult)) 
{ 
 $results[]=$row 
}

• Keiran Lovett: I'm going to sound completely idiotic here but how do I load the mysql into this. Arrays and foreach() are not my thing and my brain is completely melted right now.
• Keiran Lovett: Thanks a bunch, appreciate your help!
• Keiran Lovett: I'm getting this error for some reason, any clue as to what it could be? object(stdClass)#18 (1) { ["C"]=> &NULL }
• Keiran Lovett: I'm not getting an error report at all, just the above snippet on the screen. I'll post my updated code on the question for reference
• brenjt: @KeiranLovett try putting my update code above the code I posted originally. Rather than combining them

Answer 2

It would probably be easiest if you created an array that mapped from id to object as you create your objects so you can easily lookup nested objects. Basically:

$tree = array();
$lookup = array();
while($row = mysql_fetch_assoc($result)) 
{
 $object = array('id' => $row['id'], 
 'description' => $row['description'], 
 'parentId' => $row['parentId']);
 $lookup[$row['id']] = $object;
 $parentId = $row['parentId'];
 if ($parentId == 0)
 {
 $tree[] = $object;
 }
 else
 {
 $lookup[$parentId]['Children'][] = $object;
 }
}