Get number of returned rows from a CodeIgniter query

Get number of returned rows from a CodeIgniter query

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

Stefan Bogdanescu

Founder & Senior Architect

Get number of returned rows from a CodeIgniter query

When you execute a SELECT statement in CodeIgniter, the framework's Query Builder returns an object containing the results, but it doesn't automatically expose how many rows were retrieved unless you explicitly ask for it. This is particularly important when building pagination logic or validating that a specific number of records exists before processing them. You have two primary approaches to determine the row count: querying the database directly using a COUNT(*) clause or inspecting the result set returned by your initial query.

The most straightforward method involves appending a count() call to your Query Builder instance. This function executes a separate SQL statement that calculates the number of rows matching your conditions without retrieving the actual data columns. In CodeIgniter 3, you would typically access the query builder via $this->db or an alias defined in your model.

$this->db->select('*');
$this->db->from('users');
$this->db->where('status', 'active');

// Option 1: Count directly on the Query Builder object
$count = $this->db->count_all_results(); 

echo "Found " . $count . " active users.";

This approach is efficient because it executes a SELECT COUNT(*) query under the hood. However, there is a crucial distinction between count() and count_all_results(). The count() method in CodeIgniter's Query Builder returns an object containing the row count if you have previously called get(), or it can execute a separate count query depending on your specific version and configuration. In most standard scenarios, count_all_results() is the safer bet to ensure a scalar integer is returned immediately without loading data into memory first.

If you prefer not to execute an extra database round-trip and your dataset fits comfortably in memory, you can inspect the result set from your main query. CodeIgniter returns an array of objects or associative arrays depending on your configuration. You can use the count() PHP function directly on the returned object.

$builder = $this->db->select('name, email')
                    ->from('users')
                    ->where('status', 'active');

$results = $builder->get()->result(); // Or result_array()

echo "Total rows: " . count($results);

While this method is perfectly valid for small datasets, it loads every row into PHP memory before you can calculate the size. For large tables, this approach wastes resources and increases execution time. Additionally, if your query uses complex joins or subqueries that might return duplicate records based on your SELECT columns (though rare with primary keys), count($results) will reflect the actual array length, which might differ from a strict unique row count if duplicates exist in the result set.

Another common pitfall is assuming that num_rows() works the same way it does in raw MySQLi or PDO connections when used on CodeIgniter's DB class. The native $this->db->query() method returns a query object where $result->num_rows might not be populated unless you have fetched at least one row first. If you fetch all rows using get(), the result object will contain a num_rows property, but relying on this requires that you actually retrieve the data.

// Using raw query via CodeIgniter DB class
$sql = "SELECT * FROM users WHERE status = 'active'";
$query = $this->db->query($sql);

if ($query->num_rows() > 0) {
    // Process results
}

Here, num_rows() is a property of the query result object. It returns an integer representing the number of rows in the result set. This is useful if you are working with raw SQL strings but still want to leverage CodeIgniter's database abstraction layer for connection management and portability.

For high-performance applications where you only need the count, avoiding data retrieval entirely is always the preferred strategy. Using count_all_results() ensures that your application scales better as your user base grows, preventing unnecessary memory spikes caused by loading millions of rows just to check how many exist. If you are migrating from an older version of CodeIgniter or switching between 2.x and 3.x, note that method signatures for counting have remained relatively stable, but always verify the specific API documentation for your target version to avoid deprecated method usage.

Our network