Confusion re loops for database results

[eluser]jacobkball[/eluser]
Hi there,

A CI newbie question follows

I have a page where I want to list a series of db query results, but I'm having trouble getting my head around how to actually achieve it using models, controllers and views.

What I'm trying to get is:

Category One
- Cat One SubCat One (count of items in subcat)
- Cat One SubCat Two (count of items in subcat)
- Cat One SubCat Three (count of items in subcat)

Category Two
- Cat Two SubCat One (count of items in subcat)
- Cat Two SubCat Two (count of items in subcat)
- Cat Two SubCat Three (count of items in subcat)

and I have these three functions in my model:

Code:

function get_maincat_info () {
    $query = $this->db->get('maincategories');
    return $query->result();
  }
  
  function get_subcat_info ($maincatid) {
    $query = $this->db->where('MainCatID',$maincatid);
    $query = $this->db->get('subcategories');
    return $query->result();
  }
  
  function get_item_count ($subcatid) {
    $query = $this->db->where('SubCatID',$subcatid);
    $query = $this->db->where('ShowItem','y');
    $count = $this->db->count_all_results('iteminfo');
    return $count;
  }

Obviously, a loop is needed in my view:

Code:

<?php foreach ($results as $row) { ?>
       &lt;?php echo $row->MainCatName;?&gt;<br>
    &lt;?php foreach ($subcatresults as $subcatrow) { ?&gt;
        <a href="/search/&lt;?php echo $row->MainCatLink . '/' . $subcatrow->SubCatLink; ?&gt;/" class="catlink">&lt;?php echo $subcatrow->SubCatName; ?&gt;</a>&nbsp;&nbsp;[ &lt;?php echo $countresults[0]; ?&gt; ]<br>
        &lt;?php
        }
    }
?&gt;

but I don't know how to do it so that it loops through each main category, retrieves the sub category info AND count, and returns it to the view.

I don't think I've explained it very well, so let me know what other info you need to see what I'm actually trying to do!!

Hope someone can help

Cheers
Jacob

[eluser]jacobkball[/eluser]
Clearly, I wasn't searching for the right keywords earlier, as I swear I had trouble finding other people with a similar issue, but now I've found a bunch, so I'm working my way through them to make some sense of it all

[eluser]Sumon[/eluser]
why not you use something like:

Code:

function get_maincat_info () {
$query = $this->db->query("SELECT Cat.cat_name, SCat.subcat_name, Item.item_name FROM maincategories Cat, subcategories SCat, iteminfo Item WHERE Cat.MainCatID=$maincatid and Cat.MainCatID=SCat.MainCatID and SCat.sub_cat_id=Item.sub_cat_id");
if ($query->num_rows() > 0)
   return $query->row_array();
}

writing model in this way will save your code in view as well.. use only single array

Code:

<?php foreach ($results as $row) { ?>
       &lt;?php echo $row->MainCatName;?&gt;<br>
&lt;?php endforeach;?&gt;

However please change my query... field name must not be same as you expect. Other will work fine