blocks|key|3583884|text|$get_data+=+$this->your_model->get_data();
$data+++++=+$get_data['data'];
$count++++=+$get_data['count'];|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|3583885|模型|unstyled|3583886|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;
}|3583887|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|L|8|@]|9|@]|A|$]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$B|C]]|$1|I|3|-4|5|F|7|N|8|@]|9|@]|A|$]]]|J|$]]

<pre><code>$get_data = $this-&gt;your_model-&gt;get_data();
$data = $get_data['data'];
$count = $get_data['count'];
</code></pre>

Model

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

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

 return $return;
}
</code></pre>

blocks|key|3605762|text|很明显，您需要使用两个不同的查询。使用单个查询尽可能快地完成这一点是最好的，但是由于您需要在第二个查询之前获取所有记录，所以我们需要使用两个查询。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3605763|但是，可以根据与MySQL一起使用的引擎优化第一个查询。如果使用InnoDB，则应该使用SELECT+COUNT(*)+FROM+<table-name>，因为总行大小被缓存在InnoDB中。|offset|length|style|CODE|3605764|我相信count_all_rows使用count(*)来实现性能，应该直接使用它对您进行排序。|3605765|使用MyISAM，您可以使用COUNT(<column-name>)。|3605766|因此，在模型类中有一个count函数，它返回表的计数，然后调用该函数从数据库插入/update/get数据。|3605767|entityMap^0|0|18|X|0|3|E|J|8|0|E|K|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Q|8|@$D|R|E|S|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|T|8|@$D|U|E|V|F|G]|$D|W|E|X|F|G]]|9|@]|A|$]]|$1|J|3|K|5|6|7|Y|8|@$D|Z|E|10|F|G]]|9|@]|A|$]]|$1|L|3|M|5|6|7|11|8|@]|9|@]|A|$]]|$1|N|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|O|$]]

It is quite evident that you would need to use two different queries. It would be optimum to do this as quickly as possible using a single query, but since you need to get all the records before the second query, we need to use two queries. 

However, you can optimize the first query based on the engine you use with MySQL. If you use InnoDB then you should use <code>SELECT COUNT(*) FROM &lt;table-name&gt;</code> cause the total row size is cached in InnoDB.

I believe <code>count_all_rows</code> uses <code>count(*)</code> for performance and you should be sorted using this direcctly.

With MyISAM you can use <code>COUNT(&lt;column-name&gt;)</code>.

So, you have a count function in your model class which returns the count for your table and then you can call the function to insert/update/get data from your database.

blocks|key|4463788|text|我知道这是个老问题，但我遇到了这个问题，想出了一个不同的解决方案。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4463789|这样做的目的是在限制和偏移之前获取db类的副本。|4463790|$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');|code-block|syntax|javascript|4463791|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

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. 

<pre><code>$this-&gt;db-&gt;where('Pool', "1");
$this-&gt;db-&gt;where('Bedrooms &gt;=', "3");

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

$this-&gt;db-&gt;limit($limit, $offset);
$query = $this-&gt;db-&gt;get('table-name');
</code></pre>

blocks|key|4463804|text|++++$this->db->select('*');
++++$this->db->from('users');
++++$this->db->where('active',$status);

++++//open1+here+we+copy+$this->db+in+to+tempdb+and+apply+
++++//count_all_results()+function+on+to+this+tempdb
++++$tempdb+=+clone+$this->db;
++++$num_results=+$tempdb->count_all_results();+++

++++//+now+applying+limit+and+will+get+actual+result+
++++$this->db->limit(10);

++++$this->db->get();
++++$query+=+$this->db->last_query();
++++$res+=+$this->db->query($query);++++++++

++++$data_array+=+array('num_results'+=>+$num_results,+'results'+=>+$res->result()+);
++++return+$data_array;|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|4463805|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code> $this-&gt;db-&gt;select('*');
 $this-&gt;db-&gt;from('users');
 $this-&gt;db-&gt;where('active',$status);

 //open1 here we copy $this-&gt;db in to tempdb and apply 
 //count_all_results() function on to this tempdb
 $tempdb = clone $this-&gt;db;
 $num_results= $tempdb-&gt;count_all_results(); 

 // now applying limit and will get actual result 
 $this-&gt;db-&gt;limit(10);

 $this-&gt;db-&gt;get();
 $query = $this-&gt;db-&gt;last_query();
 $res = $this-&gt;db-&gt;query($query); 

 $data_array = array('num_results' =&gt; $num_results, 'results' =&gt; $res-&gt;result() );
 return $data_array;
</code></pre>

