如何使用php和mysql在多维数组中返回多级类别

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.

Try this,

$mysqli = new mysqli("localhost", "user", "password", "database");
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}

$menu = [];


function get_categories($parent_id = 0, &$menu)
{
    global $mysqli;

    if ($stmt = $mysqli->prepare("SELECT * from terms where parent=?")) {

        $stmt->bind_param("i", $parent_id);

        $stmt->execute();

        $result = $stmt->get_result();

        while ($row = $result->fetch_assoc()) {

            $reference = &$menu[];
            $reference['name'] = $row['name'];
            $sub_cats = get_categories($row['id'], $reference);
            $reference['sub_cats'] = $sub_cats ? $sub_cats : 0;

        }

        $stmt->close();
    }
}

get_categories(0,$menu);
echo '<pre>';
print_r($menu);
echo '<pre>';

I have used mysqli prepared statements, just to avoid sql injection and I recommend you to use prepared statements as well.

Output,

Array
(
    [0] => Array
        (
            [name] => language
            [0] => Array
                (
                    [name] => english
                    [0] => Array
                        (
                            [name] => john milton
                            [sub_cats] => 0
                        )

                    [1] => Array
                        (
                            [name] => william blake
                            [sub_cats] => 0
                        )

                    [sub_cats] => 0
                )

            [1] => Array
                (
                    [name] => spanish
                    [sub_cats] => 0
                )

            [2] => Array
                (
                    [name] => sanskrit
                    [sub_cats] => 0
                )

            [sub_cats] => 0
        )

    [1] => Array
        (
            [name] => xyz
            [sub_cats] => 0
        )

)