Introduction

In this article, we'll explore how to count the occurrences of user_id in a database table, group the results, and display the top 10 users with the most entries using CodeIgniter's Active Record query builder.

✅ Step 1: Database Structure

Sample Table user_scores

✅ Step 2: CodeIgniter Active Record Query

$this->db->select('user_id, COUNT(user_id) as row_count');
$this->db->from('user_scores');
$this->db->group_by('user_id');
$this->db->order_by('row_count', 'DESC');
$this->db->limit(10);
$query = $this->db->get();

$result = $query->result_array();

✅ Step 3: Explanation

1. select(): Selects the user_id and counts how many rows each user_id has.

2. from(): Specifies the table name (user_scores).

3. group_by(): Groups the results by user_id.

4. order_by(): Orders the results by the row count in descending order.

5. limit(): Limits the results to the top 10 entries.

✅ Step 4: Sample Output

Array
(
    [0] => Array
        (
            [user_id] => 12
            [row_count] => 3
        )

    [1] => Array
        (
            [user_id] => 15
            [row_count] => 2
        )

    [2] => Array
        (
            [user_id] => 18
            [row_count] => 1
        )
)

✅ Complete Code Example

class UserModel extends CI_Model {
    public function get_top_users() {
        $this->db->select('user_id, COUNT(user_id) as row_count');
        $this->db->from('user_scores');
        $this->db->group_by('user_id');
        $this->db->order_by('row_count', 'DESC');
        $this->db->limit(10);
        $query = $this->db->get();

        return $query->result_array();
    }
}

Tips

• Ensure that the table name is correctly referenced.

• The group_by() function is essential for counting the entries per user_id.

• Use order_by() to sort the results in descending order.

Common Pitfalls

• Forgetting to call group_by() will result in inaccurate data.

• Not setting a limit can lead to performance issues if the table is large.

• Using incorrect field names can cause errors or empty results.

✅ Conclusion

By leveraging CodeIgniter's Active Record query builder, you can efficiently count rows, group them by user_id, and retrieve the top 10 users with the highest entries. This approach is both simple and scalable for handling large datasets.