DB and nested calls

[eluser]elite-board[/eluser]
hi,

I have a situation that I'm trying to debug. there is a page that i need to call two sql queries, and they have to be nested as one query relies on the previous entry.

example of this

Code:

SELECT id FROM category ORDER BY Sort ASC
SELECT id, Name FROM category WHERE category='$row[id]' ORDER BY Sort ASC

now my issue is that the first query fires perfectly fine, however the second one does not.

heres my code:

Code:

$this->db->select('id,Name')->from('category')->order_by("title", "asc");
$query = $this->db->get();

foreach ($query->result() as $row)
{
$parentData[] = $row;
$this->db->select('id,Name')->from('category')->where('category',$row->id)->order_by("title", "asc");
    $query2 = $this->db->get();
    foreach ($query2->result() as $row2)
    {
        $childData[] = $row2;
    }
}

I'm open to trying a join, but i just don't see that working correct as the UI looks like

[HEADER]
[column]
[BODY]

the items in all caps are the db data.

I hope someone can help me on this as i'm getting stuck.

Thanks.