+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: count_all_results() produces invalid result with group by clause (/showthread.php?tid=23430)
count_all_results() produces invalid result with group by clause - El Forum - 10-10-2009
[eluser]tahpot[/eluser]
Hi,
This bug occurs with the MySQL driver, most likely with others as well.
Codigniter assumes that calling "count (*)" over a query will always produce the total number of results for the given query (see $_count_string in mysql_driver.php). This is incorrect. When using "group by", count(*) doesn't produce a count of all the rows, it produces a result set, counting the number of entries for the given group by column(s).
To fix this I suggest that that the count_all_results() method should be overridden by the respective driver and implement a more driver specific solution (in this case with MySQL, make use of the SQL_CALC_FOUND_ROWS, possibly performing the query with limit = 1 first to get the value).
While I'm here, I also suggest adding a second parameter to count_all_results(), $reset_select=true. That way, it's possible to fetch the number of rows for a query and then fetch the results without having to completely rebuild the active record class.
Cheers,
Chris
count_all_results() produces invalid result with group by clause - El Forum - 06-15-2011
[eluser]Unknown[/eluser]
+1
Has this post even been seen?
count_all_results() produces invalid result with group by clause - El Forum - 07-10-2012
[eluser]Unknown[/eluser]
I sort of have a solution:
@tahpot you've suggested adding $reset to count_all_results.
I've added the $reset parameter to the get() method:
Code:
public function get($table = '', $limit = null, $offset = null, $reset = true)
{
...
}now what i do is:
Code:
$this->db->select(...);
$this->db->from(...);
$this->db->group_by(...);
$query = $this->db->get('',null,null,false); // added the "false" here
echo $query->num_rows();it's not as pretty but it does the job and since we didn't run $query->result() it does not fetch all results (though it still executes the full select query).