递归数据库查询以构建ul列表

Question

I have a database table I have to build a UL list from

the table is setup like cat-id parent-id (is the cat-id of whatever the parent is) cat-name cat-image

I can get the root cat easily which has a parent-id of "0" but for the life of me I can't figure out how to do it so the end result is

rootcat --childParent ---child

function cats(){
        $sql = "SELECT * FROM `cats";
        $cats;
        $con = mysqli_connect($this->vars["host"], $this->vars["user"], $this->vars["pass"], $this->vars["db"]);
        if(mysqli_connect_errno()){
            print "START MYSQLI ERROR<br/>".
            mysqli_connect_error() .
            "<br/>ENDMYSQLI ERROR";
        }else{
            $res = mysqli_query($con, $sql);
            if(mysqli_num_rows($res) > 0){
                while($cat = $res->fetch_array(MYSQLI_ASSOC)){
                    if($cat["parent-id"] == "0"){
                        $cats[] = $cat["cat-name"];
                    }
                }
            }
            mysqli_free_result($res);
            mysqli_close($con);
            return $cats;
        }

Answer 1

You can parse a sub category like this, but it does requirer to make a sql connection every time you check what categories does parent has:

function cats(){
    $sql = "SELECT * FROM `cats";
    $cats;
    $con = mysqli_connect($this->vars["host"], $this->vars["user"], $this->vars["pass"], $this->vars["db"]);
    if(mysqli_connect_errno()){
        print "START MYSQLI ERROR<br/>".
        mysqli_connect_error() .
        "<br/>ENDMYSQLI ERROR";
    }else{
        $res = mysqli_query($con, $sql);
        if(mysqli_num_rows($res) > 0){
            while($cat = $res->fetch_array(MYSQLI_ASSOC)){
                if($cat["parent-id"] == "0"){
                    $cats[$key] = $cat["cat-name"];
                    $sql_subCategory = "SELECT * FROM cats where parent_id=". $cat["id"];
                            $res_sub = mysqli_query($con, $sql_subCategory);
                            if(mysqli_num_rows($res_sub) > 0){
                                while($cat_sub = $res_sub->fetch_array(MYSQLI_ASSOC)){
                                   $cats[$cat["cat-name"]] = $cat_sub["cat-name"];
                                }
                            }
                        }
                    }
                }
                mysqli_free_result($res_sub);
                mysqli_free_result($res);
                mysqli_close($con);
                return $cats;
            }

That should give you the array:

array(
    'cat1' => 'Some Category',
    'cat2' => array(
        'sub1' => 'name',
    ),
);