Codeigniter query and structure

[eluser]AFRC[/eluser]
Hi,

I have a model with two queries:

Code:

function listCategories()
{
     $result=$this->db->query("select * from table")->result();
     return $result;
}

function listSubCategories($var)
{
     $result=$this->db->query("select * from table where field=$var")->result();
     return $result;
}

Then i call the first function on my controller:

Code:

$data['rows']=$this->my_model->listCategories();

The problem is: now i need to call the second function (listSubCategories()) and the $var that i need to pass to it, is a field from the database which is returned by the listCategories() function.

The idea was doing something like (in the view):

Code:

foreach ($categories as $category) {
     echo '<h1>'.$category->title.'</h1>';
     foreach ($subcategories as $subcategory) {
          echo '<h2>'.$subcategory->title.'</h2>';
     }
}

What's the best way to handle a situation like this?

Thanks in advance.

[eluser]osci[/eluser]
if different tables - your example doesn't illustrate this - just do a join

[eluser]AFRC[/eluser]
Hi osci,

It's the same table

[eluser]osci[/eluser]
and your first query returns you only the categories and not subcategories?
You got something totally wrong.

btw there is a thread in here (search) that show how to join from one table to itself for a scenario like yours.

[eluser]AFRC[/eluser]
Hi osci,

Thanks again for your help.

I store categories and subcategories in one table.

For the level 0 categories i select where the field parent=0.

Then i usually have a foreach where i select the categories where the field parent=$row_id.

Do you think this is the wrong way to do it?

[eluser]osci[/eluser]
If you like it it's ok. As I said there is another thread about the same scenario. Do a search

[eluser]byde[/eluser]
I have been in your shoes but i dont have the code in my pc rigth now.

try to call listSubCategories($var) insede of listCategories so you can return i bigger array at listCategories.

This array should content categories and subcategories