title: How to convert nested sql into Codeigniter Active record
body: <p>How do I write query statement in CodeIgniter active record?</p>
  
  <p>Here is View code in <strong>menu.php</strong> file with php procedure. (this code can run)</p>
  
  <pre><code>    &lt;?php
  
      $m=1;
  
      echo "&lt;table&gt;
      &lt;tr&gt;
        &lt;td&gt;No&lt;/td&gt;&lt;td&gt;Menu&lt;/td&gt;&lt;td&gt;Submenu 1&lt;/td&gt;&lt;td&gt;Submenu 2&lt;/td&gt;&lt;td&gt;Submenu 3&lt;/td&gt;
      &lt;/tr&gt;";
  
          // main menu
          $query1 = $this-&gt;db-&gt;query("select id,title,parent,order from tbl_menu where parent='0' order by order;");
          foreach($query1-&gt;result_array() as $r) {
  
      echo "&lt;tr&gt;
          &lt;td&gt;$m&lt;/td&gt;&lt;td&gt;$row[title]&lt;/td&gt;&lt;td&gt;-&lt;/td&gt;&lt;td&gt;-&lt;/td&gt;&lt;td&gt;-&lt;/td&gt;
      &lt;/tr&gt;";
  
          $m++;
          $s1=1;
  
          // submenu 1
          $query2 = $this-&gt;db-&gt;query("select * from tbl_menu where parent='$row[id]' order by order;");
          foreach($query2-&gt;result_array() as $r) {
  
      echo "&lt;tr&gt;
          &lt;td&gt;&lt;/td&gt;&lt;td &gt;$s1&lt;/td&gt;&lt;td&gt;$r[title]&lt;/td&gt;&lt;td&gt;-&lt;/td&gt;&lt;td&gt;-&lt;/td&gt;
      &lt;/tr&gt;";
  
              $s1++;
              $s2=1;
  
              // submenu 2
  
              $query3 = $this-&gt;db-&gt;query("select * from tbl_menu where parent='$r[id]' order by order;");
              foreach($query3-&gt;result_array() as $r2) {
  
  
      echo "&lt;tr&gt;
          &lt;td&gt;&lt;/td&gt;&lt;td&gt;&lt;/td&gt;&lt;td &gt;$s2&lt;/td&gt;&lt;td&gt;$r2[title]&lt;/td&gt;&lt;td&gt;-&lt;/td&gt;
      &lt;/tr&gt;";
  
                  $s2++;
                  $s3=1;
                  // submenu 3
                  $query4 = $this-&gt;db-&gt;query("select * from tbl_menu where parent='$r2[id]' order by order;");
                  foreach($query4-&gt;result_array() as $r3) {
  
      echo "&lt;tr&gt;
          &lt;td&gt;&lt;/td&gt;&lt;td&gt;&lt;/td&gt;&lt;td&gt;&lt;/td&gt;&lt;td&gt;$s3&lt;/td&gt;&lt;td&gt;$r3[title]&lt;/td&gt;
      &lt;/tr&gt;";
          $s3++;
  
                  } // submenu 3
              } // submenu 2
          } // submenu 1
      } // main menu
  
      echo"&lt;/table&gt;";
  
  
      ?&gt;
  </code></pre>
  
  <p>I changed into this... (this is error)</p>
  
  <pre><code>    &lt;?php
  
      $m=1;
  
      echo "&lt;table&gt;
      &lt;tr&gt;
        &lt;td&gt;No&lt;/td&gt;&lt;td&gt;Menu&lt;/td&gt;&lt;td&gt;Submenu 1&lt;/td&gt;&lt;td&gt;Submenu 2&lt;/td&gt;&lt;td&gt;Submenu 3&lt;/td&gt;
      &lt;/tr&gt;";
  
          // main menu
          foreach($query1 as $r) {
  
      echo "&lt;tr&gt;
          &lt;td&gt;$m&lt;/td&gt;&lt;td&gt;$row[title]&lt;/td&gt;&lt;td&gt;-&lt;/td&gt;&lt;td&gt;-&lt;/td&gt;&lt;td&gt;-&lt;/td&gt;
      &lt;/tr&gt;";
  
          $m++;
          $s1=1;
  
          // submenu 1
          foreach($query2 as $r) {
  
      echo "&lt;tr&gt;
          &lt;td&gt;&lt;/td&gt;&lt;td &gt;$s1&lt;/td&gt;&lt;td&gt;$r[title]&lt;/td&gt;&lt;td&gt;-&lt;/td&gt;&lt;td&gt;-&lt;/td&gt;
      &lt;/tr&gt;";
  
              $s1++;
              $s2=1;
  
              // submenu 2
  
              foreach($query3 as $r2) {
  
  
      echo "&lt;tr&gt;
          &lt;td&gt;&lt;/td&gt;&lt;td&gt;&lt;/td&gt;&lt;td &gt;$s2&lt;/td&gt;&lt;td&gt;$r2[title]&lt;/td&gt;&lt;td&gt;-&lt;/td&gt;
      &lt;/tr&gt;";
  
                  $s2++;
                  $s3=1;
                  // submenu 3
                  foreach($query4 as $r3) {
  
      echo "&lt;tr&gt;
          &lt;td&gt;&lt;/td&gt;&lt;td&gt;&lt;/td&gt;&lt;td&gt;&lt;/td&gt;&lt;td&gt;$s3&lt;/td&gt;&lt;td&gt;$r3[title]&lt;/td&gt;
      &lt;/tr&gt;";
          $s3++;
  
                  } // submenu 3
              } // submenu 2
          } // submenu 1
      } // main menu
  
      echo"&lt;/table&gt;";
  
  
      ?&gt;
  </code></pre>
  
  <p>Here is Model code in <strong>Menu_model.php</strong> file.</p>
  
  <pre><code>&lt;?php
  
  
  class Menu_model extends CI_Model {
  
      public function menu_q1() {
      $this-&gt;db-&gt;select('*')-&gt;from('tbl_menu')-&gt;where('parent','0')-&gt;order_by('order', 'ASC');
      $query1 = $this-&gt;db-&gt;get();
      return $query1-&gt;result_array();
      }
  
      public function menu_q2() {
      $this-&gt;db-&gt;select('*')-&gt;from('tbl_menu')-&gt;where('parent',$row['id'])-&gt;order_by('order', 'ASC');
      $query2 = $this-&gt;db-&gt;get();
      return $query2-&gt;result_array();
      }
  
      public function menu_q3() {
      $this-&gt;db-&gt;select('*')-&gt;from('tbl_menu')-&gt;where('parent',$r['id'])-&gt;order_by('order', 'ASC');
      $query3 = $this-&gt;db-&gt;get();
      return $query3-&gt;result_array();
      }
  
      public function menu_q4() {
      $this-&gt;db-&gt;select('*')-&gt;from('tbl_menu')-&gt;where('parent',$r2['id'])-&gt;order_by('order', 'ASC');
      $query4 = $this-&gt;db-&gt;get();
      return $query4-&gt;result_array();
      }
  
  }
  
  ?&gt;
  </code></pre>
  
  <p>Here is Controller code in <strong>Menu_controller.php</strong> file.</p>
  
  <pre><code>&lt;?php
  
  Class Menu_controller extends CI_Controller {
  
      function __construct(){
          parent::__construct();
          $this-&gt;load-&gt;model('menu_model'); //Load profil_model
      }
  
      public function profil() {
  
          $query1 = $this-&gt;profil_model-&gt;menu_q1();
          $query2 = $this-&gt;profil_model-&gt;menu_q2();
          $query3 = $this-&gt;profil_model-&gt;menu_q3();
          $query4 = $this-&gt;profil_model-&gt;menu_q4();
  
      $this-&gt;load-&gt;view('profil', array('query1' =&gt; $query1,'query2' =&gt; $query2,'query3' =&gt; $query3,'query4' =&gt; $query4));
  
  
  }
  
  ?&gt;
  </code></pre>
  
  <p>This is the table</p>
  
  <pre>
      --------------------------------------------------------
      No  |  Menu       | Submenu 1 | Submenu 2  | Submenu 3
      --------------------------------------------------------
      $m  | $row[title] | -         | -          | -
          |          $s1| $r[title] | -          | -
          |             |        $s2| $r2[title] | -
          |             |           |         $s3| $r3[title]
      --------------------------------------------------------
  </pre>
  
  <p>But getting error in this statement:</p>
  
  <p>1) where('parent',$row['id'])</p>
  
  <p>2) where('parent',$r['id'])</p>
  
  <p>3) where('parent',$r2['id'])</p>