blocks|key|3584046|text|您可以使用mysql的SQL_CALC_FOUND_ROWS|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3584047|SELECT+SQL_CALC_FOUND_ROWS+*+FROM+table1
WHERE
++cond1,+cond2,+...,+condN
LIMIT+10|code-block|syntax|javascript|3584048|选择FOUND_ROWS()；|3584049|这是使用mysql，您可能需要检查如何使用它的代码点火器的方式。|3584050|参考资料：|3584051|found-rows|3584052|entityMap|0|LINK|mutability|MUTABLE|url|https://dev.mysql.com/doc/refman/5.7/en/information-functions.html#function_found-rows^0|B|J|0|0|0|0|0|0|A|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@$9|11|A|12|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|13|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|14|8|@]|D|@]|E|$]]|$1|M|3|N|5|6|7|15|8|@]|D|@]|E|$]]|$1|O|3|P|5|6|7|16|8|@]|D|@]|E|$]]|$1|Q|3|R|5|6|7|17|8|@]|D|@$9|18|A|19|1|1A]]|E|$]]|$1|S|3|-4|5|6|7|1B|8|@]|D|@]|E|$]]]|T|$U|$5|V|W|X|E|$Y|Z]]]]

You can use <code>SQL_CALC_FOUND_ROWS</code> of mysql

<pre><code>SELECT SQL_CALC_FOUND_ROWS * FROM table1
WHERE
 cond1, cond2, ..., condN
LIMIT 10
</code></pre>

SELECT FOUND_ROWS();

This is using mysql, you may need to check how to use it codeignitor way.

Reference:

<a href="https://dev.mysql.com/doc/refman/5.7/en/information-functions.html#function_found-rows" rel="nofollow noreferrer">https://dev.mysql.com/doc/refman/5.7/en/information-functions.html#function_found-rows</a>

blocks|key|3584089|text|我知道这是个老问题，但我找到了一个很简单的解决办法。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3584090|//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();|code-block|syntax|javascript|3584091|希望它能帮到别人|3584092|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

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

<pre><code>//do your select, from and where
$this-&gt;db-&gt;select('your selects');
$this-&gt;db-&gt;from('your table');

$this-&gt;db-&gt;where('your where');

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

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

Hope it helps someone

blocks|key|3584107|text|我知道这个问题是老的，但我有同样的问题，得到了一个比这里显示的更简单的解决方案。不需要构建两次查询，也不需要克隆db+class。在添加where部件之后，只需计算结果而不需要查询生成器，然后添加limit并执行查询。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3584108|$this->db->where('Pool',+1);
$this->db->where('Beedrooms+>='+3);
$count+=+$this->db->count_all_results('table-name',+false);+//+No+reset+query+builder
$this->db->limit($limit,+$offset)
$result+=+$this->db->get();|code-block|syntax|javascript|3584109|您将有两个变量:结果数为$count，结果为$result。|3584110|entityMap^0|1K|8|1W|5|2Q|5|0|0|C|6|M|7|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]|$9|R|A|S|B|C]|$9|T|A|U|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|V|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|W|8|@$9|X|A|Y|B|C]|$9|Z|A|10|B|C]]|D|@]|E|$]]|$1|M|3|-4|5|6|7|11|8|@]|D|@]|E|$]]]|N|$]]

I know this question is old but I had the same issue and got a simpler solution than the displayed here. No need to build the query twice and no need to clone the <code>db class</code>. Just count the result without resting the query builder after you add the <code>where</code> part and then you add the <code>limit</code> and execute your query.

<pre><code>$this-&gt;db-&gt;where('Pool', 1);
$this-&gt;db-&gt;where('Beedrooms &gt;=' 3);
$count = $this-&gt;db-&gt;count_all_results('table-name', false); // No reset query builder
$this-&gt;db-&gt;limit($limit, $offset)
$result = $this-&gt;db-&gt;get();
</code></pre>

You are going to have two variables:
<code>$count</code> with the number of results and <code>$result</code> with the result.

I have ran into a problem...

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

<pre><code>$this-&gt;db-&gt;where('Pool', "1");
$this-&gt;db-&gt;where('Bedrooms &gt;=', "3");
</code></pre>

Then a limit statement

<pre><code>$this-&gt;db-&gt;limit($limit, $offset);
</code></pre>

And finally my get statement

<pre><code>$query = $this-&gt;db-&gt;get('table-name');
</code></pre>

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..

<pre><code>$this-&gt;db-&gt;where('Pool', "1");
$this-&gt;db-&gt;where('Bedrooms &gt;=', "3");

$num_rows = $this-&gt;db-&gt;count_all_results();

$this-&gt;db-&gt;limit($limit, $offset);
$query = $this-&gt;db-&gt;get('table-name');
</code></pre>

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!

Count rows before adding a further limit statement CodeIgniter?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我遇到了一个问题..。我有很多这样的状态.$this->db->where('Pool', "1");$this->db->where('Bedrooms >=', "3");然后是限位语句$this->db->limit($limit, $offset);最后我的get声明$query = $this->db->ge...

问在添加进一步的限制语句CodeIgniter之前对行进行计数？
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在添加进一步的限制语句CodeIgniter之前对行进行计数？EN