Building a tree with foreach loops

[budimir]

I'm trying to build a tree to display product groups, but I can't achieve what I want.

There is a hierarchy for the tree which I need to follow:

1H
  1HS
    1HSMP

I want to list all the categories (1H) with its subcategories(1HS), and it's subcategories (1HSMP).

This is the code I'm currently using:

<ul>
                                                            <?php
                                                            //Here we fetch category
                                                            $categories_list = array();
                                                            if($stmt = $conn -> prepare("SELECT a.category, a.subcategory, a.subcategory1, a.category_description, a.subcategory_description, a.subcategory_description1,  SUM(nsq.sales_amount) AS sales, SUM(nsq.sales_amount+nsq.cost_amount) AS RUC
                                                                                                FROM dpb.articles a
                                                                                                LEFT JOIN dpb.nav_sales_qty nsq
                                                                                                ON nsq.article_no = a.article_no
                                                                                                WHERE nsq.entry_type_desc = 'Prodaja'
                                                                                                GROUP BY a.subcategory1")) {
                                                               $stmt -> execute();
                                                               $stmt -> store_result();
                                                               $stmt -> bind_result($category, $subcategory, $subcategory1, $category_description, $subcategory_description, $subcategory_description1, $total_sales, $total_profit);
                                                               while($stmt -> fetch()) {
                                                                   $categories_list[] = array('category' => $category,
                                                                                               'subcategory' => $subcategory,
                                                                                               'subcategory1' => $subcategory1,
                                                                                               'category_description' => $category_description,
                                                                                               'subcategory_description' => $subcategory_description,
                                                                                               'subcategory_description1' => $subcategory_description1,
                                                                                               'total_profit' => $total_profit,
                                                                                               'total_sales' => $total_sales);
                                                               }
                                                               $stmt -> close();
                                                            }

                                                            foreach($categories_list as $key => $value) {
                                                            ?>
                                                                <li><?php echo $value['category'].' - '.$value['category_description']; ?>
                                                                    <ul>
                                                                        <?php
                                                                        //Here we filter subcategory
                                                                        if(strpos($value['subcategory'], $value['category']) === 0) {
                                                                            //Iterate over subcategory
                                                                            foreach ($value as $key2 => $value2) {
                                                                                debugVar($value2);
                                                                        ?>

                                                                                <li data-jstree='{"opened":true}'><?php echo $value2['subcategory'] . ' - ' . $value2['subcategory_description']; ?>
                                                                                    <ul>

                                                                                        <?php
                                                                                        //Here we filter subcategory2
                                                                                        if (strpos($value['subcategory1'], $value['subcategory']) !== false) {
                                                                                            ?>

                                                                                            <li data-jstree='{"type":"file"}'>
                                                                                                <?php echo $value['subcategory1']; ?>
                                                                                            </li>

                                                                                            <?php
                                                                                            //Here we close subcategory1 if statement
                                                                                        }
                                                                                        ?>

                                                                                    </ul>
                                                                                </li>
                                                                                <?php
                                                                                //Here we close subcategory if statement
                                                                            }
                                                                        }
                                                                        ?>
                                                                    </ul>
                                                                </li>

                                                            <?php
                                                            //Close foreach loop
                                                            }
                                                            ?>
                                                            </ul>

  The result I'm getting is attached in the photo, and it's not what I want!

The result I need is the following:

I find a category (1H), and then I find all of its subcategories which are 1H% (all that start with 1H) and display them bellow 1H. Then I iterate over all of these subcategories (example 1HS%, 1HP%, 1HRD%) and I find all of their subcategories which are starting with (1HS, 1HP, 1HRD) and display them.

Example:

1H
  1HS
    1HSMP
    1HSTR
    1HSKT
  1HP
    1HPSD
    1HPDD
    1HPSA
  1HRD
    1HRDA
    1HRDBV
   1HRDTE
   1HRDEF

Can you please help me how to achieve that? I spent a lot of time, and I can't get the proper result.

 

[Barand]

Recursion* is easier for this problem than nested loops

 

(* recursion : see "recursion" )

Edited December 12, 2018 by Barand

[Barand]

Here's a recursive solution.

DATA

mysql> select * from category;
+--------+-----------+--------+
| cat_id | catname   | parent |
+--------+-----------+--------+
|      1 | Cat 1     |      0 |
|      2 | Cat 2     |      0 |
|      3 | Cat 3     |      0 |
|      4 | Cat 1 1   |      1 |
|      5 | Cat 1 2   |      1 |
|      6 | Cat 2 1   |      2 |
|      7 | Cat 2 2   |      2 |
|      8 | Cat 2 3   |      2 |
|      9 | Cat 3 1   |      3 |
|     10 | Cat 1 1 1 |      4 |
|     11 | Cat 1 1 2 |      4 |
|     12 | Cat 2 3 1 |      8 |
+--------+-----------+--------+

CODE

<?php
include('db_inc.php');
$db = pdoConnect('test');

$res = $db->query("SELECT cat_id
                        , catname
                        , parent
                   FROM category     
                   ");
$cats = [];

// store the categories in a 2-dim array with arrays of cats belonging to each parent 
foreach ($res as $r) {
    if (!isset($cats[$r['parent']])) {
        $cats[$r['parent']] = [];
    }
    $cats[$r['parent']][] = [ 'id' => $r['cat_id'], 'name' => $r['catname'] ];
}

function subcats(&$cats, $parent=0, $level=0)
{
    if (!isset($cats[$parent])) return;
    $subcats = $cats[$parent];
    foreach ($subcats as $sc) {
        $cls = "class='level$level'";
        echo "<li $cls>{$sc['name']}";
        if (isset($cats[$sc['id']])) {                  // if it has subcategories, 
            echo "\n<ul>\n" ;
            subcats( $cats, $sc['id'], $level+1);       //     re-call function to print its subcats
            echo "</ul>\n" ;
        }
        echo "</li>\n";
    }
}  
?>
<html>
<body>
     <ul>
         <?=subcats($cats, 0)?>
     </ul>
     

</body>
</html>

OUTPUT

 

[budimir]

Thanks Barand! I'll give it a try.

[budimir]

Hey Barand! Thank you very much! That's it, exactly what I wanted!