Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
578 views
in Technique[技术] by (71.8m points)

php - codeigniter active record get query and query without the LIMIT clause

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;
}    
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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;

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...