Multidimensional array how group theme

Question

Hello everyone I hope you all having a great weekend... I have the following problem I have spend almost tow days trying to figure it out... I have an SQL query to pull data from database, the result is a Multidimensional array as stdClass Object... which looks like this

Array
(
    [0] => stdClass Object
        (
            [category_id] => 81
            [category_name] => 369-1006-0
            [published] => 1
            [category_parent_id] => 8
            [category_child_id] => 81
        )

    [1] => stdClass Object
        (
            [category_id] => 80
            [category_name] => 369-1007-0
            [published] => 1
            [category_parent_id] => 8
            [category_child_id] => 80
        )

    [2] => stdClass Object
        (
            [category_id] => 82
            [category_name] => 369-1012-0
            [published] => 1
            [category_parent_id] => 8
            [category_child_id] => 82
        )

    [3] => stdClass Object
        (
            [category_id] => 85
            [category_name] => 369-1014-0
            [published] => 1
            [category_parent_id] => 8
            [category_child_id] => 85
        )
[........]
);

until this point I can simple use print_r() to dispatch the information as per request by the API... or echo's while's foreach's .... to display that information for easy reading... but the problem is that I can't group them by ID's ... please allow me...

This array are categories and each category as you have guess it they have sub-categories but the categories and sub-categories are associated using a second table so... [Table 1] has all the information about the categories, and [Table 2] has the association, my query join them together so that I can get a single "virtual table"... and the result I get is that multidimensional nightmare.... the idea is to group them in categories with their sub-categories, now here is the catch ...

as you can see in that array I have [category_parent_id] in each group all of them have a value as 1,2,3,4,5... and this values repeat them self per group... as you can see in this little example... so, every [category_parent_id] with the value of 0 is the main category and the rest are sub-categories

This is how my virtual table Table looks like

[category_id] [category_name] [category_parent_id]
       1          item 1            0
       2          item 2            0
       3          item 3            1
       4          item 4            1
       5          item 5            2
       6          item 6            3
       7          item 7            2
       8          item 8            2

// This is the main idea to group them using the multidimensional array aka nightmare

Item 1
  |_item 3
  |_item 4
Item 2
  |_item 5
  |_item 7
  |_item 8

This are my useless attempts:

Attempt 1
    $cc = 0;

    foreach ($results as $obj_key => $cc){
    $cc++
        $catsname =  $obj->category_name;
        $catid = $obj->category_id;
        $subcatid = $obj->category_parent_id;

        echo $obj_key .' Name';
            foreach($cc as $key=>$value)
            {
                echo $key . $value . '<br> ';
            }
        echo '<br>';

    }

Attempt 2
$datasetCount = count($results);
 echo "<h2>How many ccats? $datasetCount </h2>";

$i = 0;
foreach ($results as $categorias) {
    $i++;
    echo "<h2>Categoria $i</h2>";


    while (list($key, $value) = each ($categorias)) {
        echo "$key: $value<br />";




    }

}

I have many more attempts but after my second day and multiple examples it seems that is not possible! which I refuse to believe...

Now, don't think I haven't try other solutions, I have, there is an easy way to do this without the array's I can always add another column to the Table 1 [cat_role] value [sub] : [main] that way I wont even use a second table... but there I am, asking my self, if I don't learn this technique what would happen when the rule is very clear tow or tree tables, one hold the data and the other hold the relations... I really really really need to learn this technique...

Any help would be greatly appreciated!. Thank you for taking the time to ready my mess...

Answer 1

Inspired by this, as reference @redreggae:

$tree = array();
$nodes = array();

foreach ($results as $category) {
    $id = $category->category_id;
    $category->children = array();
    $nodes[$id] = $category;

    if ($category->category_parent_id == 0) {
        $tree[$id] = & $nodes[$id];
    }

    foreach ($nodes as $id => & $node) {        
        if (isset($nodes[$node->category_parent_id])) {
            $nodes[$node->category_parent_id]->children[$id] = $node;
        }
    }
}

echo '<pre>'; print_r($tree);