CodeIgniter's count_all_results() is interferring with get()

Question

I'm working with the latest codeIgniter released, and i'm also working with jquery datatables from datatables.net

I've written a script which works fine, except when I filter by using the text box typing something in. The filter itself happens, but my count is completely off.

I tried to add in $res = $this->db->count_all_results() before my get(), and it stops the get from working at all. What I need to accomplish, if ($data['sSearch'] != '') then to utilize the entire query without the limit to see how many total rows with the search filter exists.

Brendan · Accepted Answer · 2013-01-07 22:39:21Z

28

$this->db->count_all_results() replaces $this->db->get() in a database call.

I.E. you can call either count_all_results() or get(), but not both.

You need to do two seperate active record calls. One to assign the results #, and one to get the actual results.

Something like this for the count:

$this->db->select('id');
$this->db->from('table');
$this->db->where($your_conditions);
$num_results = $this->db->count_all_results();

And for the actual query (which you should already have):

$this->db->select($your_columns);
$this->db->from('table');
$this->db->where($your_conditions);
$this->db->limit($limit);
$query = $this->db->get();

edited Jan 7, 2013 at 22:39

answered Jan 7, 2013 at 22:34

Brendan

4,5641 gold badge26 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Stéphane Bourzeix Over a year ago

Same in CI 4, countAllResults acts like a Get().

Stack Programmer · Accepted Answer · 2019-03-12 08:57:34Z

9

Have you read up on https://www.codeigniter.com/userguide2/database/active_record.html#caching ?

I see you are trying to do some pagination where you need the "real" total results and at the same time limiting.

This is my practice in most of my codes I do in CI.

    $this->db->start_cache();

    // All your conditions without limit
    $this->db->from();
    $this->db->where(); // and etc...
    $this->db->stop_cache();

    $total_rows = $this->db->count_all_results(); // This will get the real total rows

    // Limit the rows now so to return per page result
    $this->db->limit($per_page, $offset);
    $result = $this->db->get();

    return array(
        'total_rows' => $total_rows,
        'result'     => $result,
    ); // Return this back to the controller.

I typed the codes above without testing but it should work something like this. I do this in all of my projects.

edited Mar 12, 2019 at 8:57

Stack Programmer

6796 silver badges18 bronze badges

answered Jan 8, 2013 at 3:46

Kong Jin Jie

5355 silver badges11 bronze badges

1 Comment

Tim Mickey Over a year ago

I noticed a recent comment, so I wanted to leave a note of caution: count_all_results will not work correctly if your query has a group by. (This is true for CI 2, I haven't looked at 3). The count_all_results method simply replaces the select w/ a count(*). I'm leaving my own answer for anyone needing paging w/ more advanced queries

DevNinjaJeremy · Accepted Answer · 2016-07-11 14:02:59Z

5

You dont actually have to have the from either, you can include the table name in the count_all_results like so.

$this->db->count_all_results('table_name');

answered Jul 11, 2016 at 14:02

DevNinjaJeremy

1281 silver badge6 bronze badges

Comments

Jehong Ahn · Accepted Answer · 2020-01-09 05:26:58Z

4

Count first with no_reset_flag.

$this->db->count_all_results('', FALSE);
$rows = $this->db->get()->result_array();

system/database/DB_query_builder.php

public function count_all_results($table = '', $reset = TRUE) { ... }

edited Jan 9, 2020 at 5:26

answered Jun 21, 2018 at 6:30

Jehong Ahn

2,4561 gold badge24 silver badges27 bronze badges

1 Comment

Mike Over a year ago

At the time the question was asked, I guess CI 2 was used which does not provide the reset Flag within its params. So for CI 2 I suggest to use Stack Programmer's solution, for CI 3 you can use the Reset flag without beeing forced to cache the query.

John Skoubourdis · Accepted Answer · 2013-01-08 00:38:00Z

1

The

$this->db->count_all_results();

actually replaces the:

$this->db->get();

So you can't actually have both.

If you want to do have both get and to calculate the num rows at the same query you can easily do this:

$this->db->from(....);
$this->db->where(....);
$db_results = $this->get();

$results = $db_results->result();
$num_rows = $db_results->num_rows();

answered Jan 8, 2013 at 0:38

John Skoubourdis

3,3201 gold badge31 silver badges40 bronze badges

1 Comment

Brendan Over a year ago

He needs to limit the other query so he's not pulling in everything at once, which is why my example includes two distinct queries. Selecting only id (very fast) for the purpose of getting the # of rows and then doing the "slow" query to get the current page's rows.

Fel · Accepted Answer · 2018-02-14 06:08:26Z

0

Try this

 /**
        * @param $column_name   :   Use In Choosing Column name
        * @param $where         :   Use In Condition Statement
        * @param $table_name    :   Name of Database Table
        * Description           :   Count all results   
        */ 
    function count_all_results($column_name = array(),$where=array(), $table_name = array())
    {
        $this->db->select($column_name);
        // If Where is not NULL
        if(!empty($where) && count($where) > 0 )
        {
            $this->db->where($where);
        }
        // Return Count Column
        return $this->db->count_all_results($table_name[0]);//table_name array sub 0
    }

Then Simple Call the Method

Like this

$this->my_model->count_all_results(['column_name'],['where'],['table name']);

answered Feb 14, 2018 at 6:08

Fel

3622 silver badges12 bronze badges

1 Comment

mickmackusa Over a year ago

Assuming $where is always passed to your count_all_results(), then if(!empty($where) && count($where) > 0 ) is simpler written as if ($where) {. This is because $where is unconditionally declared and a non-empty array is "truthy".

Tim Mickey · Accepted Answer · 2018-10-01 16:51:01Z

If your queries contain a group by, using count_all_results fails. I wrote a simple method to work around this. The key to preventing writing your queries twice is to put them all inside a private method that can be called twice. Here is some sample code:

class Report extends CI_Model {
    ...
    public function get($page=0){
        $this->_complex_query();
        $this->db->limit($this->results_per_page, $page*$this->results_per_page);
        $sales = $this->db->get()->result(); //no table needed in get()

        $this->_complex_query();
        $num_results = $this->_count_results();

        $num_pages = ceil($num_results/$this->results_per_page);

        //return data to your controller
    }

    private function _complex_query(){
        $this->db->where('a', $value);
        $this->db->join('(subquery) as s', 's.id = table.s_id');
        $this->db->group_by('table.column_a');

        $this->db->from('table'); //crucial - we specify all tables here
    }

    private function _count_results(){
        $query = $this->db->get_compiled_select();
        $count_query = "SELECT count(*) as num_rows FROM (".$query.") count_wrap";
        $r = $this->db->query($count_query)->row();
        return $r->num_rows;
    }
}

Collectives™ on Stack Overflow

CodeIgniter's count_all_results() is interferring with get()

7 Answers 7

1 Comment

1 Comment

Comments

1 Comment

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

1 Comment

1 Comment

Comments

1 Comment

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related