title: PHP find parents of leaf and build tree
body: <p>I have a quite simple task but I lost somewhere. I have categories table with common structure: id, id_parent, some other fields less important. I need to build multidimensional array from parents to children nodes like this:</p>
  
  <pre class="lang-php prettyprint-override"><code>array(
      'id' =&gt; 1,
      'id_parent' =&gt; 0,
      'name' =&gt; 'Main', 
      'children' =&gt; array(
          'id' =&gt; 2,
          'id_parent' =&gt; 1,
          'name' =&gt; 'Main-sub',
          'children' =&gt; array(
              'id' =&gt; 3,
              'id_parent' =&gt; 2,
              'name' =&gt; 'Main-sub-sub'
          )
      )
  )
  </code></pre>
  
  <p>I have given id of category (e.g. id = 3). I need to find very first parent of this category and then build tree traversing down. I'm tried simplest method using if statement recursively, but order of array I got is reversed. This is how it looks like at this moment:</p>
  
  <pre class="lang-php prettyprint-override"><code>public function getCategoryById($id)
  {
      $branch = array();
      $data = $this -&gt; db -&gt; query("SELECT categories.* FROM categories WHERE categories.visible = 1 AND categories.id = {$id} ORDER BY categories.position -- LIMIT 1");
      if(is_array($data))
      {
          foreach($data as $item)
          {
              if($item['id_parent'] != 0)
              {
                  // do it again recursively
                  $parent = $this -&gt; getCategoryById($item['id_parent']);
                  $branch['parents'] = $parent;
                  // lost here
              }
              // lost here also
              $branch[] = $item;
          }
      }
      return $branch;
  }
  </code></pre>
  
  <p>I have searched threads and found few questions similar to mine, but solutions doesn't work in my case.</p>
  
  <p>Any help would be appreciated. </p>