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;
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;
} </div>