How to display children in recursion?

Question

PHP

Good day to all! Please tell a newbie, I am writing a menu for the admin panel, there is a menu with multi-level nesting, I did it through recursion, in the end, I wanted the sum of the final products in each subcategory to be displayed at each menu level, but I only display them in the final parent

$res1 = mysqli_query(DB::db(), 'SELECT id,name,pid FROM ve_category WHERE toshow1 = 1 AND toshow = 1');
$num = mysqli_num_rows($res1);
if ($num > 0) {
  $cats = [];
  while ($cat =  mysqli_fetch_assoc($res1)) {
    $req1 = mysqli_fetch_assoc(mysqli_query(DB::db(), 'SELECT COUNT(a.good_id) as cnt FROM ve_goods_images as a join ve_goods as b on a.good_id = b.id WHERE b.category = ' . $row['id'] . ' AND b.inprod = 0 AND ( a.width < 500 or a.height < 500 or a.width / a.height < 3/4 or a.width / a.height > 4/3 )'));
    $cats_ID[$cat['id']][] = $cat;
    $cats[$cat['pid']][$cat['id']] =  $cat;
  }
}

function build_tree($cats, $pid, $only_parent)
{

  if (is_array($cats) and isset($cats[$pid])) {
    $tree = '<ul>';
    if ($only_parent == false) {
      foreach ($cats[$pid] as $cat) {
        $req1 = mysqli_fetch_assoc(mysqli_query(DB::db(), 'SELECT COUNT(a.good_id) as cnt FROM ve_goods_images as a join ve_goods as b on a.good_id = b.id WHERE b.category = ' . $cat['id'] . ' AND b.inprod = 0 AND ( a.width < 500 or a.height < 500 or a.width / a.height < 3/4 or a.width / a.height > 4/3 )'));
        $tree .= '<li>' . $cat['name'] . ' #' . $cat['id'] . ' (' . $req1['cnt'] . ' )';
        $tree .=  build_tree($cats, $cat['id'], false);
        $tree .= '</li>';
      }
    } elseif (is_numeric($only_parent)) {
      $cat = $cats[$pid][$only_parent];
      $res2 = mysqli_query(DB::db(), 'SELECT DISTINCT producer FROM ve_goods WHERE category= ' . $cats[$pid][$only_parent] . '');
      $tree = '<ul class="nested">11';

      while ($row1 = mysqli_fetch_assoc($res2)) {
        $goods_producer = $row1['producer'];

        $tree .= '<li><span class="caret">' . $goods_producer . '</span>';

        

        $tree .= '</li>';
      }
      $tree .= '</ul>';
    }
    $tree .= '</ul>';
  }


  return $tree;
}


function find_parent($tmp, $cur_id)
{
  if ($tmp[$cur_id][0]['pid'] != 0) {
    return find_parent($tmp, $tmp[$cur_id][0]['pid']);
  }
  return (int)$tmp[$cur_id][0]['id'];
}


echo build_tree($cats, 0, find_parent($cats_ID, [95, 113, 117, 118, 164, 352, 530, 815, 816, 817, 818, 916, 1249, 1419, 1460, 1588, 1614, 1624, 1644, 1789, 1918]));

that's what happens

the last parent has values ​​​​and higher in the hierarchy are zeros, please help, it's a matter of life. I am a young specialist and just started working in this area and I like all this, but I have not come across this, please help

Answer 1

I propose to make the calculation of the number a separate function, and also with recursion. The option is not very good in terms of performance (there will be many queries to the database), but for lack of a better one, it will do. The scheme is something like this:

function countGoods ($catId) {
  $result = ... // Здесь считаете количество товаров именно в этой категории
  $children = ... // Здесь получаете список дочерних категорий
  foreach ($children as $child) {
    $result += countGoods($child['id']);
  }
  return $result;
}

Answer 2

I tried to figure out what was what, but the code is noodles, of course. There are also some troubles with the pictures, which the author did not describe in any way.
This daunting task is definitely not solved by two queries?

SELECT category_id, COUNT(id) FROM goods GROUP BY category_id

- to get the number in distant categories
and - to build a tree by pulling the values ​​obtained above to it and summing them when outputting. The only trick is not to display the category at once, and then the subcategories, but to collect and sum up the subcategories, and then display both, having received the required amount.
SELECT * FROM categories ORDER BY pid ASC