Welcome Guest, Not a member yet? Register   Sign In
count_all_results() not returning correct result
#1

[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;
#2

[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?
#3

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

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

[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
#6

[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
#7

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

[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');
...
#9

[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.
#10

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




Theme © iAndrew 2016 - Forum software by © MyBB