具有子子类别的子类别的PHP类别

Question

I am trying to make a category tree system to display endless categories. The database is setup like:

ID PARENT_ID CATEGORY_NAME

PHP Code:

$cat_array = array();
$subcat_array = array(); 
if (mysqli_connect_errno())
          {
          echo "Failed to connect to MySQL: " . mysqli_connect_error();
          }         

$sql = mysqli_query($con, "SELECT * FROM `documents_category` WHERE ISNULL(parent_id)    ");
while($row = mysqli_fetch_array($sql))
{      $cat_array[] = $row;  
//echo $row['category_name'];
}  

// print_r($cat_array);


 $sql2 = mysqli_query($con, "SELECT * FROM `documents_category`
 WHERE parent_id IS NOT NULL ");
 while($row2 = mysqli_fetch_array($sql2))
 {      $subcat_array[] = $row2;  
 }  

 foreach ($cat_array as $value)

 {
    echo "{$value['category_name']}<br/>";  

    foreach ($subcat_array as $value2)

    {
            if($value2['parent_id'] == $value['id'])

            {
            echo "{$value2['category_name']}<br/>";
            }


    }

}

The sub categories would just use the parents id. I can get this working upto to one parent and a sub but after than nothing shows up. I have tried this a few different ways but no luck. Any advice?

Answer 1

You need to use recursion. Normally I use PDO but I tried to make a working example:

<?php
$cat_array = array();
$subcat_array = array(); 
if (mysqli_connect_errno())
{
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
}         

function GetCategories($parent_id, $counter = 0)
{

    $sql = mysqli_query($con, "SELECT * FROM `documents_category` WHERE parent_id = " . (int)$parent_id);
    while($row = mysqli_fetch_array($sql))
    {
        echo str_repeat("&nbsp;", $counter) . $row["id"] . " - " . $row["parent_id"] . " - " . $row["category_name"];
        $sub_category = mysqli_query($con, "SELECT * FROM `documents_category` WHERE parent_id = " . (int)$row["id"]);
        $counter++;
        if(count($sub_category) > 0)
            GetCategories($row["id"], $counter);
    }
}

$parent_categories = GetCategories(0);
?>

As you see, I select the "root" by calling the function. It will echo the category. Then it checks if it has sub categories on the current id, if found then call the function again but now with parent_id of the current row. if not found, it will go further... You can use a counter for adding spaces or something....