Query Builder

Query Builder in CodeIgniter (CodeIgniter 4)

The Query Builder in CodeIgniter provides a simple, secure, and database-independent way to build SQL queries using PHP methods instead of writing raw SQL.

1. What Is Query Builder?

• Builds SQL queries programmatically

• Prevents SQL injection automatically

• Works with multiple database systems

• Easier to read and maintain than raw SQL

2. Loading Query Builder

$db = \Config\Database::connect();
$builder = $db->table('users');

3. Select Queries

Select All Records

$query = $builder->get();
$results = $query->getResult();

Select Specific Columns

$builder->select('id, name, email');
$query = $builder->get();

Where Clause

$builder->where('id', 1)->get();

Multiple Conditions

$builder->where([
    'status' => 'active',
    'role'   => 'admin'
])->get();

4. Insert Queries

$data = [
    'name'  => 'John',
    'email' => 'john@example.com'
];

$builder->insert($data);

5. Update Queries

$builder->where('id', 1);
$builder->update(['name' => 'Updated Name']);

6. Delete Queries

$builder->where('id', 1)->delete();

7. Advanced Query Builder Methods

Like

$builder->like('name', 'john')->get();

Join

$builder->select('users.name, orders.total')
        ->join('orders', 'orders.user_id = users.id')
        ->get();

Order By

$builder->orderBy('created_at', 'DESC')->get();

Limit & Offset

$builder->limit(10, 0)->get();

8. Group By & Having

$builder->groupBy('role')
        ->having('COUNT(id) >', 5)
        ->get();

9. Query Builder with Models

$model = new UserModel();
$model->where('status', 'active')->findAll();

10. Getting SQL Query (Debugging)

echo $builder->getCompiledSelect();

11. Query Builder Security

✔ Automatically escapes values
✔ Prevents SQL injection
✔ Supports prepared statements

12. Transactions

$db->transStart();
$builder->insert($data);
$db->transComplete();

Best Practices

✅ Use Query Builder instead of raw SQL
✅ Use models for reusable queries
❌ Avoid complex logic in controllers

Summary

• Query Builder simplifies database queries

• Secure & database-agnostic

• Supports CRUD, joins, filters, transactions

• Integrates seamlessly with models