up vote 1 down vote favorite
share [g+] share [fb]

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);
link|improve this question
65% accept rate
Some code would be helpful. – Evan Mulawski 14 hours ago
Added. Sorry for the hold up there. – Keiran Lovett 14 hours ago
feedback

2 Answers

up vote 1 down vote accepted

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 
}
link|improve this answer
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 14 hours ago
1  
@KeiranLovett I updated my answer to reflect your comment – brenjt 14 hours ago
Thanks a bunch, appreciate your help! – Keiran Lovett 14 hours ago
I'm getting this error for some reason, any clue as to what it could be? object(stdClass)#18 (1) { ["C"]=> &NULL } – Keiran Lovett 14 hours ago
@KeiranLovett Not sure, can you give me more information? – brenjt 14 hours ago
show 3 more comments
feedback
up vote 0 down vote

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;
    }
}
link|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.