How to select all child categories of the main category?

It is impossible to select all the child categories of the main category. The category table structure:

Each category has parent_id. If parent_id = 0, this means parent. You need to select all the category_id of the parent in one array. Here is a piece of code I worked with. But it does not display 3-4-5 categories nesting: $ filter_category_id here is the parent

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 then refer 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; 

}