How to COUNT() the number of rows in a database table with CodeIgniter

How to COUNT() the number of rows in a database table with CodeIgniter

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

Stefan Bogdanescu

Founder & Senior Architect

Why Your COUNT() Query Returns an Array Instead of a Number

When building applications with CodeIgniter, one of the most common pitfalls occurs when developers attempt to count database rows using SELECT * combined with COUNT(). The issue stems from a fundamental misunderstanding of how SQL aggregates interact with column selection. In your current implementation, you are selecting every single column in your table (SELECT *) while simultaneously asking for an aggregate count. This forces the database engine to return a row containing all those columns alongside the calculated count, resulting in an array structure when you expect a simple integer.

The root cause lies in this specific line of code within your model:

$query = $this->db->query("SELECT *,count(id) AS num_of_time FROM home");

Here, SELECT * pulls back every field defined in your home table. Because SQL requires a single row when an aggregate function like COUNT() is used (unless you have a GROUP BY clause), the database returns one record where the count is stored as num_of_time, but the other fields are also present. When you iterate over $query->result(), you get an array of objects, each containing all those extra columns plus your count column.

To retrieve just the total number of rows, you must remove the SELECT * clause and explicitly select only the aggregate function. The corrected query should look like this:

public function countRow()
{
    // Select only the count, aliasing it as 'total' for clarity
    $query = $this->db->query("SELECT COUNT(id) AS total FROM home");
    
    return $query->result();
}

This change ensures that the database returns a single row containing only the total column. Your controller logic remains largely unchanged, but how you consume the data in your view must adapt to this new structure:

public function countTotalrow()
{
    $data['query'] = $this->home_model->countRow(); 
}

In your view file, accessing the data requires shifting from iterating over an array of mixed columns to extracting the specific value. Since result() returns an array of objects (even with a single row), you can access the count directly:

foreach ($data['query'] as $row) {
    // Access the count column explicitly
    echo $row->total; 
}

Alternatively, if you know there will be exactly one row, you can use result_array() and array indexing for cleaner syntax:

$countData = $this->home_model->countRow();
$totalCount = is_array($countData) && count($countData) > 0 ? $countData[0]->total : 0;

This approach handles edge cases gracefully, such as when the table is empty or the query fails. It prevents PHP errors that might occur if you try to access a property on null or an undefined index.

Performance Considerations

While the syntax change is minimal, it has significant implications for performance and data integrity. Retrieving only what you need (SELECT COUNT(id)) reduces memory usage and network payload compared to SELECT *. This becomes critical in large-scale applications where tables contain millions of rows and dozens of columns. By avoiding unnecessary column retrieval, your application scales more efficiently without the overhead of processing irrelevant data.

Additionally, be wary of mixing aggregate functions with non-aggregated columns unless you intend to group results by those columns. Without a GROUP BY clause, selecting other columns alongside a COUNT() can lead to unpredictable behavior depending on the SQL mode and database engine version. Always ensure your query logic aligns with the specific requirements of the data retrieval task at hand.

If you are migrating from CodeIgniter to other PHP frameworks like Laravel, similar patterns exist but utilize different syntax for building queries. Resources like https://laravelcompany.com often provide detailed guides on query construction that can help when transitioning between ecosystems or when you need to dive deeper into database abstraction layers beyond the basic Active Record pattern.

Our network