PHP CodeIgniter Count Rows in Table

Counting rows in a database table is a common requirement in web development, especially when working with data-driven applications. Whether you're building a dashboard, generating reports, or validating entries, knowing how to count rows using PHP CodeIgniter is essential.

In this blog, we’ll explore different methods to count rows in a table using CodeIgniter, including examples, use cases, and best practices.

What is CodeIgniter?

CodeIgniter is a powerful PHP framework that helps developers build web applications faster and with more structure. Known for its lightweight design and performance, CodeIgniter is ideal for developers looking to create dynamic websites with minimal configuration.

It follows the Model-View-Controller (MVC) pattern, making it easier to manage code, database interactions, and application logic.

Why Count Rows in CodeIgniter?

Counting rows is useful for:

• Displaying statistics (e.g., total users, orders, posts)

• Pagination: Knowing how many records exist helps in building paginated views

• Validation: Ensure a record doesn’t already exist

• Reports: Summarize data for analytics

CodeIgniter Database Library

CodeIgniter comes with a robust Database Library, which simplifies queries including inserts, updates, deletes, and SELECT operations like counting rows.

Before using the database library, make sure to load the database in your controller or autoload it in application/config/autoload.php.

$autoload['libraries'] = array('database');

Methods to Count Rows in CodeIgniter

1. Using num_rows() Method

This is the most straightforward way to count rows in CodeIgniter.

Example:

public function countUsers()
{
    $query = $this->db->get('users');
    $count = $query->num_rows();

    echo "Total users: " . $count;
}

Explanation:

• get('users') fetches all rows from the users table

• num_rows() returns the total number of rows

When to Use:

• When you need to fetch the data and also know how many rows exist.

 

2. Using count_all() Method

If you only want the count and not the actual data, count_all() is more efficient.

Example:

public function totalUsers()
{
    $count = $this->db->count_all('users');
    echo "There are $count users in the database.";
}

Explanation:

• count_all('users') runs a simple SELECT COUNT(*) query internally

When to Use:

• For performance, especially when you don't need the actual data.

3. Using Active Record with where() and num_rows()

You can also apply conditions while counting.

Example:

public function activeUsers()
{
    $this->db->where('status', 'active');
    $query = $this->db->get('users');
    $count = $query->num_rows();

    echo "Total active users: " . $count;
}

Explanation:

• Adds a WHERE clause (status = 'active') before counting

 

4. Using count_all_results() with Conditions

You can count rows directly using conditions, without retrieving data.

Example:

public function countAdmins()
{
    $this->db->where('role', 'admin');
    $count = $this->db->count_all_results('users');

    echo "Total admins: " . $count;
}

Advantages:

• More efficient than retrieving rows and using num_rows()

• Ideal when working with large datasets

Real-World Use Case: Dashboard Counter

Let’s create a dashboard function that displays:

• Total users

• Active users

• Admins

public function dashboardStats()
{
    $data['total_users'] = $this->db->count_all('users');

    $this->db->where('status', 'active');
    $data['active_users'] = $this->db->count_all_results('users');

    $this->db->where('role', 'admin');
    $data['admin_users'] = $this->db->count_all_results('users');

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

In the dashboard view (admin/dashboard.php), you can access and display these counts.

Performance Considerations

• Use count_all() or count_all_results() if you don't need the actual data.

• Avoid num_rows() on large tables unless necessary.

• Always index columns used in WHERE clauses to speed up queries.

Best Practices

• Use Models to handle database logic, not Controllers.

• Cache frequent row counts if data changes rarely.

• Avoid unnecessary joins in count queries unless needed.

• Use pagination instead of loading entire tables.

Common Mistakes to Avoid

Mistake	Solution
Fetching large datasets just to count rows	Use count_all() or count_all_results()
Using num_rows() with no result	Always check if query ran successfully
Counting without filtering sensitive data	Always sanitize and apply conditions
Ignoring indexes	Index your database columns

Bonus: Count Rows in Joined Tables

public function countUserOrders($user_id)
{
    $this->db->from('orders');
    $this->db->join('users', 'users.id = orders.user_id');
    $this->db->where('users.id', $user_id);
    return $this->db->count_all_results();
}

This will return the total number of orders for a specific user.

Summary

Method	Usage	Performance
num_rows()	Counts rows from fetched data	Medium
count_all()	Counts all rows in table	Fast
count_all_results()	Counts rows with conditions	Fast
where() + num_rows()	Count after fetching filtered data	Medium

 

Choose the method based on your need—data display, filtering, or pure statistics.

FAQs

1. How can I count rows in CodeIgniter without retrieving data?

Use count_all() or count_all_results():

$this->db->where('status', 'active'); $count = $this->db->count_all_results('users'); 

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

• num_rows() counts rows after a query is run and data is fetched.

• count_all() performs a count directly without fetching records.

3. Can I count rows with multiple conditions?

Yes. You can use multiple where() conditions:

$this->db->where('status', 'active'); $this->db->where('role', 'admin'); $count = $this->db->count_all_results('users');

4. Is count_all() faster than num_rows()?

Yes. Since count_all() performs a direct SELECT COUNT(*), it’s faster than fetching all rows and counting them.

5. Can I use CodeIgniter count methods in models?

Absolutely. It is recommended to write counting logic inside models and call them from controllers.

6. How do I count distinct values in CodeIgniter?

Use a custom query like:

$this->db->select('COUNT(DISTINCT(column_name)) as count'); $query = $this->db->get('table_name'); return $query->row()->count;

7. What if my count query is slow?

• Use indexes on filtered columns

• Avoid joins if not needed

• Use database caching if applicable

8. Can I get the count and data at the same time?

Yes, but it's better to separate them for performance. Or you can return both from a model.

9. Can I use pagination with count queries?

Yes. Count total rows, then fetch limited results using limit() and offset().

10. Is there a difference in count method between CodeIgniter 3 and 4?

The logic is similar, but CodeIgniter 4 uses a slightly different syntax and query builder. The concept remains the same.