Indented HTML <select> dropdown with hierarchical adjacency model MySql and PHP

Question

My need is a very common select dropdown that displays hierarchical items where all child items are indented by adding   I have gotten up to populating a dropdown in php with the right hierarchical order, but can't get it to indent. How do I make the dropdown indent properly using the space trick, when items are showing in the correct order (children under the right parents), just not indented?

In Mysql I have a table with columns Category_ID Item_Name, and Parent_ID.

In php, using a query I fetch a self-referencing table that has columns Category and Parent

In php, using the fetched table of categories and immediate Parent Category, I call a function parseTree(); which takes the sql result array, and returns an array of the full tree structure.

In php I call a function printTree(); in between <select> tags which recursively loops through the tree array and echos each node with <option> tags.

Within printTree() is a function printChildren() whose purpose is to add indents to all children arrays if found. This does not work.

Desired Result:

<select>
 <option>Root</option>
 <option>Root</option>
  <option>&nbsp;Child Level 1</option>
   <option>&nbsp;&nbsp;Child Level 2</option>
     <option>&nbsp;&nbsp;&nbsp;Child Level 3</option>
 </select>

etc....

PHP

<?php

$sql = "SELECT 
        e.cat_Name AS 'Category',
        m.cat_Name AS 'Parent Category'
        FROM
        categories_tbl e
            lEFT JOIN
        categories_tbl m ON m.cat_ID = e.parent_ID";
$result = mysqli_query($db, $sql);


function parseTree($tree, $root = "")
{
    $return = array();
    # Traverse the tree and search for direct children of the root
    foreach($tree as $child => $parent) {
        # A direct child is found
        if($parent == $root) {
            # Remove item from tree (we don't need to traverse this again)
            unset($tree[$child]);
            # Append the child into result array and parse its children
            $return[] = array(
                'name'     => $child,
                'children' => parseTree($tree, $child)
            );
        }
    }
    return empty($return) ? NULL : $return;
}

function printTree($tree)
{
    $indent = "";
    function printChildren($childrenarray)
    {
        $indent .= "&nbsp;&nbsp;";
        if(!is_null($childrenarray) && count($childrenarray) > 0) {

            foreach($childrenarray as $node) {

                echo '<option>' . $indent . $node['name'] . '</option>';
                printChildren($node['children']);

            }
        }
    }

    if(!is_null($tree) && count($tree) > 0) {

        foreach($tree as $node) {

            echo '<option>' . $indentpaddingval . $node['name'] . '</option>';


            if(!is_null($node['children']) && count($node['children']) > 0) {

                printChildren($node['children']);
            }

        }

    }

}

?>

HTML/PHP TO EXECUTE FUNCTIONS AND POPULATE SELECT

<select class="form-control">
    <?php
    $cat_tree_arr = array();

    while ($row = mysqli_fetch_assoc($result)) {



        $cat_tree_arr[$row['Category']] = $row['Parent Category'];


    }
    $result = parseTree($cat_tree_arr);

    printTree($result);

    ?>

</select>

I'm adding the parsed Array containing the entire tree of categories in Parent/Children arrays here:

Array
(
    [0] => Array
        (
            [name] => All Raw Materials
            [children] => Array
                (
                    [0] => Array
                        (
                            [name] => Bag Raw Materials
                            [children] => Array
                                (
                                    [0] => Array
                                        (
                                            [name] => LDPE Materials
                                            [children] => 
                                        )

                                )

                        )

                )

        )

    [1] => Array
        (
            [name] => All Finished Goods
            [children] => Array
                (
                    [0] => Array
                        (
                            [name] => Local Finished Goods
                            [children] => Array
                                (
                                    [0] => Array
                                        (
                                            [name] => Local Bags
                                            [children] => 
                                        )

                                )

                        )

                    [1] => Array
                        (
                            [name] => Export Finished Goods
                            [children] => Array
                                (
                                    [0] => Array
                                        (
                                            [name] => Export Bags
                                            [children] => 
                                        )

                                )

                        )

                )

        )

)

Answer 1

As per my understand, if categoryid is differentiate the all products then my answer will work for you.

There is no need to left join a same table again.

$sql = 'select cat_ID from categories_tbl';
$result = mysqli_query($db, $sql);
echo '<select class="form-control">';
while ($row = mysqli_fetch_assoc($result)){
    $sqlcat_name  = "select cat_Name from categories_tbl where parent_ID="."'".$row['cat_ID']."'"; 
    // echo this query to check that properly formed
    $Secondresult = mysqli_query($db, $sqlcat_name);
    $num_rows     = mysql_num_rows($Secondresult);
    $Secondrow    = mysqli_fetch_assoc($Secondresult)
    for($a=0;$a<$num_rows;$a++){
        echo'<option>';
        for($b=0;$b<$a;$b++){
            echo"   ";
        }
        echo "$Secondrow[$a]"."</option>";
    }
}
echo '</select>';

Answer 2

The code that worked for me, and my question above ended up being as follows. I simply needed to count the number of times my recursive function to populate the select was called, and every time the function entered a new level of categories, I could use the count value to repeat a '&nbsp' string as needed, adding more for each level that was recursed.

This aligned the nodes in each category in the select correctly, so users can see which sub-categories came under which parent category.

I used a static variable for this, but intend to replace it with a better method.

I'm posting a portion of my code that creates the select dropdown, in case anyone will find it useful.

             function printTree($tree) {


                        function printChildren($childrenarray) {
                               static $level = 1;
                            if(!is_null($childrenarray) && count($childrenarray) > 0){
                               $level++; 
                                        foreach($childrenarray as $node) {

                                            echo '<option>'.str_repeat('&nbsp;', $level).$node['name'].'</option>';

                                                printChildren($node['children']);


                                        }

                                --$level;

                            }
                        }

                    if(!is_null($tree) && count($tree) > 0){

                        foreach($tree as $node) {
                            echo '<option bg-danger>'.$node['name'].'</option>';

                                if (!is_null($node['children']) && count($node['children']) > 0 ) {

                                    printChildren($node['children']);
                                }

                        }
                    }

             }