How do I count the rows in a table in the CodeIgniter framework?

Question

I'm trying to count the number of row in a table. The table has a fk 'city_id'. I want to count the number of the row that meets certain criteria and want to echo the number.

I've tried with the following code:

function count(){

  $citys = $this->db->get('city');

  foreach ($citys->result() as $city) {

    $this->db->where('info', array('city_city_id'=>$city->city_id) AND status_status_id==1);
    $sql = $this->db->count_all('info');

  }

  return  $sql->result;
} 

Controller:

  $data['city'] = $this->state_model->count();
  $this->load->view('sview', $data);

View:

 <?php foreach($citys as $cities):?>
        <h4><?php echo $city ?>
      <?php endforeach;?></br></br>

In my model i'm trying to count the num of rows where lets say, city_city_id=1 and status_status_id=1 in my 'info' table. But i'm getting the following error :

   Severity: Notice

 Message: Use of undefined constant status_status_id - assumed 'status_status_id'

 Filename: models/State_model.php

 Line Number: 98

In line 98 i have

         $this->db->where('info', array('city_city_id'=>$city->city_id) AND                    status_status_id==1);

i'm newly working with codeigniter so a little help would be appreciated.

Thanks in advance.

Answer 1

This should get you going:

$this->db->where('city_id', $city_id);
$this->db->from('city');
return $this->db->count_all_results();

Returns an integer. CodeIgniter Active Record

You can’t use SQL queries in the middle of PHP, that’s why AND breaks it. You don’t need to join the queries with AND, CodeIgniter does that for you.