PHP Category Loop with multiple levels-CodePudding

I am having a difficult time displaying a list of categories which may or may not be the last child in the category list. Please see my current code below.

 <?
 $sql = "SELECT * FROM categories WHERE cat_parent_id = '0' ORDER BY cat_name ASC;";
 $stmt = mysqli_stmt_init($conn);
        
if(!mysqli_stmt_prepare($stmt, $sql)){
  // DISPLAY ERROR                                      
 }
                                
mysqli_stmt_execute($stmt);
$resultData = mysqli_stmt_get_result($stmt);
while($row = mysqli_fetch_assoc($resultData)){
                                        
    $cat_id = $row['cat_id'];
    $cat_name = $row['cat_name'];
    
?>
    <h3><? echo $cat_name; ?></h3>
    
<?
   $sub_cat_list = retCatList($conn, $cat_id);
   while($SubCat = mysqli_fetch_assoc($sub_cat_list)){
                                            
         $cat_id = $SubCat['cat_id'];
         $cat_name = $SubCat['cat_name'];
                                            
?>
    <h3 style="text-indent: 20px;"><? echo $cat_name; ?></h3>
<?
            }
  }
?>

The above is only example code, the function is almost exactly like the first statement, although takes the ID and searches for any child categories. This works fine for displaying top level categories and their children, but what I want to do is display the children which might be added to the top level categories children.

So from this:

PARENT 1
   CHILD 1.1
   CHILD 1.2
PARENT 2 
   CHILD 2.1
   CHILD 2.2

To this:

 PARENT 1
     CHILD 1.1
       SECOND CHILD 1.1.1
       SECOND CHILD 1.1.2
     CHILD 1.2
   PARENT 2
      CHILD 2.1
        SECOND CHILD 2.1.1

The table uses the ID from Cat_id and adds it to a Cat_parent_id for their children. I hope this makes sense, I am trying to figure out what kind of loop to run or how to add the ID in to that loop until there are no children left and how to find out how many times to run it?

Thanks

CodePudding user response：

I haven't been able to fully test this with a database but this should hopefully give you a start for what you're looking for.

<?php

function outputCategory($parent_id, $indent = 0){
    global $conn;

    $sql = "SELECT * FROM `categories` WHERE `cat_parent_id` = ? ORDER BY `cat_name` ASC;";

    $stmt = mysqli_stmt_init($conn);
    // bind integer value $parent_id
    mysqli_stmt_bind_param($stmt, "i", $parent_id);
            
    if(!mysqli_stmt_prepare($stmt, $sql)){
        // error
        return;
    }
                                    
    mysqli_stmt_execute($stmt);
    $resultData = mysqli_stmt_get_result($stmt);

    if(mysqli_stmt_num_rows($stmt) === 0){
        // no categories with this parent
        return;
    }

    while($row = mysqli_fetch_assoc($resultData)){
        $cat_id = $row["cat_id"];
        $cat_name = $row["cat_name"];

        echo "<h3 style=\"text-indent: {$indent}px\">{$cat_name}</h3>";

        // recursion
        outputCategory($cat_id, $indent   20);
    }
}

// initial case - parent_id = 0
outputCategory(0);

NOTE: you could also add a $conn parameter if you want to pass the connection through instead of using global $conn;