Query Builder
Relevant source files
The following files were used as context for generating this wiki page:
The Query Builder class in CodeIgniter 4 provides a database-independent interface for creating and executing database queries. It allows developers to build SQL queries programmatically using a series of chained methods, which enhances security by automatically escaping all values and identifiers. This approach simplifies the process of retrieving, inserting, updating, and deleting data, making the application more portable across different supported database platforms.
Query Builder is an integral part of the database abstraction layer. It is accessed through a database connection instance and returns standardized result objects. Its fluent interface enables the construction of complex queries in a highly readable and maintainable way, without needing to write raw SQL.
Sources: source/database/query_builder.rst:3-12
Initializing Query Builder
To use the Query Builder, you first need a database connection instance. The primary way to get this is through the \Config\Database::connect() helper. Once you have the connection object, you can access the Query Builder by calling the $db->table() method, specifying the name of the database table you wish to query.
$db = \Config\Database::connect();
$builder = $db->table('users');
This $builder object is now ready for chaining methods to build and execute a query.
The following diagram illustrates the initialization flow.
This diagram shows the process of obtaining a Query Builder instance from the main database connection service.
Sources: source/database/connecting.rst:19-27, source/database/query_builder.rst:32-37
Selecting Data
Query Builder provides a rich set of methods for building SELECT statements. The process typically involves specifying the table, adding conditions, and then executing the query to fetch the results.
Basic SELECT Query Flow
A typical SELECT query involves chaining methods like where() to add constraints and then calling an execution method like get() to run the query and retrieve the data.
This sequence shows how chained methods on the builder instance translate into a single SQL query executed against the database, with the results returned in a dedicated Result object.
Sources: source/database/query_builder.rst:32-45, source/database/results.rst:3-5
Common SELECT Methods
The following table summarizes the most frequently used methods for building SELECT queries.
| Method | Description |
|---|---|
select() | Specifies the columns to be selected. Can be a string or an array. |
selectMax(), selectMin(), selectAvg(), selectSum() | Convenience methods for aggregate functions. |
from() | Specifies the table to query. This is an alternative to $db->table(). |
join() | Adds a JOIN clause to the query. Supports left, right, inner, outer join types. |
where() | Adds a WHERE clause. Can be a string, array, or key/value pairs. |
orWhere() | Adds a WHERE clause, but prepended with OR. |
whereIn(), orWhereIn(), whereNotIn(), orWhereNotIn() | Generates WHERE field IN ('item1', 'item2') clauses. |
like(), orLike(), notLike(), orNotLike() | Generates LIKE clauses, useful for string matching. |
groupBy() | Adds a GROUP BY clause. |
having() | Adds a HAVING clause, typically used with GROUP BY. |
orderBy() | Adds an ORDER BY clause. |
limit() | Adds a LIMIT clause to restrict the number of results. Can also set an offset. |
get() | Executes the SELECT query and returns a result object. |
getWhere() | A shortcut for combining where() and get(). |
Sources: source/database/query_builder.rst:47-414
Handling Results
After executing a query with get(), you receive a result object. Several methods are available to format and access the returned data.
| Method | Return Type | Description |
|---|---|---|
getResult() | array of stdClass objects | Returns all query result rows as an array of objects. |
getResultArray() | array of arrays | Returns all query result rows as an array of associative arrays. |
getRow() | stdClass object or null | Returns a single result row as an object. |
getRowArray() | array or null | Returns a single result row as an associative array. |
getNumRows() | int | Returns the number of rows returned by the query. |
getFirstRow() | stdClass object or null | Returns the first row of the result set. |
getLastRow() | stdClass object or null | Returns the last row of the result set. |
Sources: source/database/results.rst:3-130
Modifying Data
Query Builder simplifies INSERT, UPDATE, and DELETE operations with dedicated methods that handle data escaping automatically.
Inserting Data
To insert a new row, use the insert() method, passing an array or an object of data. For multiple rows, insertBatch() is more efficient as it performs a single query.
| Method | Description |
|---|---|
insert() | Generates and executes an INSERT statement. Returns true on success. |
insertBatch() | Generates and executes a batch INSERT statement for multiple rows. |
set() | Can be used to set values for inserts or updates in a chained manner. |
Example:
$data = [
'username' => 'darth',
'email' => 'd.vader@theempire.com',
'password' => 'deathstar',
];
$builder->insert($data);
// Produces: INSERT INTO users (username, email, password) VALUES ('darth', 'd.vader@theempire.com', 'deathstar')
Sources: source/database/query_builder.rst:489-566
Updating Data
The update() method is used to modify existing records. It requires a where() clause to specify which rows to update. updateBatch() can update multiple rows with different values in a single query.
| Method | Description |
|---|---|
update() | Generates and executes an UPDATE statement. Requires a where() clause. |
updateBatch() | Generates and executes a batch UPDATE statement. Requires a "where" key in the data array. |
Example:
$data = ['username' => 'darth.vader'];
$builder->where('id', 1);
$builder->update($data);
// Produces: UPDATE users SET username = 'darth.vader' WHERE id = 1
Sources: source/database/query_builder.rst:568-648
Deleting Data
To delete records, use the delete() method, which is typically combined with a where() clause. To delete all records from a table, emptyTable() or truncate() can be used.
| Method | Description |
|---|---|
delete() | Generates and executes a DELETE statement. Requires a where() clause. |
emptyTable() | Deletes all data from a table. DELETE FROM table. |
truncate() | Empties a table and resets its auto-increment primary key. TRUNCATE TABLE table. |
Sources: source/database/query_builder.rst:650-689
Advanced Features
Method Chaining
One of the key features of the Query Builder is the ability to chain methods together to form a readable and fluent query.
This flowchart shows a typical chain of methods to build a complex SELECT query before executing it.
Sources: source/database/query_builder.rst:691-698
Transactions
Query Builder operations can be wrapped in database transactions to ensure data integrity. The transaction methods are accessed via the database connection object ($db).
This diagram illustrates the automated transaction flow. transComplete() automatically commits if all queries were successful and rolls back if any failed.
| Method | Description |
|---|---|
transStart() | Starts a transaction. |
transComplete() | Completes the transaction, automatically committing or rolling back. |
transStatus() | Returns the status of the transaction (true for success, false for failure). |
transCommit() | Manually commits the transaction. |
transRollback() | Manually rolls back the transaction. |
Sources: source/database/transactions.rst:3-10, 29-55
Database Configuration
The behavior of the Query Builder is dependent on the database connection, which is configured in app/Config/Database.php. This file defines connection groups (e.g., default, tests) and their specific settings.
Key configuration options include:
DSN: A DSN connection string.hostname: The database host.username: The database user.password: The user's password.database: The name of the database.DBDriver: The database driver (e.g.,MySQLi,Postgre).DBPrefix: A prefix added to all table names, which Query Builder handles automatically.
Sources: source/database/configuration.rst:11-100
Summary
CodeIgniter's Query Builder is a powerful and secure tool for database interaction. It provides a fluent, database-agnostic API for building and executing queries for selecting, inserting, updating, and deleting data. By leveraging method chaining, automatic data escaping, and integration with features like transactions and result handling, it significantly improves developer productivity and application security.
Sources: source/database/query_builder.rst, source/database/results.rst