How to select all child categories of the main category?

Question

PHP

Unable to select all child categories of the main category.
Category table structure:
Each category has a parent_id. If parent_id = 0, it means it's a parent
. Select all category_ids of the parent into one array.
Here's a piece of code I've been working with. But it doesn't output 3-4-5 nesting of categories:
$filter_category_id is parent here

if ($filter_category_id) {
            $this->load->model('catalog/category');
            $cats = array($filter_category_id);
            $cats = $this->model_catalog_category->getChildCategories($filter_category_id, $cats);

            $cats_merge = array();
            if ($cats) {
                foreach ($cats as $cat) {
                    $catsa = $this->model_catalog_category->getChildCategories($cat);
                    $cats_merge = array_merge($cats_merge, $catsa);
                }
            }
            $cats_merge = array_merge($cats_merge, $cats);
            $cats_merge = array_unique($cats_merge);

            return $cats_merge;
        }

And here is the reference to the table:

public function getChildCategories($id, $cats = array()){
        $sql = "SELECT * FROM category WHERE parent_id  = " . $id;
        $result = $this->db->query($sql);
        $rows = $result->rows;
        if($rows){
            foreach($rows as $row){
                $cats[] = $row['category_id'];
                $this->getChildCategories($row['category_id'], $cats);
            }
        }
        return $cats;
    }

Found a solution:

public function getCategoriesandSubs($parent_id = 0) {
      static $cat_branch = array();
      $results = $this->db->query("SELECT * FROM " . DB_PREFIX . "category c LEFT JOIN " . DB_PREFIX . "category_description cd ON (c.category_id = cd.category_id) WHERE c.parent_id = '" . (int)$parent_id . "' AND c.status = 1");
      $num_rows = count($results->rows);
      if($num_rows != 0) {
         foreach ($results->rows as $result) {
            $cat_branch[] = $result['category_id'];
            $this->getCategoriesandSubs($result['category_id']);
         }
      }
      if(count($cat_branch) == 0) {
        $cat_branch[] = $parent_id;
      }
      return $cat_branch;
   }

Answer 1

This is the cycle you need.
On the first iteration, you ask for the id of the children of the parents, on each subsequent iteration, the children of the children selected in the previous one. As soon as the selection of descendants is empty, you stop the loop.

Answer 2

https://dev.mysql.com/doc/refman/8.0/en/with.html
nice, recursively

Answer 3

I am using the following:

<?php
// Получение сплошного списка
function get_cat()
{
  global $pdo;
  $cat_arr = [];

            try
            {
                $query = "SELECT ID, PID, NAME FROM `catalog` WHERE 1";
                $stmt = $pdo->prepare( $query );
                $stmt->execute();
            }
            catch (PDOException $e)
            {
              die("Error in :".__FILE__." file, at ".__LINE__." line. Can't get data : " . $e->getMessage());
            }
            while( $row = $stmt->fetch(PDO::FETCH_ASSOC ) )
      $cat_arr[$row['ID']] = $row;

  return $cat_arr;
}

// Построение дерева
function tree_map( $dataset ) 
{
  $tree = [];

  foreach ($dataset as $id=>&$node) 
    if (!$node['PID'])
      $tree[$id] = &$node;
        else
  $dataset[$node['PID']]['childs'][$id] = &$node;
 	return $tree;
}

$dataset = get_cat();
$dataset = tree_map( $dataset );

Used by PDO, but it doesn't matter. In the resulting array, we have a tree with child-PID relationships.
For convenient data visualization I use:

<?php
    function debug( $arr )
    {
        $str = print_r($arr, true);
        echo '<pre>'.$str.'</pre>';
    }