Looping through categories and build a category tree using the parent id

Question

I am trying to work with a database containing category information. The relation between the items is only present in form of a parent_id. I know I could do some "workarounds" to reach the goal, but there has to be a better solution. I could write some more info into the database, like deepness, but I am sure there is a loop/array_filter combination I am not aware about.

This is what I have:

Array
(
[0] => Array
    (
        [id] => 0
        [parent_id] => 0
        [title] => All
    )

[1] => Array
    (
        [id] => 23
        [parent_id] => 0
        [title] => Nature
    )

[2] => Array
    (
        [id] => 3
        [parent_id] => 4
        [title] => Thriller
    )

[3] => Array
    (
        [id] => 4
        [parent_id] => 0
        [title] => Drama
    )

[4] => Array
    (
        [id] => 1
        [parent_id] => 0
        [title] => Crime
    )

[5] => Array
    (
        [id] => 19
        [parent_id] => 23
        [title] => Documentation
    )

[6] => Array
    (
        [id] => 20
        [parent_id] => 23
        [title] => Coverage
    )

[7] => Array
    (
        [id] => 21
        [parent_id] => 19
        [title] => Isles
    )

This is what I want:

Array
(
[0] => Array
    (
        [id] => 0
        [parent_id] => 0
        [title] => All
        [0] => Array
            (
                [id] => 23
                [parent_id] => 0
                [title] => Nature
                [0] => Array
                        (
                            [id] => 19
                            [parent_id] => 23
                            [title] => Documentation
                            [0] => Array
                                    (
                                        [id] => 21
                                        [parent_id] => 19
                                        [title] => Isles
                                    )
                        )
                [1] => Array
                        (
                            [id] => 20
                            [parent_id] => 23
                            [title] => Coverages
                        )
                )

        [1] => Array
            (
                [id] => 4
                [parent_id] => 0
                [title] => Drama
                [0] => Array
                        (
                            [id] => 3
                            [parent_id] => 4
                            [title] => Thriller
                        )
            )

        [2] => Array
            (
                [id] => 1
                [parent_id] => 0
                [title] => Crime
            )
    )

I tried it with loops and array_filter() but it was not working. I tried it with a loop within a function and calling the function within the same loop, but it didn´t work. Then I started trying it with only 1 subcategory, but this can´t be a solution nor a workaround.

$categories = [see first code block]
$i = -1;
$j = -1;

foreach ($categories as $cat) {
    $i++;
    if ($cat['parent_id'] > 0) {
        $j = -1;
        foreach ($categories as $cat2) {
            $j++;
            if ($cat2['id'] == $cat['parent_id']) {
                $categories[$j][] = array("id" => $cat['id'], "parent_id" => $cat['parent_id'], "title" => $cat['title']);
                unset($categories[$i]);
                continue 2;
            }
        }
    }
}

I would be happy for every push in the right direction. Thanks!

It seems a duplicate of [that question](http://stackoverflow.com/questions/10646833/using-mysql-query-to-traverse-rows-to-make-a-recursive-tree). — , Aug 11 '15 at 15:39
thx! This redirects to this: http://dba.stackexchange.com/questions/7147/find-highest-level-of-a-hierarchical-field-with-vs-without-ctes/7161#7161 which is very interesting. When I have a solution and a working code, I will post it here. — Seba, Aug 11 '15 at 15:51

score 0 · Answer 1 · edited May 23 '17 at 12:29

0

I found a working soltuion in this answer: https://stackoverflow.com/a/20286566/5215634

It is positive to mention that only one database query is done and the result set is processed in tree(). If my code is ready I will post it here.

edited May 23 '17 at 12:29

Community

1
1

answered Aug 11 '15 at 16:44

Seba

1
1

Looping through categories and build a category tree using the parent id

1 Answers1