how to do this..[query inside query]

[eluser]Bluemagix[/eluser]
how can i do this type of thing in codeigniter

Code:

$sql="SELECT * FROM category WHERE parentid=0";
$result=mysql_query($sql);
while($row=mysql_fetch_assoc($result))
{
echo $row["name"]."<br />";
$sub_sql="SELECT * FROM category WHERE parentid='".$row["id"]."'";
$sub_result=mysql_query($sub_sql);
while($sub_row=mysql_fetch_assoc($sub_result))
{
  echo "- ".$sub_row["name"]."<br />";
  }
}

[eluser]Phil Sturgeon[/eluser]
With active record

Code:

$this->load->database();

$this->db->where('parentid', 0);
$query = $this->db->get('category');
foreach($query->result_array() as $row)
{
  echo $row["name"]."<br />";

  $this->db->where('parentid', $row["id"]);
  $sub_query = $this->db->get('category');

  foreach($sub_query->result_array() as $sub_row)
  {
    echo "- ".$sub_row["name"]."<br />";
  }
}

Without active record

Code:

$this->load->database();

$query = $this->db->query("SELECT * FROM category WHERE parentid=0");
foreach($query->result_array() as $row)
{
  echo $row["name"]."<br />";

  $sub_query = $this->db->query("SELECT * FROM category WHERE parentid='".$row["id"]."'");

  foreach($sub_query->result_array() as $sub_row)
  {
    echo "- ".$sub_row["name"]."<br />";
  }
}

This is all in the manual my friend.