CODEWITHSUNDEEP
codeigniteractiverecord
Johnny
Johnny

Reputation: 851

codeigniter count_all_results

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

I've written this function: https://gist.github.com/4478424 which, as is 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.

If you need to see any other code other than whats in my gist, just ask and I will go ahead and post it.

Upvotes: 11

Views: 66073

Answers (7)

Jehong Ahn
Jehong Ahn

Reputation: 2416

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) { ... }

Upvotes: 4

Kong Jin Jie
Kong Jin Jie

Reputation: 535

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.

Upvotes: 9

Tim Mickey
Tim Mickey

Reputation: 361

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;
    }
}

Upvotes: 0

Fel
Fel

Reputation: 362

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']);

Upvotes: 0

DevNinjaJeremy
DevNinjaJeremy

Reputation: 138

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');

Upvotes: 5

John Skoubourdis
John Skoubourdis

Reputation: 3289

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();

Upvotes: 1

Brendan
Brendan

Reputation: 4565

$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();

Upvotes: 28

Related Questions