codeigniter 活动记录获取查询和不带 LIMIT 子句的查询 [英] codeigniter active record get query and query without the LIMIT clause
问题描述
我正在使用活动记录,一切正常,但我想将 $data["totalres"] 设置为总结果,我的意思是,相同的查询但没有 LIMIT
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
问题是当您执行查询修饰符时,前面的语句未设置,所以我什至无法在获得结果后添加 $this->db->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;
}
推荐答案
您可以使用 SQL_CALC_FOUND_ROWS
以获取将返回的行数 sans-LIMIT
.注意 select
行中的 ,FALSE
.这告诉 CodeIgniter 不要试图用反引号转义 SELECT
子句(因为 SQL_CALC_FOUND_ROWS
不是一个字段,而 CodeIgniter 没有意识到这一点).
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;
这篇关于codeigniter 活动记录获取查询和不带 LIMIT 子句的查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!