PHP CodeIgniter Count Rows in Table

Introduction

When developing a web application using PHP CodeIgniter, one of the most common database operations is counting the number of rows in a table. Whether you're displaying the total number of registered users, published blog posts, completed orders, or active products, row counting plays an essential role in building dashboards, reports, and analytics.

Fortunately, CodeIgniter provides simple and efficient methods to count records without writing complex SQL queries. Its Query Builder class makes counting rows secure, readable, and easy to maintain.

In this guide, you'll learn different ways to count rows in a database table using CodeIgniter, including counting all records, counting records with conditions, grouping data, and improving performance. Whether you're a beginner or an experienced developer, this tutorial will help you understand the best practices for counting rows efficiently.

 

Why Count Rows in CodeIgniter?

Counting rows helps developers display meaningful information throughout an application. Some common use cases include:

• Total registered users

• Number of products

• Active customers

• Pending orders

• Completed transactions

• Blog post count

• Employee records

• Student database reports

• Inventory management

• Dashboard statistics

Instead of retrieving every record from the database, counting rows directly improves application performance.

 

Prerequisites

Before getting started, ensure you have:

• PHP installed

• CodeIgniter project setup

• MySQL database

• Database configuration completed

• A sample database table

Example table:

users

 

-------------------------------------

id

name

email

status

created_at

Suppose this table contains multiple user records.

 

Method 1: Count All Rows Using countAll()

One of the easiest ways to count records is by using countAll().

Example

$this->db->count_all('users');

Output

250

This returns the total number of records available in the users table.

Explanation

The function directly executes an SQL query similar to:

SELECT COUNT(*) FROM users;

It is simple, fast, and ideal when you need the total number of rows.

 

Method 2: Count Rows Using Query Builder

Another popular method is using Query Builder.

$this->db->from('users');

$count = $this->db->count_all_results();

Output

250

This method becomes more useful when conditions are applied before counting.

 

Method 3: Count Rows with WHERE Condition

Often, developers only need specific records.

Example:

Count active users.

$this->db->where('status',1);

$this->db->from('users');

$count = $this->db->count_all_results();

Generated SQL

SELECT COUNT(*)

FROM users

WHERE status=1;

Output

180

This method is commonly used in admin dashboards.

Examples include:

• Active users

• Paid customers

• Approved comments

• Delivered orders

• Published articles

 

Method 4: Count Rows Using get()->num_rows()

Another approach is retrieving records first and then counting them.

Example

$query = $this->db->get('users');

 

$count = $query->num_rows();

Output

250

Although this works, it is not recommended for large datasets because all records are fetched before counting them.

 

Difference Between count_all() and num_rows()

count_all()	num_rows()
Counts directly in database	Counts after fetching records
Faster	Slower for large tables
Uses COUNT(*)	Retrieves complete result
Better performance	Higher memory usage

For large projects, always prefer count_all() or count_all_results().

 

Method 5: Count Rows with Multiple Conditions

Sometimes multiple filters are required.

Example

$this->db->where('status',1);

$this->db->where('country','India');

 

$this->db->from('users');

 

$count = $this->db->count_all_results();

SQL

SELECT COUNT(*)

FROM users

WHERE status=1

AND country='India';

This is useful for reports based on multiple criteria.

 

Method 6: Count Rows Using LIKE

Suppose you want to count users whose names start with "A".

$this->db->like('name','A','after');

$this->db->from('users');

 

$count = $this->db->count_all_results();

Equivalent SQL

SELECT COUNT(*)

FROM users

WHERE name LIKE 'A%';

Useful examples include:

• Product search

• Customer search

• Employee lookup

• Blog title filtering

 

Method 7: Count Rows Between Dates

Many applications display reports based on date ranges.

Example

$this->db->where('created_at >=','2026-01-01');

$this->db->where('created_at <=','2026-12-31');

 

$this->db->from('users');

 

$count = $this->db->count_all_results();

SQL

SELECT COUNT(*)

FROM users

WHERE created_at

BETWEEN '2026-01-01'

AND '2026-12-31';

Useful for:

• Monthly registrations

• Daily reports

• Annual statistics

• Sales reports

 

Method 8: Count Rows Using Model

Keeping database logic inside models makes your application cleaner and easier to maintain.

Model

class UserModel extends CI_Model

{

 

   public function totalUsers()

   {

       return $this->db->count_all('users');

   }

 

}

Controller

$data['users'] = $this->UserModel->totalUsers();

 

$this->load->view('dashboard',$data);

View

Total Users :

<?php echo $users; ?>

This follows the MVC architecture recommended in CodeIgniter.

 

Conclusion

Counting rows is one of the most common database operations in PHP CodeIgniter, and the framework offers several efficient methods to accomplish it. Whether you need the total number of records, filtered counts, grouped data, or distinct values, CodeIgniter's Query Builder provides a clean and secure solution.

For the best performance, use count_all() for total records and count_all_results() for filtered queries. Avoid fetching unnecessary data with num_rows() when working with large tables, and follow the MVC architecture by placing database logic inside models.

By applying the techniques and best practices covered in this guide, you can build faster, more scalable, and easier-to-maintain CodeIgniter applications that deliver accurate statistics and reporting.

 

Frequently Asked Questions (FAQs)

1. How do I count all rows in a CodeIgniter table?

Use the count_all() method.

$this->db->count_all('users');

It returns the total number of records in the specified table.

 

2. What is the difference between count_all() and count_all_results()?

count_all() counts every row in a table, while count_all_results() counts rows after applying filters such as WHERE, LIKE, or JOIN.

 

3. Can I count rows with a WHERE condition?

Yes. Apply the condition before calling count_all_results().

$this->db->where('status', 1);

$this->db->from('users');

$count = $this->db->count_all_results();

 

4. Is num_rows() recommended for counting records?

It works, but it retrieves all matching rows before counting them. For large tables, count_all() or count_all_results() is more efficient.

 

5. Can I count rows using JOIN?

Yes. You can use JOIN along with COUNT() to count related records across multiple tables.

 

6. How do I count distinct values?

Use SQL's COUNT(DISTINCT column_name) with CodeIgniter's Query Builder.

 

7. Can I group records while counting?

Yes. Use GROUP BY along with COUNT() to display counts for each category.

 

8. Which method offers the best performance?

count_all() is the fastest for counting all records, while count_all_results() is best for filtered queries.

 

9. Can I use row counts in dashboards?

Absolutely. Counting rows is commonly used for displaying users, products, orders, reports, and other key business statistics.

 

10. Why should I use Query Builder instead of raw SQL?

Query Builder makes your code easier to read, simplifies query construction, improves maintainability, and helps protect against SQL injection.