How to do a num_rows() on COUNT query in codeigniter?

How to do a num_rows() on COUNT query in codeigniter?

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

Stefan Bogdanescu

Founder & Senior Architect

The num_rows() Quirk in CodeIgniter and How to Handle Aggregates

In legacy frameworks like CodeIgniter, developers often encounter a subtle but persistent discrepancy when dealing with aggregate queries. You can run a standard SELECT id query and immediately call $query->num_rows() to get the result count. However, when you switch to an aggregated query using COUNT(*), that same method frequently returns zero or fails to behave as expected. Understanding why this happens requires looking past the surface-level API design into how CodeIgniter's Query Builder handles raw SQL execution versus its internal object structures.

The root cause lies in how CodeIgniter interprets the result set of a query executed via $this->db->query(). When you execute a query like SELECT id FROM users WHERE account_status = 'active', the database returns a list of rows, each containing one or more columns. CodeIgniter's result object inspects this collection to determine the row count. It iterates through the internal array of results to calculate the total.

However, when you execute SELECT COUNT(*) FROM users..., the database driver returns a single row where that column contains the integer sum (e.g., 42). While logically this is one row in the result set, CodeIgniter's legacy result wrapper sometimes struggles to interpret this specific scalar return type correctly within the context of raw SQL execution. Depending on the underlying database driver (MySQLi or PDO) and the specific version of CodeIgniter, the internal logic might assume that a COUNT query returns zero actual "data rows" for iteration purposes, causing num_rows() to return 0 even though the data is there.

Retrieving the Count Correctly

To reliably get the count from an aggregate query in CodeIgniter, you should avoid relying on $query->num_rows(). Instead, fetch the single row returned by the database and cast the value to an integer. This approach works regardless of whether the underlying driver treats the result as a scalar or a single-row object.

Here is the recommended implementation pattern:

$sql = "SELECT COUNT(*) as total FROM `users` WHERE `account_status` = '" . $this->escape($i) . "'";
$query = $this->db->query($sql);

// Fetch the first (and only) row
$row = $query->row();

// Cast to integer for safety and consistency
$total_users = (int) $row->total;

var_dump($total_users);

By explicitly fetching the row and accessing the total alias, you bypass the ambiguity of num_rows(). If you prefer not to alias the column in your SQL, you can still access it via row_array()['COUNT(*)'], though using an alias is generally cleaner for readability.

Performance Considerations

A common follow-up question regarding this approach is whether executing a SELECT COUNT(*) query is inferior to selecting specific columns and counting them in PHP (or the ORM layer). The short answer is no; in fact, it is usually the superior choice.

Database engines are highly optimized for aggregation operations. When you ask MySQL or PostgreSQL to run COUNT(*), the database server often utilizes specialized data structures like index statistics (for COUNT(DISTINCT col)) or table metadata (for COUNT(*) on tables without complex filtering) to answer the query almost instantly without scanning every single row in memory.

Conversely, if you were to write a raw SQL query selecting all IDs and then attempt to count them in PHP using count($ids_array), you would be transferring potentially millions of rows over the network. This consumes significant bandwidth and memory on your application server before you even reach the counting logic. Furthermore, CodeIgniter's ORM or Query Builder typically builds these queries as standard SQL statements anyway; there is no native "load all into array then count" mechanism that avoids a database round-trip unless you are manually fetching every record.

Therefore, running SELECT COUNT(*) directly is not just a workaround for the API limitation; it is the architecturally correct decision for performance. It leverages the database engine's strengths and minimizes data transfer between the server and your application code.

Our network