Problem with counting comments inside loop

[eluser]mkairys[/eluser]
I've recently started to use CodeIgniter since I found it the easiest framework to work with to get stuff done. I've been working on a web design gallery site as a side project and seem to have hit a problem (probably due to being sleep deprived, I'm sure there would be a simple answer).

Essentially for the index I wanted to display a few items in the gallery in a paginated form (this works). I also wanted to display the amount of comments for each particular item however had problems getting the row id from the loop displaying the query results. Here's my code so far.

Controller

Code:

function index()
    {
        /* Set Variables */
        $data['title'] = "Gallery Index";
        
        /* Initialise Database */
        $this->load->database();
        
        /* Pagination */
        $this->load->library('pagination');
        $config['base_url'] = 'http://localhost/public_html/cssgallery/index.php/gallery/index/'; // Leave temporarily
        $config['total_rows'] = $this->db->count_all('design');
        $config['per_page'] = '6';
        $this->pagination->initialize($config);

        /* Database Queries */
        
        /* Query: Display Gallery Item */
        $this->db->select('*');
        $this->db->from('design');
        $this->db->join('site', 'site.site_id = design.site_id');
        $this->db->join('category', 'category.category_id = site.category_id', 'left');
        $this->db->limit($config['per_page'], $this->uri->segment(3));
        $data['query'] = $this->db->get();
        
        /* Query: Display Comment Count */
        $this->db->select('*');
        $this->db->from('design_comments');
        $this->db->where('design_id', $variable);
        $data['query2'] = $this->db->get();        
        
        /* Output */
        $this->load->view('gallery', $data);
            
    }

View

Code:

<?php $this->load->view('layout/header', $data);?>

<?php foreach ($query->result_array() as $row):?>
     <p>
     &lt;?php echo $row['design_id'];?&gt;
     <a href="&lt;?php echo $row['site_address'];?&gt;">&lt;?php echo $row['site_title'];?&gt;</a>
     <br />
     Category: &lt;?php echo $row['category_name'];?&gt;
     <br />
     Comments: &lt;?php echo $query2->num_rows()?&gt;
     </p>
&lt;?php endforeach;?&gt;

&lt;?php echo $this->pagination->create_links();?&gt;

&lt;?php $this->load->view('layout/footer', $data);?&gt;

In the 2nd query in the controller I have just left '$variable' to show where I wanted to get the row id from the loop in my view. If I leave a defined number such as 1 it returns results fine so I'm pretty sure the query is spot on. I just wasn't quite sure how to grab design_id from the loop array and output the result.

Also was wondering, am I on the right track to keep my code clean? I'll move the database specific stuff to Models later, just wanted to make sure I get the queries working first.

Any help is greatly appreciated!

[eluser]xwero[/eluser]
try

Code:

$this->db->select('count(*) as tel');
        $this->db->from('design_comments');
        $this->db->where('design_id', $variable);
        $query = $this->db->get();
        $row = $query->row();
        $data['query2'] = $row->tel;

i don't know if it works, i like to write my sql statements. You don't need to pass the query object if you are only after the number.

[eluser]mkairys[/eluser]
Unfortunately that didn't work for me, I always got the result as 0. I did actually just manage to get mine working by putting all of the database queries inside my view (which I know is very bad practice) since I could set $variable = $row['design_id'] inside the loop which seemed to work. How would I be able to get this variable to work inside the controller since I can't use $row['design_id'] as that's in the loop inside the view.

[eluser]xwero[/eluser]
Ok i understand now, i wasn't awake when i answered It's best to put this in a model and not in the controller. You would get something like this

Code:

class Design_model extends Model
{

function Design_model()
{
    parent::Model();
}

function design_sites($limit,$offset)
{
        $this->db->select('*');
        $this->db->from('design');
        $this->db->join('site', 'site.site_id = design.site_id');
        $this->db->join('category', 'category.category_id = site.category_id', 'left');
        $this->db->limit($limit, $offset);
        $query = $this->db->get();
        $return = array();
        foreach($query->result_array() as $row) // loop through rows
        {
           $temp = array();
           foreach($row as $key => $val) // loop trough fields
           {
              if($key == 'design_id')
              {
                 $query2 = $this->db->query('select count(*) as tel from design_comments where design_id = ?',array($val));
                 $row2 = $query2->row2();
                 $temp['comments'] = $row2->tel;
              }
              $temp[$key] = $val;
           }
           $return[] = $temp;
        }
        return $return;
}

}
// controller
$this->load->model('Design_model','',true);
$data['query'] = $this->Design_model->design_sites($config['per_page'], $this->uri->segment(3));
$this->load->view('gallery',$data);
// view
<?php foreach ($query as $row):?>
     <p>
     &lt;?php echo $row['design_id'];?&gt;
     <a href="&lt;?php echo $row['site_address'];?&gt;">&lt;?php echo $row['site_title'];?&gt;</a>
     <br />
     Category: &lt;?php echo $row['category_name'];?&gt;
     <br />
     Comments: &lt;?php echo $row['comments']; ?&gt;
     </p>
&lt;?php endforeach;?&gt;

[eluser]mkairys[/eluser]
Cheers xwero, had to slightly tweak some things but your solution worked like a charm. Thanks