title: how to return multilevel categories in multidimensional array with php and mysql
body: <p>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.</p>
  
  <p>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</p>
  
  <pre><code>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;
    }
  </code></pre>
  
  <p>and db schema is in attached file
  <a href="https://i.stack.imgur.com/Mv0at.png" rel="nofollow noreferrer">db schema looks like</a></p>
  
  <p>expected output is </p>
  
  <pre><code>array(
    [0]=&gt;array(
      ['name']=&gt;language
      ['sub_cats']=&gt;array(
        [0]=&gt;array(
          [name]=&gt;english
          ['sub_cats']=&gt;array(
            [0]=&gt;array(
              [name]=&gt;john milton
              ['sub_cats']=&gt;0
            )
            [1]=&gt;array(
              [name]=&gt;william blake
              ['sub_cats']=&gt;0
            )
          )
        )
        [1]=&gt;array(
          ['name']=&gt;spanish
          ['sub_cats']=&gt;0
        )
        [2]=&gt;array(
          ['name']=&gt;sanskrit
          ['sub_cats']=&gt;0
        )
      )
    )
    [1]=&gt;array(
      ['name']=&gt;xyz
      ['sub_cats']=&gt;0
    )
  )
  </code></pre>
  
  <p><strong>current output is</strong></p>
  
  <pre><code>Array
  (
      [Language] =&gt; Array
          (
              [english] =&gt; Array
                  (
                      [abcd] =&gt; Array
                          (
                          )
  
                  )
  
              [hindi] =&gt; Array
                  (
                  )
  
              [sanskrit] =&gt; Array
                  (
                  )
  
          )
  
      [science] =&gt; Array
          (
          )
  
  )
  </code></pre>
  
  <p>or any other format which can be easily transported via JSON and interpreted via JavaScript on other side.</p>
  
  <p>and i know i am using multiple queries which can slow down the process, so if possible tell me the correct way.</p>
  
  <p>also i am using mysqli for now to grasp the concept and i will shift my code in prepared statements later.</p>