codeigniter active record get query and query with

问题:

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;
}    

回答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;

回答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;
}    

回答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();

回答4:

$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();

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

CodeIgniter 2.1.0 not run, below code will fix it.

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