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

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

  1. Blog
  2. 15 November 2026
  3. 3 min read

Stefan Bogdanescu

Founder & Senior Architect

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

When integrating DataTables from datatables.net into a CodeIgniter application, the immediate challenge often lies in synchronizing server-side pagination and filtering with accurate row counts. The scenario described involves a script that functions correctly until a text filter is applied to the dataset, at which point the row count becomes inaccurate or causes the subsequent data retrieval (get()) to fail entirely.

The core issue stems from how CodeIgniter's Active Record handles query states. Specifically, calling $this->db->count_all_results() on an active query object does not merely return a number; it executes the SQL query against the database and, more critically, modifies the internal state of the query builder instance. In many versions of the framework, once count_all_results() is invoked, the underlying query string is altered or reset to reflect the count operation rather than the original SELECT statement used for pagination.

If you attempt to call get() immediately after count_all_results(), you may encounter an empty result set or a total failure to fetch data because the framework assumes the primary query has already been fully executed or discarded. The user's observation that adding count_all_results() stops the get() from working is a direct consequence of this state mutation. The framework's database class treats the query as "finished" after the count, making it impossible to reuse the same object for the actual data retrieval without reconstructing the query logic.

To resolve this discrepancy while maintaining the filtering capabilities of DataTables, you must decouple the counting operation from the data retrieval logic. Instead of chaining these calls on a single query instance, construct two distinct queries based on your filter conditions.

First, build the standard query for pagination and data fetching. This is typically where you define your from, join, and any search parameters derived from $data['sSearch']. If a search term exists, ensure it is appended to the where clause using an or_like() or where() method before applying the limit and offset.

$sql = $this->db->select('*')
    ->from('table_name')
    ->order_by('id', 'desc');

// Apply search filter if present
if (!empty($data['sSearch'])) {
    $sql->where_like('column_name', $data['sSearch']);
}

// Get the paginated data
$query = $sql->limit(10, $this->pagination->page);
$data['rows'] = $query->result();

Next, handle the count separately. Create a fresh query object that mirrors the where conditions of your main query but excludes the limit() and offset() clauses. This ensures you are counting total matching rows against the active filters without interfering with the pagination logic.

$count_query = $this->db->select('COUNT(*) as count')
    ->from('table_name');

// Apply the same search filter to the count query
if (!empty($data['sSearch'])) {
    $count_query->where_like('column_name', $data['Ssearch']);
}

// Execute only for the count
$count_result = $count_query->get(); 
$total_rows = $count_result->row()->count;

This approach guarantees that get() receives a clean, unmodified query object capable of returning paginated results, while count_all_results() operates on a dedicated instance to provide the accurate total for DataTables' rendering. By separating these concerns, you eliminate the state corruption issue and ensure that your table displays the correct number of rows and pages regardless of the search input.

For developers migrating between frameworks or optimizing complex database interactions, this pattern of isolating count logic from data retrieval is a best practice to avoid subtle bugs related to query state management. If you require further guidance on optimizing database queries in PHP ecosystems, resources like https://laravelcompany.com often provide insights into modern ORM patterns that can simplify these architectural decisions.

Our network