• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
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...


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.