title: mySQL hierarchical query on codeigniter
body: <p>I'm havin troubles trying to get the results from this table using slugs.</p>
  
  <pre><code>| id | parent | slug       | name       |  
  -----------------------------------------
  |  1 | 0      | animations | animations |
  |  2 | 1      | flash      | flash      |
  |  3 | 2      | looped     | looped     |
  |  4 | 1      | gif        | gif images |
  </code></pre>
  
  <p>For example i need to get the categories where the parent is 'animations' and the child is 'flash'.</p>
  
  <p>The real issue is because i need to search for results using category/$parent_slug/$child_slug instead use ID's (category/$id)  to get <code>|3|2|looped|looped|</code>.</p>
  
  <p>This what i've so far:</p>
  
  <pre><code>function get_category_childrens($category_parent=null){
      $this-&gt;db-&gt;select('*');
      if(!is_null($category_parent)){
          $this-&gt;db-&gt;where('categories.slug', $category_parent);
          $this-&gt;db-&gt;join('categories as l1', 'l1.parent = categories.id', 'left');
      }
      else{
          $this-&gt;db-&gt;where('categories.parent', '0');
      }
      $query = $this-&gt;db-&gt;get('categories');
      return $query-&gt;result_array();
  } 
  </code></pre>
  
  <p>The sql generated:</p>
  
  <pre><code>SELECT *
  FROM (`categories`)
  LEFT JOIN `categories` as l1 ON `l1`.`parent` = `categories`.`id`
  WHERE `categories`.`slug` = 'animations'  
  </code></pre>
  
  <p>No problem if you dont know CI, if you've the query or an idea of it please comment.</p>