CodeIgniter Forums
Confusion re loops for database results - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Confusion re loops for database results (/showthread.php?tid=8847)

Confusion re loops for database results - El Forum - 06-02-2008

[eluser]jacobkball[/eluser]
Hi there,

A CI newbie question follows Smile

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 Smile

Cheers
Jacob

Confusion re loops for database results - El Forum - 06-03-2008

[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 Smile

Confusion re loops for database results - El Forum - 06-03-2008

[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:
&lt;?php foreach ($results as $row) { ?&gt;
       &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 Smile