Count rows before adding a further limit statement CodeIgniter?

Question

I have ran into a problem...

I have a bunch of where statments like so...

$this->db->where('Pool', "1");
$this->db->where('Bedrooms >=', "3");

Then a limit statement

$this->db->limit($limit, $offset);

And finally my get statement

$query = $this->db->get('table-name');

My problem is I need to count the results before my limit statement, to get the total rows without the limit.. So I tried this..

$this->db->where('Pool', "1");
$this->db->where('Bedrooms >=', "3");

$num_rows = $this->db->count_all_results();

$this->db->limit($limit, $offset);
$query = $this->db->get('table-name');

This counts my rows with the where statements fine.. However, the get statement now gets records without the previous where statements working.

It's not visible, but there is a large amount of code handling more where statements, and grabbing things in urls, So I'd prefer not to perform the retrieval of data twice in order to fix this...

Cheers!

Answer 1

I know this is an old question, but I just ran into this problem and came up with a different solution.

The idea is to take a copy of the db class before the limit and offset.

$this->db->where('Pool', "1");
$this->db->where('Bedrooms >=', "3");

//here we use the clone command to create a shallow copy of the object
$tempdb = clone $this->db;
//now we run the count method on this copy
$num_rows = $tempdb->from('table-name')->count_all_results();

$this->db->limit($limit, $offset);
$query = $this->db->get('table-name');

Answer 2

I know that's an old question but I found a pretty simple solution.

//do your select, from and where
$this->db->select('your selects');
$this->db->from('your table');

$this->db->where('your where');

//get the filtered rows count
//the trick is the first empty parameter and second false parameter
$filtered_count = $this->db->count_all_results('', false);

//limit your results and get the rows
$this->db->limit($length, $start);
$results = $this->db->get()->result_array();

Hope it helps someone

Answer 3

$get_data = $this->your_model->get_data();
$data     = $get_data['data'];
$count    = $get_data['count'];

Model

function get_data($limit = 10, $offset= 0)
{
    $table = 'table-name';
    $where = array('Pool' => 1, 'Beedrooms >=' 3);

    $return['data']  = $this->db->from($table)->where($where)->limit($limit, $offset)->get();
    $return['count'] = $this->db->from($table)->where($where)->count_all_results();

    return $return;
}