handle parent/child categories in mysql/php

Question

I have this mysql table "categories":

id  category        parent
1   category1       NULL
2   subcategory1    1
3   category2       NULL
4   subcategory2    2

and I want to get this result:

category1
    subcategory1
        subcategory2
category2

to get this result I use this code, but is very slow:

foreach($db->query("SELECT * FROM categories WHERE parent IS NULL") as $parent)
{
    $t=0;
    categories($db,$t,$parent['id'],$parent['category']);
}

function categories($db,$t,$id,$category)
{
    echo"<option value=".$id.">";//
    for($i=0;$i<$t;$i++) {echo"&nbsp;&nbsp;&nbsp;"; $GLOBALS['cat'].="&nbsp;&nbsp;&nbsp;";}
    echo $category."</option>";//" ".$id.
    $GLOBALS['cat'].=$category."<br>";
    $t++;
    if($db->query("SELECT * FROM categories WHERE parent=".$id)->rowCount()>0)
    {
        foreach($db->query("SELECT * FROM categories WHERE parent=".$id) as $child)
        categories($db,$t,$child['id'],$child['category']);
    }
}

Do you have a faster solution?
Thanks

You should take a look at `nested sets`. Then you can avoid having to do dozens of queries to build your tree... It's too much info to share here... — Raphioly-San, Feb 14 '17 at 21:32
Possible duplicate of [php / Mysql best tree structure](https://stackoverflow.com/questions/5916482/php-mysql-best-tree-structure) — Mike Doe, May 21 '18 at 08:11

score 0 · Answer 1 · answered Feb 14 '17 at 22:54

If you know the depth of your tree (maximum or desired), you can have it all in SQL, flattened to a row representing path:

SELECT c.category, c1.category AS sub1, c2.category AS sub2, c3.category AS sub3
FROM categories c
    LEFT JOIN 
        (categories c1 
             LEFT JOIN
                 (categories c2
                      LEFT JOIN categories c3 
                      ON c3.parent = c2.category)
                 ON c2.parent = c1.category)
         ON c1.parent = c.category
WHERE c.parent IS NULL;

Having that, it's not a big deal to present it in UI accordingly.

score -1 · Answer 2 · answered May 21 '18 at 08:10

$sql = "SELECT id, name, parent_id,
    (SELECT COUNT(*) FROM categories WHERE parent_id = cat.id) as child_count
    FROM categories cat";

$result = db_select_all($sql);

function build_menu($pid, $level = 1) {
    global $result;
    $filtered = array_filter($result, function($elem) use($pid) {
        return $elem['parent_id'] == $pid;
    });

    if(empty($filtered)) return;

    foreach($filtered as $item) {
        extract($item);
        echo "<div>" . str_repeat("---", $level) .  $name;
        build_menu($id, $level + 1);
        echo "</div>" . PHP_EOL;
    }
}



$menu_elements = array_filter($result, function($item) {
    return $item['parent_id'] == '';
});


foreach($menu_elements as $menu) {
    $name = $menu['name'];
    $id = $menu['id'];
    echo "<div>" . $name;
    build_menu($id);
    echo "</div>" . PHP_EOL;
}

handle parent/child categories in mysql/php

2 Answers2