i am trying to get multidimensional array from categories saved in database with recursion but i am unable to properly format the output i need.
i have tried following code which is working fine if i want to output ul-li multi-level list but not working when i am trying to convert it into array
function get_categories($parent_id=0){
global $link;
$menu=[];
$sql="SELECT * from terms where parent='$parent_id'";
$result=mysqli_query($link,$sql);
while ($row=mysqli_fetch_assoc($result)) {
$sub_cats=get_categories($row['id']);
$menu[$row['name']]=$sub_cats;
}
return $menu;
}
and db schema is in attached file db schema looks like
expected output is
array(
[0]=>array(
['name']=>language
['sub_cats']=>array(
[0]=>array(
[name]=>english
['sub_cats']=>array(
[0]=>array(
[name]=>john milton
['sub_cats']=>0
)
[1]=>array(
[name]=>william blake
['sub_cats']=>0
)
)
)
[1]=>array(
['name']=>spanish
['sub_cats']=>0
)
[2]=>array(
['name']=>sanskrit
['sub_cats']=>0
)
)
)
[1]=>array(
['name']=>xyz
['sub_cats']=>0
)
)
current output is
Array
(
[Language] => Array
(
[english] => Array
(
[abcd] => Array
(
)
)
[hindi] => Array
(
)
[sanskrit] => Array
(
)
)
[science] => Array
(
)
)
or any other format which can be easily transported via JSON and interpreted via JavaScript on other side.
and i know i am using multiple queries which can slow down the process, so if possible tell me the correct way.
also i am using mysqli for now to grasp the concept and i will shift my code in prepared statements later.