i want to get Hierarchical structure of category and subcategory for nth level in one mysql query

Question

i want to get Hierarchical structure of category and subcategory for nth level in one mysql query i try some solution but it's not work properly. i try following query but it only get only subcategory but, i want it for nth level from one table like

category|subcategory|sub-subcategory|......

my table is fd_category and it's field is

id|name|parent_id FK -> fd_category.id  



 select c.id
      , c.name
      , group_concat(sc.name) as subcategory 
  from fd_category c 
  join fd_category sc 
   on c.id=sc.parent_id 
 where c.parent_id=null 
 group 
   by sc.parent_id;

Answer 1

public function get_menu() {
       $this->db->select('id,name,parent_id');
        $menu = $this->db->get('category')->result_array();
        $data=$this->menu_child($menu);
        //print_r($data);        
        return $data;
    } 
function menu_child($menu, $parent = NULL) {
       $main_menu = array_filter($menu, function($a)use($parent) {
                   return $a['parent_id'] == $parent;
               });
       if ($main_menu) {
           foreach ($main_menu as $key => $value) {
               $main_menu[$key]['sub_menu'] = $this->menu_child($menu, $value['id']);
           }
       }
       return $main_menu;
    }