count_all_results

[eluser]ccachor[/eluser]
I saw this functionality was put into the SVN and there was some documentation on it but it was kind of thin.

How would I integrate this into my pagination? I'm doing some search forms and I can't get $this->db->count_all_results to work in that configuration.

Code:

// Controller

    function search()
    
    {
    
    //Load Pagination
    
            $data['make'] = $this->uri->segment(3);
            $data['model'] = $this->uri->segment(4);
    
            $this->load->library('pagination');
            $config['base_url'] = base_url().'inventory/search/'.$data['make'].'/'.$data['model'].'/';
            $config['total_rows'] = $this->db->count_all_results();
            $config['per_page'] = '10';
            $config['num_links'] = 5;
            $config['full_tag_open'] = '<p>';
            $config['full_tag_close'] = '</p>';
            $this->pagination->initialize($config);
            $paginate = $this->pagination->create_links();
            
    //Load Model
            $this->load->model('Inventory_model');
            $data['query'] = $this->Inventory_model->searchInventory($this->uri->segment(3), $this->uri->segment(4), $config['per_page'], $this->uri->segment(5));
            
            
    //Load View
            
            $this->load->view('inventory_view', $data);    
    }

Code:

// Model

    function searchInventory($make, $model, $num, $offset)
    
      {
            
        $this->db->select('*');
        $this->db->from('inventory');
        $this->db->where('make', $this->uri->segment(3));
        $this->db->where('model', $this->uri->segment(4));
        $this->db->limit($num, $offset);
        $this->db->orderby('sellingprice', 'asc');
        $this->db->count_all_results();

                
        $query = $this->db->get();
        return $query;
     }

I'm just not sure where count_all_results fits in with my query and pagination. Thanks.

[eluser]Kinsbane[/eluser]
Since your query is only retrieving the number of current records for the $num and $offset, your count_all_rows() function will only return a count of whatever $num is - you'll need to a run another query, something like "SELECT COUNT(*) As Total WHERE BLAH='BLEH'";

[eluser]Edemilson Lima[/eluser]
If you use COUNT(*) with GROUP BY in your query, the database will not return just one row, but one row with the total of each group. In this case, it is necessary to fetch all the rows and sum the subtotals to get the the real total rows returned by the query. Example:

Code:

$records=mysql_num_rows($result);
if($records==1) { // no groups where used
  $row=mysql_fetch_array($result);
  $num_rows=intval($row[0]);
  return $num_rows;
} elseif($records>1) { // query with group
  $total_rows=0;
  while($row=mysql_fetch_array($result)) {
    $total_rows+=intval($row[0]);
  }
  return $total_rows;
}