codeigniter active record get query and query without the LIMIT clause

Question

im using active record, its all working ok but i want to set the $data["totalres"] to the total results, i mean, the same query but without the LIMIT

the problem is the previous statements gets unset when you do a query modifier, so i cant even add the $this->db->limit() after i get the results.

any ideas? i think its a bad practice to 'duplicate' the query just to do this

function get_search($start, $numrows, $filter = array())
{    

    ...

    $this->db
    ->select("emp")
    ->from('emp')
    ->join('empr', 'empr.b = empr.id', 'left')
    ->like('code', $code)
    ->limit($numrows, $start);

    ...

    $q = $this->db->get();        

    // number of rows WITHOUT the LIMIT X,Y filter
    $data["totalres"] = ???????;        

    if ($q->num_rows() > 0)
    {        
        $data["results"] = $q->result();
    } else {
        $data["results"] = array();
    }   

    return $data;
}    

Solution 1

You can use SQL_CALC_FOUND_ROWS to get the number of rows that would have been returned sans-LIMIT. Note the ,FALSE in the select line. This tells CodeIgniter not to try to escape the SELECT clause with backticks (because SQL_CALC_FOUND_ROWS is not a field, and CodeIgniter doesn't realize that).

$this->db
->select("SQL_CALC_FOUND_ROWS emp", FALSE)
->from('emp')
->join('empr', 'empr.b = empr.id', 'left')
->like('code', $code)
->limit($numrows, $start);

$q = $this->db->get();

Then after that query is ran, we need run another query to get the total number of rows.

$query = $this->db->query('SELECT FOUND_ROWS() AS `Count`');
$data["totalres"] = $query->row()->Count;

Solution 2

Try this:

function get_search($start, $numrows, $filter = array()){    
    $tmp= $this->db
    ->select("emp")
    ->from('emp')
    ->join('empr', 'empr.b = empr.id', 'left')
    ->like('code', $code)
    ->_compile_select();

    $q= $this->db->limit($numrows, $start)->get();

    // number of rows WITHOUT the LIMIT X,Y filter

    $data["totalres"] = $this->db->query($tmp)->num_rows();

    if ($q->num_rows() > 0){        
        $data["results"] = $q->result();
    } else {
        $data["results"] = array();
    }   
    return $data;
}    

Solution 3

I would actually suggest the use of CIs query caching.

To use this, you start the cache, build the query without the limits in place. Run your query to get the full count, then apply the limit and run the query to get your list for your page with the offset you need.

The cache will remember the variables that have been defined.

You can then clear the cache for subsequent queries.

Example

    $this->db->start_cache();

    // Where, like, having clauses in here

    $this->db->stop_cache();

    $count = $this->db->get('table')->num_rows();

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

    $result = $this->db->get('table')->result_array();

    $this->db->flush_cache();