count_all_results() not returning correct result

count_all_results() not returning correct result - Printable Version

+- 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() not returning correct result (/showthread.php?tid=15316)

count_all_results() not returning correct result - El Forum - 01-31-2009

[eluser]CI Newbie Guru[/eluser]
Hi,

I am using count_all_result() method to count the total records for pagination but it is not returning correct number of records. It returns something like 144 but results are only 26. I have copied my code below, please let me know where I am going wrong.

Code:
$to        =    $this->config->item('per_page_report');

        $this->db->start_cache();

        $this->db->select('c.*, count(b.bidId) as totalBids');

        $this->db->from('customer c');

        $this->db->join('bids b', 'b.customerId = c.customerId', 'left');

        //$this->db->order_by('totalBids', 'DESC'); //returns error that totalBids is not found

        $this->db->group_by('c.customerId');

        $config['base_url']     = "/index.php/admin/reports/bids/history/";

        $config['per_page']     = $this->config->item('per_page_report');

        $config['cur_page']     = $offset;

        $config['total_rows']     = $this->db->count_all_results();

        $query = $this->db->get(null, $to, $offset);

        $this->db->flush_cache();

        $this->pagination->initialize($config);

        $this->layout['pagination'] = $this->pagination->create_links();

        $this->layout['pages'] = '1 of 1';

        $tokens = ($query->num_rows() > 0) ? $query->result() : false;

        return $tokens;

count_all_results() not returning correct result - El Forum - 01-31-2009

[eluser]darkhouse[/eluser]
I've never used count_all_results before, not sure how it works exactly, but I wonder if it's ignoring the group by?

count_all_results() not returning correct result - El Forum - 01-31-2009

[eluser]Colin Williams[/eluser]
Use $query->num_rows(), not count_all_results()

count_all_results() not returning correct result - El Forum - 02-01-2009

[eluser]CI Newbie Guru[/eluser]
how can i use $query->num_rows() before I run query?

count_all_results() not returning correct result - El Forum - 02-01-2009

[eluser]Computerzworld[/eluser]
you can check your query by echoing it using $this->db->last_query() exactly after $this->db->count_all_results().. And check whether your count query is correct or not... You can refer to the syntax of $this->db->count_all_results() from here

count_all_results() not returning correct result - El Forum - 02-01-2009

[eluser]CI Newbie Guru[/eluser]
Thanks, I will try that. I have used count_all_results in other queries without problem but when I use it with group_by then it doesn't give the correct number of results.

Can you please advice if there is any know issue relating to using count_all_results with group_by.

thanks

count_all_results() not returning correct result - El Forum - 02-01-2009

[eluser]Colin Williams[/eluser]
I don't see anything that is keeping you from running the query first.

count_all_results() not returning correct result - El Forum - 02-01-2009

[eluser]akkumaru[/eluser]
[quote author="CI Newbie Guru" date="1233469523"]Hi,

Code:
$this->db->select('c.*, count(b.bidId) as totalBids');

        $this->db->from('customer c');

        $this->db->join('bids b', 'b.customerId = c.customerId', 'left');

        //$this->db->order_by('totalBids', 'DESC'); //returns error that totalBids is not found

        $this->db->group_by('c.customerId');

[/quote]

sorry,,off the topic,
but i suppose you cannot order a query result by an alias,,
you may write this instead:

Code:
...

$this->db->order_by(count(b.bidId), 'desc');

...

count_all_results() not returning correct result - El Forum - 10-07-2013

[eluser]alphastudio[/eluser]
This is an old thread but I am having the same issue.

The trouble is the count_all_results method uses a query that starts with SELECT COUNT(*) AS `numrows`..., so if your query uses GROUP BY you don't get a count of all results, but a count of each group, and the returned count is whichever comes first.

For instance :

Code:
$this->db->from( 'table1' );

$this->db->join('table2', 'table1.id = table2.id' );

$this->db->group_by( 'table2.something' );

$this->db->count_all_results();

will run this query:

Code:
SELECT COUNT(*) AS `numrows`

FROM (`table1`)

LEFT JOIN `table2` ON `table1`.`id` = `table2`.`id`

GROUP BY `table2`.`something`

returning something like:

| numrows |
| 6 |
| 1 |
| 1 |
| 3 |

And so count_all_results will return 6, instead of 4, which would be the number of rows returned by the join query.

Any idea on how to get a real count of all results when using group by ?

I am, like the original poster, meaning to count the total records for pagination, when only retrieving the records for the current page.

count_all_results() not returning correct result - El Forum - 10-07-2013

[eluser]alphastudio[/eluser]
I found my own solution.

Something like this does the trick:

Code:
$this->db->select( 'SQL_CALC_FOUND_ROWS, `table1`.`id`, `table2`.`something`', false );

$this->db->from( 'table1' );

$this->db->join('table2', 'table1.id = table2.id' );

$this->db->group_by( 'table2.something' );

$this->db->limit( 25, 0 ); 

$result = $this->db->get();

$total_results = $this->db->query( 'SELECT FOUND_ROWS() total' )->row()->total;

As a reminder, the purpose of this is retrieving a certain amount of rows to display in a paginated list, while also getting the total amount of rows. For instance, I might be displaying the 25 first rows out of 150 total rows (which would be $total_results).

I hope this helps someone else, I'm not very good at this sample code thing...