CodeIgniter gives you access to a Query Builder class. This pattern allows information to be retrieved, inserted, and updated in your database with minimal scripting. In some cases, only one or two lines of code are necessary to perform a database action. CodeIgniter does not require that each database table be its own class file. It instead provides a more simplified interface.
Beyond simplicity, a major benefit to using the Query Builder features is that it allows you to create database independent applications, since the query syntax is generated by each database adapter. It also allows for safer queries, since the values are escaped automatically by the system.
Note
CodeIgniter doesn't support dots (.) in the table and column names.
Since v4.5.0, database names with dots are supported.
- SQL Injection Protection
- Loading the Query Builder
- Selecting Data
- Looking for Specific Data
- Looking for Similar Data
- Ordering Results
- Limiting or Counting Results
- Union queries
- Query grouping
- Inserting Data
- Upserting Data
- Updating Data
- Deleting Data
- Conditional Statements
- Method Chaining
- Resetting Query Builder
- Class Reference
You can generate SQL statements quite safely with the Query Builder. However, it is not designed to prevent SQL injection no matter what data you pass.
- Arguments passed to the Query Builder can be:
- identifiers such as field (or table) names
- their values
- a part of SQL strings
The Query Builder will escape all values by default.
It will also try to properly protect identifiers and identifiers in SQL strings by default. However, it is implemented to work well in many use cases and is not designed to prevent all attacks. Therefore, you should never feed in user input to them without proper validation.
Also, many methods have the $escape parameter that can be set to disable escaping.
If $escape is set to false, no protection is provided by the Query Builder,
so you must ensure by yourself that
they are properly escaped or protected before passing it to the Query Builder.
The same is true when using RawSql, which specifies a raw SQL statement.
The Query Builder is loaded through the table() method on the
database connection. This sets the FROM portion of the query for you
and returns a new instance of the Query Builder class:
.. literalinclude:: query_builder/001.php
The Query Builder is only loaded into memory when you specifically request the class, so no resources are used by default.
The following methods allow you to build SQL SELECT statements.
Runs the selection query and returns the result. Can be used by itself to retrieve all records from a table:
.. literalinclude:: query_builder/002.php
The first and second parameters enable you to set a limit and offset clause:
.. literalinclude:: query_builder/003.php
You'll notice that the above method is assigned to a variable named $query, which can be used to show the results:
.. literalinclude:: query_builder/004.php
Please visit the :ref:`getResult() <getresult>` method for a full discussion regarding result generation.
Compiles the selection query just like $builder->get() but does not run
the query. This method simply returns the SQL query as a string.
Example:
.. literalinclude:: query_builder/005.php
The parameter (false) in the first query below enables you to set whether or not the query builder
will be reset (because the default value of the parameter is true, getCompiledSelect(bool $reset = true), by default it will be reset just like when using $builder->get()):
.. literalinclude:: query_builder/006.php
The key thing to notice in the above example is that the second query did not
utilize limit(10, 20) but the generated SQL query has LIMIT 20, 10.
The reason for this outcome is because the parameter in the first query is set to false, limit(10, 20) remained in the second query.
Identical to the get() method except that it permits you to add a
"where" clause in the first parameter, instead of using the $builder->where()
method:
.. literalinclude:: query_builder/007.php
Please read about the where() method below for more information.
Permits you to write the SELECT portion of your query:
.. literalinclude:: query_builder/008.php
Note
If you are selecting all (*) from a table you do not need to
use this method. When omitted, CodeIgniter assumes that you wish
to select all fields and automatically adds SELECT *.
$builder->select() accepts an optional second parameter. If you set it
to false, CodeIgniter will not try to protect your field or table names.
This is useful if you need a compound select statement where automatic
escaping of fields may break them.
.. literalinclude:: query_builder/009.php
.. versionadded:: 4.2.0
Since v4.2.0, $builder->select() accepts a CodeIgniter\Database\RawSql instance, which expresses raw SQL strings.
.. literalinclude:: query_builder/099.php
Warning
When you use RawSql, you MUST escape the values and protect the identifiers manually. Failure to do so could result in SQL injections.
Writes a SELECT MAX(field) portion for your query. You can optionally include a second parameter to rename the resulting field.
.. literalinclude:: query_builder/010.php
Writes a SELECT MIN(field) portion for your query. As with
selectMax(), you can optionally include a second parameter to rename
the resulting field.
.. literalinclude:: query_builder/011.php
Writes a SELECT AVG(field) portion for your query. As with
selectMax(), you can optionally include a second parameter to rename
the resulting field.
.. literalinclude:: query_builder/012.php
Writes a SELECT SUM(field) portion for your query. As with
selectMax(), you can optionally include a second parameter to rename
the resulting field.
.. literalinclude:: query_builder/013.php
Writes a SELECT COUNT(field) portion for your query. As with
selectMax(), you can optionally include a second parameter to rename
the resulting field.
Note
This method is particularly helpful when used with groupBy(). For
counting results generally see countAll() or countAllResults().
.. literalinclude:: query_builder/014.php
Adds a subquery to the SELECT section.
.. literalinclude:: query_builder/015.php :lines: 2-
Permits you to write the FROM portion of your query:
.. literalinclude:: query_builder/016.php
Note
As shown earlier, the FROM portion of your query can be specified
in the $db->table() method. Additional calls to from() will add more tables
to the FROM portion of your query.
Permits you to write part of a FROM query as a subquery.
This is where we add a subquery to an existing table:
.. literalinclude:: query_builder/017.php
Use the $db->newQuery() method to make a subquery the main table:
.. literalinclude:: query_builder/018.php
Permits you to write the JOIN portion of your query:
.. literalinclude:: query_builder/019.php
Multiple method calls can be made if you need several joins in one query.
If you need a specific type of JOIN you can specify it via the third
parameter of the method. Options are: left, right, outer, inner, left
outer, and right outer.
.. literalinclude:: query_builder/020.php
.. versionadded:: 4.2.0
Since v4.2.0, $builder->join() accepts a CodeIgniter\Database\RawSql instance as the JOIN ON condition, which expresses raw SQL strings.
.. literalinclude:: query_builder/102.php
Warning
When you use RawSql, you MUST escape the values and protect the identifiers manually. Failure to do so could result in SQL injections.
This method enables you to set WHERE clauses using one of five methods:
Note
All values passed to this method are escaped automatically, producing safer queries, except when using a custom string.
Note
$builder->where() accepts an optional third parameter. If you set it to
false, CodeIgniter will not try to protect your field or table names.
.. literalinclude:: query_builder/021.phpNotice that the equal sign is added for you.
If you use multiple method calls they will be chained together with AND between them:
.. literalinclude:: query_builder/022.php
You can include an operator in the first parameter in order to control the comparison:
.. literalinclude:: query_builder/023.php
.. literalinclude:: query_builder/024.phpYou can include your own operators using this method as well:
.. literalinclude:: query_builder/025.php
You can write your own clauses manually:
.. literalinclude:: query_builder/026.phpWarning
If you are using user-supplied data within the string, you MUST escape the values and protect the identifiers manually. Failure to do so could result in SQL injections.
.. literalinclude:: query_builder/027.php
.. versionadded:: 4.2.0Since v4.2.0,
$builder->where()accepts aCodeIgniter\Database\RawSqlinstance, which expresses raw SQL strings... literalinclude:: query_builder/100.phpWarning
When you use
RawSql, you MUST escape the values and protect the identifiers manually. Failure to do so could result in SQL injections.
.. literalinclude:: query_builder/028.php
This method is identical to the one above, except that multiple instances are joined by OR:
.. literalinclude:: query_builder/029.php
Generates a WHERE field IN ('item', 'item') SQL query joined with AND if appropriate:
.. literalinclude:: query_builder/030.php
You can use subqueries instead of an array of values:
.. literalinclude:: query_builder/031.php
Generates a WHERE field IN ('item', 'item') SQL query joined with OR if appropriate:
.. literalinclude:: query_builder/032.php
You can use subqueries instead of an array of values:
.. literalinclude:: query_builder/033.php
Generates a WHERE field NOT IN ('item', 'item') SQL query joined with AND if appropriate:
.. literalinclude:: query_builder/034.php
You can use subqueries instead of an array of values:
.. literalinclude:: query_builder/035.php
Generates a WHERE field NOT IN ('item', 'item') SQL query joined with OR if appropriate:
.. literalinclude:: query_builder/036.php
You can use subqueries instead of an array of values:
.. literalinclude:: query_builder/037.php
This method enables you to generate LIKE clauses, useful for doing searches.
Note
All values passed to this method are escaped automatically.
Note
All like* method variations can be forced to perform case-insensitive searches by passing
a fifth parameter of true to the method. This will use platform-specific features where available
otherwise, will force the values to be lowercase, i.e., WHERE LOWER(column) LIKE '%search%'. This
may require indexes to be made for LOWER(column) instead of column to be effective.
.. literalinclude:: query_builder/038.phpIf you use multiple method calls they will be chained together with AND between them:
.. literalinclude:: query_builder/039.phpIf you want to control where the wildcard (%) is placed, you can use an optional third argument. Your options are
before,afterandboth(which is the default)... literalinclude:: query_builder/040.php
.. literalinclude:: query_builder/041.php
.. versionadded:: 4.2.0Since v4.2.0,
$builder->like()accepts aCodeIgniter\Database\RawSqlinstance, which expresses raw SQL strings... literalinclude:: query_builder/101.phpWarning
When you use
RawSql, you MUST escape the values and protect the identifiers manually. Failure to do so could result in SQL injections.
This method is identical to the one above, except that multiple instances are joined by OR:
.. literalinclude:: query_builder/042.php
This method is identical to like(), except that it generates
NOT LIKE statements:
.. literalinclude:: query_builder/043.php
This method is identical to notLike(), except that multiple
instances are joined by OR:
.. literalinclude:: query_builder/044.php
Permits you to write the GROUP BY portion of your query:
.. literalinclude:: query_builder/045.php
You can also pass an array of multiple values as well:
.. literalinclude:: query_builder/046.php
Adds the DISTINCT keyword to a query
.. literalinclude:: query_builder/047.php
Permits you to write the HAVING portion of your query. There are 2 possible syntaxes, 1 argument or 2:
.. literalinclude:: query_builder/048.php
You can also pass an array of multiple values as well:
.. literalinclude:: query_builder/049.php
If you are using a database that CodeIgniter escapes values for, you
can prevent escaping content by passing an optional third argument, and
setting it to false.
.. literalinclude:: query_builder/050.php
Identical to having(), only separates multiple clauses with OR.
Generates a HAVING field IN ('item', 'item') SQL query joined with AND if appropriate:
.. literalinclude:: query_builder/051.php
You can use subqueries instead of an array of values:
.. literalinclude:: query_builder/052.php
Generates a HAVING field IN ('item', 'item') SQL query joined with OR if appropriate
.. literalinclude:: query_builder/053.php
You can use subqueries instead of an array of values:
.. literalinclude:: query_builder/054.php
Generates a HAVING field NOT IN ('item', 'item') SQL query joined with AND if appropriate
.. literalinclude:: query_builder/055.php
You can use subqueries instead of an array of values:
.. literalinclude:: query_builder/056.php
Generates a HAVING field NOT IN ('item', 'item') SQL query joined with OR if appropriate
.. literalinclude:: query_builder/057.php
You can use subqueries instead of an array of values:
.. literalinclude:: query_builder/058.php
This method enables you to generate LIKE clauses for HAVING part or the query, useful for doing searches.
Note
All values passed to this method are escaped automatically.
Note
All havingLike*() method variations can be forced to perform case-insensitive searches by passing
a fifth parameter of true to the method. This will use platform-specific features where available
otherwise, will force the values to be lowercase, i.e., HAVING LOWER(column) LIKE '%search%'. This
may require indexes to be made for LOWER(column) instead of column to be effective.
.. literalinclude:: query_builder/059.phpIf you use multiple method calls they will be chained together with AND between them:
.. literalinclude:: query_builder/060.phpIf you want to control where the wildcard (%) is placed, you can use an optional third argument. Your options are
before,afterandboth(which is the default)... literalinclude:: query_builder/061.php
.. literalinclude:: query_builder/062.php
This method is identical to the one above, except that multiple instances are joined by OR:
.. literalinclude:: query_builder/063.php
This method is identical to havingLike(), except that it generates
NOT LIKE statements:
.. literalinclude:: query_builder/064.php
This method is identical to notHavingLike(), except that multiple
instances are joined by OR:
.. literalinclude:: query_builder/065.php
Lets you set an ORDER BY clause.
The first parameter contains the name of the column you would like to order by.
The second parameter lets you set the direction of the result.
Options are ASC, DESC AND RANDOM.
.. literalinclude:: query_builder/066.php
You can also pass your own string in the first parameter:
.. literalinclude:: query_builder/067.php
Or multiple method calls can be made if you need multiple fields.
.. literalinclude:: query_builder/068.php
If you choose the RANDOM direction option, then the first parameters will
be ignored, unless you specify a numeric seed value.
.. literalinclude:: query_builder/069.php
Lets you limit the number of rows you would like returned by the query:
.. literalinclude:: query_builder/070.php
Note
If LIMIT 0 is specified in a SQL statement, 0 records are returned.
However, there is a bug in the Query Builder, and if limit(0) is specified,
the generated SQL statement will have no LIMIT clause and all records will
be returned. To fix the incorrect behavior, a setting was added in v4.5.0. See
:ref:`v450-query-builder-limit-0-behavior` for details. The incorrect behavior
will be fixed in a future version, so it is recommended that you change the
default setting.
The second parameter lets you set a result offset.
.. literalinclude:: query_builder/071.php
Permits you to determine the number of rows in a particular Query
Builder query. Queries will accept Query Builder restrictors such as
where(), orWhere(), like(), orLike(), etc. Example:
.. literalinclude:: query_builder/072.php
However, this method also resets any field values that you may have passed
to select(). If you need to keep them, you can pass false as the
first parameter.
.. literalinclude:: query_builder/073.php
Permits you to determine the number of rows in a particular table. Example:
.. literalinclude:: query_builder/074.php
As is in countAllResult() method, this method resets any field values that you may have passed
to select() as well. If you need to keep them, you can pass false as the
first parameter.
Is used to combine the result-set of two or more SELECT statements. It will return only the unique results.
.. literalinclude:: query_builder/103.php
Note
For correct work with DBMS (such as MSSQL and Oracle) queries are wrapped in SELECT * FROM ( ... ) alias
The main query will always have an alias of uwrp0. Each subsequent query added via union() will have an
alias uwrpN+1.
All union queries will be added after the main query, regardless of the order in which the union() method was
called. That is, the limit() or orderBy() methods will be relative to the main query, even if called after
union().
In some cases, it may be necessary, for example, to sort or limit the number of records of the query result.
The solution is to use the wrapper created via $db->newQuery().
In the example below, we get the first 5 users + the last 5 users and sort the result by id:
.. literalinclude:: query_builder/104.php
The behavior is the same as the union() method. However, all results will be returned, not just the unique ones.
Query grouping allows you to create groups of WHERE clauses by enclosing them in parentheses. This will allow you to create queries with complex WHERE clauses. Nested groups are supported. Example:
.. literalinclude:: query_builder/075.php
Note
Groups need to be balanced, make sure every groupStart() is matched by a groupEnd().
Starts a new group by adding an opening parenthesis to the WHERE clause of the query.
Starts a new group by adding an opening parenthesis to the WHERE clause of the query, prefixing it with OR.
Starts a new group by adding an opening parenthesis to the WHERE clause of the query, prefixing it with NOT.
Starts a new group by adding an opening parenthesis to the WHERE clause of the query, prefixing it with OR NOT.
Ends the current group by adding a closing parenthesis to the WHERE clause of the query.
Starts a new group by adding an opening parenthesis to the HAVING clause of the query.
Starts a new group by adding an opening parenthesis to the HAVING clause of the query, prefixing it with OR.
Starts a new group by adding an opening parenthesis to the HAVING clause of the query, prefixing it with NOT.
Starts a new group by adding an opening parenthesis to the HAVING clause of the query, prefixing it with OR NOT.
Ends the current group by adding a closing parenthesis to the HAVING clause of the query.
Generates an insert string based on the data you supply, and runs the query. You can either pass an array or an object to the method. Here is an example using an array:
.. literalinclude:: query_builder/076.php
The first parameter is an associative array of values.
Note
All values except RawSql are escaped automatically producing safer queries.
Warning
When you use RawSql, you MUST escape the data manually. Failure to do so could result in SQL injections.
Here is an example using an object:
.. literalinclude:: query_builder/077.php
.. literalinclude:: query_builder/121.php
The first parameter is an object.
Generates an insert ignore string based on the data you supply, and runs the query. So if an entry with the same primary key already exists, the query won't be inserted. You can optionally pass an boolean to the method. Can also be used on insertBatch, update and delete (when supported). Here is an example using the array of the above example:
.. literalinclude:: query_builder/078.php
Compiles the insertion query just like $builder->insert() but does not
run the query. This method simply returns the SQL query as a string.
Example:
.. literalinclude:: query_builder/079.php
The first parameter enables you to set whether or not the query builder query
will be reset (by default it will be--just like $builder->insert()):
.. literalinclude:: query_builder/080.php
The reason the second query worked is that the first parameter is set to false.
Note
This method doesn't work for batch inserts.
Generates an insert string based on the data you supply, and runs the query. You can either pass an array or an object to the method. Here is an example using an array:
.. literalinclude:: query_builder/081.php
The first parameter is an associative array of values.
Note
All values except RawSql are escaped automatically producing safer queries.
Warning
When you use RawSql, you MUST escape the data manually. Failure to do so could result in SQL injections.
You can also insert from a query:
.. literalinclude:: query_builder/117.php
Note
setQueryAsData() can be used since v4.3.0.
Note
It is required to alias the columns of the select query to match those of the target table.
.. versionadded:: 4.3.0
Generates an upsert string based on the data you supply, and runs the query. You can either pass an array or an object to the method. By default a constraint will be defined in order. A primary key will be selected first and then unique keys. MySQL will use any constraint by default. Here is an example using an array:
.. literalinclude:: query_builder/112.php
The first parameter is an associative array of values.
Here is an example using an object:
.. literalinclude:: query_builder/122.php
.. literalinclude:: query_builder/113.php
The first parameter is an object.
Note
All values are escaped automatically producing safer queries.
.. versionadded:: 4.3.0
Compiles the upsert query just like $builder->upsert() but does not
run the query. This method simply returns the SQL query as a string.
Example:
.. literalinclude:: query_builder/114.php
Note
This method doesn't work for batch upserts.
.. versionadded:: 4.3.0
Generates an upsert string based on the data you supply, and runs the query. You can either pass an array or an object to the method. By default a constraint will be defined in order. A primary key will be selected first and then unique keys. MySQL will use any constraint by default.
Here is an example using an array:
.. literalinclude:: query_builder/108.php
The first parameter is an associative array of values.
Note
All values are escaped automatically producing safer queries.
You can also upsert from a query:
.. literalinclude:: query_builder/115.php
Note
The setQueryAsData(), onConstraint(), and updateFields()
methods can be used since v4.3.0.
Note
It is required to alias the columns of the select query to match those of the target table.
.. versionadded:: 4.3.0
Allows manually setting constraint to be used for upsert. This does not work with MySQL because MySQL checks all constraints by default.
.. literalinclude:: query_builder/109.php
This method accepts a string or an array of columns.
.. versionadded:: 4.3.0
Allows manually setting the fields to be updated when performing upserts.
.. literalinclude:: query_builder/110.php
This method accepts a string, an array of columns, or RawSql. You can also
specify an extra column to be updated that isn't included in the dataset.
This can be done by setting the second parameter to true.
.. literalinclude:: query_builder/111.php
Notice that the updated_at field is not inserted but is used on update.
This method executes a REPLACE statement, which is basically the SQL
standard for (optional) DELETE + INSERT, using PRIMARY and UNIQUE
keys as the determining factor.
In our case, it will save you from the need to implement complex
logics with different combinations of select(), update(),
delete() and insert() calls.
Example:
.. literalinclude:: query_builder/082.php
In the above example, if we assume that the title field is our primary
key, then if a row containing My title as the title value, that row
will be deleted with our new row data replacing it.
Usage of the set() method is also allowed and all values are
automatically escaped, just like with insert().
This method enables you to set values for inserts or updates.
It can be used instead of passing a data array directly to the insert() or update() methods:
.. literalinclude:: query_builder/083.php
If you use multiple method called they will be assembled properly based on whether you are doing an insert or an update:
.. literalinclude:: query_builder/084.php
set() will also accept an optional third parameter ($escape), that
will prevent the values from being escaped if set to false. To illustrate the
difference, here is set() used both with and without the escape
parameter.
.. literalinclude:: query_builder/085.php
You can also pass an associative array to this method:
.. literalinclude:: query_builder/086.php
Or an object:
.. literalinclude:: query_builder/077.php
.. literalinclude:: query_builder/087.php
Generates an update string and runs the query based on the data you supply. You can pass an array or an object to the method. Here is an example using an array:
.. literalinclude:: query_builder/088.php
Or you can supply an object:
.. literalinclude:: query_builder/077.php
.. literalinclude:: query_builder/089.php
Note
All values except RawSql are escaped automatically producing safer queries.
Warning
When you use RawSql, you MUST escape the data manually. Failure to do so could result in SQL injections.
You'll notice the use of the $builder->where() method, enabling you
to set the WHERE clause. You can optionally pass this information
directly into the update() method as a string:
.. literalinclude:: query_builder/090.php
Or as an array:
.. literalinclude:: query_builder/091.php
You may also use the $builder->set() method described above when
performing updates.
This works exactly the same way as $builder->getCompiledInsert() except
that it produces an UPDATE SQL string instead of an INSERT SQL string.
For more information view documentation for $builder->getCompiledInsert().
Note
This method doesn't work for batched updates.
Note
Since v4.3.0, the second parameter $index of updateBatch() has
changed to $constraints. It now accepts types array, string, or RawSql.
Generates an update string based on the data you supply, and runs the query. You can either pass an array or an object to the method. Here is an example using an array:
.. literalinclude:: query_builder/092.php
The first parameter is an associative array of values, the second parameter is the where keys.
Note
Since v4.3.0, the generated SQL structure has been Improved.
Since v4.3.0, you can also use the onConstraint() and updateFields() methods:
.. literalinclude:: query_builder/120.php
Note
All values except RawSql are escaped automatically producing safer queries.
Warning
When you use RawSql, you MUST escape the data manually. Failure to do so could result in SQL injections.
Note
affectedRows() won't give you proper results with this method,
due to the very nature of how it works. Instead, updateBatch()
returns the number of rows affected.
Since v4.3.0, you can also update from a query with the setQueryAsData() method:
.. literalinclude:: query_builder/116.php
Note
It is required to alias the columns of the select query to match those of the target table.
Generates a DELETE SQL string and runs the query.
.. literalinclude:: query_builder/093.php
The first parameter is the where clause.
You can also use the where() or orWhere() methods instead of passing
the data to the first parameter of the method:
.. literalinclude:: query_builder/094.php
If you want to delete all data from a table, you can use the truncate()
method, or emptyTable().
This works exactly the same way as $builder->getCompiledInsert() except
that it produces a DELETE SQL string instead of an INSERT SQL string.
For more information view documentation for $builder->getCompiledInsert().
.. versionadded:: 4.3.0
Generates a batch DELETE statement based on a set of data.
.. literalinclude:: query_builder/118.php
This method may be especially useful when deleting data in a table with a composite primary key.
Note
SQLite3 does not support the use of where().
You can also delete from a query:
.. literalinclude:: query_builder/119.php
Generates a DELETE SQL string and runs the query:
.. literalinclude:: query_builder/095.php
Generates a TRUNCATE SQL string and runs the query.
.. literalinclude:: query_builder/096.php
Note
If the TRUNCATE command isn't available, truncate() will
execute as "DELETE FROM table".
.. versionadded:: 4.3.0
This allows modifying the query based on a condition without breaking out of the query builder chain. The first parameter is the condition, and it should evaluate to a boolean. The second parameter is a callable that will be ran when the condition is true.
For example, you might only want to apply a given WHERE statement based on the value sent within an HTTP request:
.. literalinclude:: query_builder/105.php
Since the condition is evaluated as true, the callable will be called. The value
set in the condition will be passed as the second parameter to the callable so it
can be used in the query.
Sometimes you might want to apply a different statement if the condition evaluates to false. This can be accomplished by providing a second closure:
.. literalinclude:: query_builder/106.php
.. versionadded:: 4.3.0
This works exactly the same way as $builder->when() except that it will
only run the callable when the condition evaluates to false, instead of true like when().
.. literalinclude:: query_builder/107.php
Method chaining allows you to simplify your syntax by connecting multiple methods. Consider this example:
.. literalinclude:: query_builder/097.php
Resetting Query Builder allows you to start fresh with your query without
executing it first using a method like $builder->get() or $builder->insert().
This is useful in situations where you are using Query Builder to generate SQL
(e.g., $builder->getCompiledSelect()) but then choose to, for instance,
run the query:
.. literalinclude:: query_builder/098.php
.. php:namespace:: CodeIgniter\Database
.. php:class:: BaseBuilder
.. php:method:: db()
:returns: The database connection in use
:rtype: ``ConnectionInterface``
Returns the current database connection from ``$db``. Useful for
accessing ``ConnectionInterface`` methods that are not directly
available to the Query Builder, like ``insertID()`` or ``errors()``.
.. php:method:: resetQuery()
:returns: ``BaseBuilder`` instance (method chaining)
:rtype: ``BaseBuilder``
Resets the current Query Builder state. Useful when you want
to build a query that can be cancelled under certain conditions.
.. php:method:: countAllResults([$reset = true])
:param bool $reset: Whether to reset values for SELECTs
:returns: Number of rows in the query result
:rtype: int
Generates a platform-specific query string that counts
all records returned by an Query Builder query.
.. php:method:: countAll([$reset = true])
:param bool $reset: Whether to reset values for SELECTs
:returns: Number of rows in the query result
:rtype: int
Generates a platform-specific query string that counts
all records in the particular table.
.. php:method:: get([$limit = null[, $offset = null[, $reset = true]]]])
:param int $limit: The LIMIT clause
:param int $offset: The OFFSET clause
:param bool $reset: Do we want to clear query builder values?
:returns: ``\CodeIgniter\Database\ResultInterface`` instance (method chaining)
:rtype: ``\CodeIgniter\Database\ResultInterface``
Compiles and runs ``SELECT`` statement based on the already
called Query Builder methods.
.. php:method:: getWhere([$where = null[, $limit = null[, $offset = null[, $reset = true]]]]])
:param string $where: The WHERE clause
:param int $limit: The LIMIT clause
:param int $offset: The OFFSET clause
:param bool $reset: Do we want to clear query builder values?
:returns: ``\CodeIgniter\Database\ResultInterface`` instance (method chaining)
:rtype: ``\CodeIgniter\Database\ResultInterface``
Same as ``get()``, but also allows the WHERE to be added directly.
.. php:method:: select([$select = '*'[, $escape = null]])
:param array|RawSql|string $select: The SELECT portion of a query
:param bool $escape: Whether to escape values and identifiers
:returns: ``BaseBuilder`` instance (method chaining)
:rtype: ``BaseBuilder``
Adds a ``SELECT`` clause to a query.
.. php:method:: selectAvg([$select = ''[, $alias = '']])
:param string $select: Field to compute the average of
:param string $alias: Alias for the resulting value name
:returns: ``BaseBuilder`` instance (method chaining)
:rtype: ``BaseBuilder``
Adds a ``SELECT AVG(field)`` clause to a query.
.. php:method:: selectMax([$select = ''[, $alias = '']])
:param string $select: Field to compute the maximum of
:param string $alias: Alias for the resulting value name
:returns: ``BaseBuilder`` instance (method chaining)
:rtype: ``BaseBuilder``
Adds a ``SELECT MAX(field)`` clause to a query.
.. php:method:: selectMin([$select = ''[, $alias = '']])
:param string $select: Field to compute the minimum of
:param string $alias: Alias for the resulting value name
:returns: ``BaseBuilder`` instance (method chaining)
:rtype: ``BaseBuilder``
Adds a ``SELECT MIN(field)`` clause to a query.
.. php:method:: selectSum([$select = ''[, $alias = '']])
:param string $select: Field to compute the sum of
:param string $alias: Alias for the resulting value name
:returns: ``BaseBuilder`` instance (method chaining)
:rtype: ``BaseBuilder``
Adds a ``SELECT SUM(field)`` clause to a query.
.. php:method:: selectCount([$select = ''[, $alias = '']])
:param string $select: Field to compute the average of
:param string $alias: Alias for the resulting value name
:returns: ``BaseBuilder`` instance (method chaining)
:rtype: ``BaseBuilder``
Adds a ``SELECT COUNT(field)`` clause to a query.
.. php:method:: selectSubquery(BaseBuilder $subquery, string $as)
:param string $subquery: Instance of BaseBuilder
:param string $as: Alias for the resulting value name
:returns: ``BaseBuilder`` instance (method chaining)
:rtype: ``BaseBuilder``
Adds a subquery to the selection
.. php:method:: distinct([$val = true])
:param bool $val: Desired value of the "distinct" flag
:returns: ``BaseBuilder`` instance (method chaining)
:rtype: ``BaseBuilder``
Sets a flag which tells the query builder to add
a ``DISTINCT`` clause to the ``SELECT`` portion of the query.
.. php:method:: from($from[, $overwrite = false])
:param mixed $from: Table name(s); string or array
:param bool $overwrite: Should we remove the first table existing?
:returns: ``BaseBuilder`` instance (method chaining)
:rtype: ``BaseBuilder``
Specifies the ``FROM`` clause of a query.
.. php:method:: fromSubquery($from, $alias)
:param BaseBuilder $from: Instance of the BaseBuilder class
:param string $alias: Subquery alias
:returns: ``BaseBuilder`` instance (method chaining)
:rtype: ``BaseBuilder``
Specifies the ``FROM`` clause of a query using a subquery.
.. php:method:: setQueryAsData($query[, $alias[, $columns = null]])
.. versionadded:: 4.3.0
:param BaseBuilder|RawSql $query: Instance of the BaseBuilder or RawSql
:param string|null $alias: Alias for query
:param array|string|null $columns: Array or comma delimited string of columns in the query
:returns: ``BaseBuilder`` instance (method chaining)
:rtype: ``BaseBuilder``
Sets a query as a datasource for ``insertBatch()``, ``updateBatch()``, ``upsertBatch()``.
If ``$columns`` is null the query will be run to generate column names.
.. php:method:: join($table, $cond[, $type = ''[, $escape = null]])
:param string $table: Table name to join
:param string|RawSql $cond: The JOIN ON condition
:param string $type: The JOIN type
:param bool $escape: Whether to escape values and identifiers
:returns: ``BaseBuilder`` instance (method chaining)
:rtype: ``BaseBuilder``
Adds a ``JOIN`` clause to a query. Since v4.2.0, ``RawSql`` can be used
as the JOIN ON condition. See also :ref:`query-builder-join`.
.. php:method:: where($key[, $value = null[, $escape = null]])
:param array|RawSql|string $key: Name of field to compare, or associative array
:param mixed $value: If a single key, compared to this value
:param bool $escape: Whether to escape values and identifiers
:returns: ``BaseBuilder`` instance (method chaining)
:rtype: ``BaseBuilder``
Generates the ``WHERE`` portion of the query. Separates multiple calls with ``AND``.
.. php:method:: orWhere($key[, $value = null[, $escape = null]])
:param mixed $key: Name of field to compare, or associative array
:param mixed $value: If a single key, compared to this value
:param bool $escape: Whether to escape values and identifiers
:returns: ``BaseBuilder`` instance (method chaining)
:rtype: ``BaseBuilder``
Generates the ``WHERE`` portion of the query. Separates multiple calls with ``OR``.
.. php:method:: orWhereIn([$key = null[, $values = null[, $escape = null]]])
:param string $key: The field to search
:param array|BaseBulder|Closure $values: Array of target values, or anonymous function for subquery
:param bool $escape: Whether to escape values and identifiers
:returns: ``BaseBuilder`` instance (method chaining)
:rtype: ``BaseBuilder``
Generates a ``WHERE`` field ``IN('item', 'item')`` SQL query, joined with ``OR`` if appropriate.
.. php:method:: orWhereNotIn([$key = null[, $values = null[, $escape = null]]])
:param string $key: The field to search
:param array|BaseBulder|Closure $values: Array of target values, or anonymous function for subquery
:param bool $escape: Whether to escape values and identifiers
:returns: ``BaseBuilder`` instance (method chaining)
:rtype: ``BaseBuilder``
Generates a ``WHERE`` field ``NOT IN('item', 'item')`` SQL query, joined with ``OR`` if appropriate.
.. php:method:: whereIn([$key = null[, $values = null[, $escape = null]]])
:param string $key: Name of field to examine
:param array|BaseBulder|Closure $values: Array of target values, or anonymous function for subquery
:param bool $escape: Whether to escape values and identifiers
:returns: ``BaseBuilder`` instance (method chaining)
:rtype: ``BaseBuilder``
Generates a ``WHERE`` field ``IN('item', 'item')`` SQL query, joined with ``AND`` if appropriate.
.. php:method:: whereNotIn([$key = null[, $values = null[, $escape = null]]])
:param string $key: Name of field to examine
:param array|BaseBulder|Closure $values: Array of target values, or anonymous function for subquery
:param bool $escape: Whether to escape values and identifiers
:returns: ``BaseBuilder`` instance (method chaining)
:rtype: ``BaseBuilder``
Generates a ``WHERE`` field ``NOT IN('item', 'item')`` SQL query, joined with ``AND`` if appropriate.
.. php:method:: groupStart()
:returns: ``BaseBuilder`` instance (method chaining)
:rtype: ``BaseBuilder``
Starts a group expression, using ``AND`` for the conditions inside it.
.. php:method:: orGroupStart()
:returns: ``BaseBuilder`` instance (method chaining)
:rtype: ``BaseBuilder``
Starts a group expression, using ``OR`` for the conditions inside it.
.. php:method:: notGroupStart()
:returns: ``BaseBuilder`` instance (method chaining)
:rtype: ``BaseBuilder``
Starts a group expression, using ``AND NOT`` for the conditions inside it.
.. php:method:: orNotGroupStart()
:returns: ``BaseBuilder`` instance (method chaining)
:rtype: ``BaseBuilder``
Starts a group expression, using ``OR NOT`` for the conditions inside it.
.. php:method:: groupEnd()
:returns: ``BaseBuilder`` instance (method chaining)
:rtype: ``BaseBuilder``
Ends a group expression.
.. php:method:: like($field[, $match = ''[, $side = 'both'[, $escape = null[, $insensitiveSearch = false]]]])
:param array|RawSql|string $field: Field name
:param string $match: Text portion to match
:param string $side: Which side of the expression to put the '%' wildcard on
:param bool $escape: Whether to escape values and identifiers
:param bool $insensitiveSearch: Whether to force a case-insensitive search
:returns: ``BaseBuilder`` instance (method chaining)
:rtype: ``BaseBuilder``
Adds a ``LIKE`` clause to a query, separating multiple calls with ``AND``.
.. php:method:: orLike($field[, $match = ''[, $side = 'both'[, $escape = null[, $insensitiveSearch = false]]]])
:param string $field: Field name
:param string $match: Text portion to match
:param string $side: Which side of the expression to put the '%' wildcard on
:param bool $escape: Whether to escape values and identifiers
:param bool $insensitiveSearch: Whether to force a case-insensitive search
:returns: ``BaseBuilder`` instance (method chaining)
:rtype: ``BaseBuilder``
Adds a ``LIKE`` clause to a query, separating multiple class with ``OR``.
.. php:method:: notLike($field[, $match = ''[, $side = 'both'[, $escape = null[, $insensitiveSearch = false]]]])
:param string $field: Field name
:param string $match: Text portion to match
:param string $side: Which side of the expression to put the '%' wildcard on
:param bool $escape: Whether to escape values and identifiers
:param bool $insensitiveSearch: Whether to force a case-insensitive search
:returns: ``BaseBuilder`` instance (method chaining)
:rtype: ``BaseBuilder``
Adds a ``NOT LIKE`` clause to a query, separating multiple calls with ``AND``.
.. php:method:: orNotLike($field[, $match = ''[, $side = 'both'[, $escape = null[, $insensitiveSearch = false]]]])
:param string $field: Field name
:param string $match: Text portion to match
:param string $side: Which side of the expression to put the '%' wildcard on
:param bool $escape: Whether to escape values and identifiers
:param bool $insensitiveSearch: Whether to force a case-insensitive search
:returns: ``BaseBuilder`` instance (method chaining)
:rtype: ``BaseBuilder``
Adds a ``NOT LIKE`` clause to a query, separating multiple calls with ``OR``.
.. php:method:: having($key[, $value = null[, $escape = null]])
:param mixed $key: Identifier (string) or associative array of field/value pairs
:param string $value: Value sought if $key is an identifier
:param string $escape: Whether to escape values and identifiers
:returns: ``BaseBuilder`` instance (method chaining)
:rtype: ``BaseBuilder``
Adds a ``HAVING`` clause to a query, separating multiple calls with ``AND``.
.. php:method:: orHaving($key[, $value = null[, $escape = null]])
:param mixed $key: Identifier (string) or associative array of field/value pairs
:param string $value: Value sought if $key is an identifier
:param string $escape: Whether to escape values and identifiers
:returns: ``BaseBuilder`` instance (method chaining)
:rtype: ``BaseBuilder``
Adds a ``HAVING`` clause to a query, separating multiple calls with ``OR``.
.. php:method:: orHavingIn([$key = null[, $values = null[, $escape = null]]])
:param string $key: The field to search
:param array|BaseBulder|Closure $values: Array of target values, or anonymous function for subquery
:param bool $escape: Whether to escape values and identifiers
:returns: ``BaseBuilder`` instance (method chaining)
:rtype: ``BaseBuilder``
Generates a ``HAVING`` field IN('item', 'item') SQL query, joined with ``OR`` if appropriate.
.. php:method:: orHavingNotIn([$key = null[, $values = null[, $escape = null]]])
:param string $key: The field to search
:param array|BaseBulder|Closure $values: Array of target values, or anonymous function for subquery
:param bool $escape: Whether to escape values and identifiers
:returns: ``BaseBuilder`` instance (method chaining)
:rtype: ``BaseBuilder``
Generates a ``HAVING`` field ``NOT IN('item', 'item')`` SQL query, joined with ``OR`` if appropriate.
.. php:method:: havingIn([$key = null[, $values = null[, $escape = null]]])
:param string $key: Name of field to examine
:param array|BaseBulder|Closure $values: Array of target values, or anonymous function for subquery
:param bool $escape: Whether to escape values and identifiers
:returns: ``BaseBuilder`` instance (method chaining)
:rtype: ``BaseBuilder``
Generates a ``HAVING`` field ``IN('item', 'item')`` SQL query, joined with ``AND`` if appropriate.
.. php:method:: havingNotIn([$key = null[, $values = null[, $escape = null]]])
:param string $key: Name of field to examine
:param array|BaseBulder|Closure $values: Array of target values, or anonymous function for subquery
:param bool $escape: Whether to escape values and identifiers
:param bool $insensitiveSearch: Whether to force a case-insensitive search
:returns: ``BaseBuilder`` instance (method chaining)
:rtype: ``BaseBuilder``
Generates a ``HAVING`` field ``NOT IN('item', 'item')`` SQL query, joined with ``AND`` if appropriate.
.. php:method:: havingLike($field[, $match = ''[, $side = 'both'[, $escape = null[, $insensitiveSearch = false]]]])
:param string $field: Field name
:param string $match: Text portion to match
:param string $side: Which side of the expression to put the '%' wildcard on
:param bool $escape: Whether to escape values and identifiers
:param bool $insensitiveSearch: Whether to force a case-insensitive search
:returns: ``BaseBuilder`` instance (method chaining)
:rtype: ``BaseBuilder``
Adds a ``LIKE`` clause to a ``HAVING`` part of the query, separating multiple calls with ``AND``.
.. php:method:: orHavingLike($field[, $match = ''[, $side = 'both'[, $escape = null[, $insensitiveSearch = false]]]])
:param string $field: Field name
:param string $match: Text portion to match
:param string $side: Which side of the expression to put the '%' wildcard on
:param bool $escape: Whether to escape values and identifiers
:param bool $insensitiveSearch: Whether to force a case-insensitive search
:returns: ``BaseBuilder`` instance (method chaining)
:rtype: ``BaseBuilder``
Adds a ``LIKE`` clause to a ``HAVING`` part of the query, separating multiple class with ``OR``.
.. php:method:: notHavingLike($field[, $match = ''[, $side = 'both'[, $escape = null[, $insensitiveSearch = false]]]])
:param string $field: Field name
:param string $match: Text portion to match
:param string $side: Which side of the expression to put the '%' wildcard on
:param bool $escape: Whether to escape values and identifiers
:param bool $insensitiveSearch: Whether to force a case-insensitive search
:returns: ``BaseBuilder`` instance (method chaining)
:rtype: ``BaseBuilder``
Adds a ``NOT LIKE`` clause to a ``HAVING`` part of the query, separating multiple calls with ``AND``.
.. php:method:: orNotHavingLike($field[, $match = ''[, $side = 'both'[, $escape = null[, $insensitiveSearch = false]]]])
:param string $field: Field name
:param string $match: Text portion to match
:param string $side: Which side of the expression to put the '%' wildcard on
:param bool $escape: Whether to escape values and identifiers
:returns: ``BaseBuilder`` instance (method chaining)
:rtype: ``BaseBuilder``
Adds a ``NOT LIKE`` clause to a ``HAVING`` part of the query, separating multiple calls with ``OR``.
.. php:method:: havingGroupStart()
:returns: ``BaseBuilder`` instance (method chaining)
:rtype: ``BaseBuilder``
Starts a group expression for ``HAVING`` clause, using ``AND`` for the conditions inside it.
.. php:method:: orHavingGroupStart()
:returns: ``BaseBuilder`` instance (method chaining)
:rtype: ``BaseBuilder``
Starts a group expression for ``HAVING`` clause, using ``OR`` for the conditions inside it.
.. php:method:: notHavingGroupStart()
:returns: ``BaseBuilder`` instance (method chaining)
:rtype: ``BaseBuilder``
Starts a group expression for ``HAVING`` clause, using ``AND NOT`` for the conditions inside it.
.. php:method:: orNotHavingGroupStart()
:returns: ``BaseBuilder`` instance (method chaining)
:rtype: ``BaseBuilder``
Starts a group expression for ``HAVING`` clause, using ``OR NOT`` for the conditions inside it.
.. php:method:: havingGroupEnd()
:returns: ``BaseBuilder`` instance (method chaining)
:rtype: ``BaseBuilder``
Ends a group expression for ``HAVING`` clause.
.. php:method:: groupBy($by[, $escape = null])
:param mixed $by: Field(s) to group by; string or array
:returns: ``BaseBuilder`` instance (method chaining)
:rtype: ``BaseBuilder``
Adds a ``GROUP BY`` clause to a query.
.. php:method:: orderBy($orderby[, $direction = ''[, $escape = null]])
:param string $orderby: Field to order by
:param string $direction: The order requested - ASC, DESC or random
:param bool $escape: Whether to escape values and identifiers
:returns: ``BaseBuilder`` instance (method chaining)
:rtype: ``BaseBuilder``
Adds an ``ORDER BY`` clause to a query.
.. php:method:: limit($value[, $offset = 0])
:param int $value: Number of rows to limit the results to
:param int $offset: Number of rows to skip
:returns: ``BaseBuilder`` instance (method chaining)
:rtype: ``BaseBuilder``
Adds ``LIMIT`` and ``OFFSET`` clauses to a query.
.. php:method:: offset($offset)
:param int $offset: Number of rows to skip
:returns: ``BaseBuilder`` instance (method chaining)
:rtype: ``BaseBuilder``
Adds an ``OFFSET`` clause to a query.
.. php:method:: union($union)
:param BaseBulder|Closure $union: Union query
:returns: ``BaseBuilder`` instance (method chaining)
:rtype: ``BaseBuilder``
Adds a ``UNION`` clause.
.. php:method:: unionAll($union)
:param BaseBulder|Closure $union: Union query
:returns: ``BaseBuilder`` instance (method chaining)
:rtype: ``BaseBuilder``
Adds a ``UNION ALL`` clause.
.. php:method:: set($key[, $value = ''[, $escape = null]])
:param mixed $key: Field name, or an array of field/value pairs
:param mixed $value: Field value, if $key is a single field
:param bool $escape: Whether to escape values
:returns: ``BaseBuilder`` instance (method chaining)
:rtype: ``BaseBuilder``
Adds field/value pairs to be passed later to ``insert()``, ``update()`` or ``replace()``.
.. php:method:: insert([$set = null[, $escape = null]])
:param array $set: An associative array of field/value pairs
:param bool $escape: Whether to escape values
:returns: ``true`` on success, ``false`` on failure
:rtype: bool
Compiles and executes an ``INSERT`` statement.
.. php:method:: insertBatch([$set = null[, $escape = null[, $batch_size = 100]]])
:param array $set: Data to insert
:param bool $escape: Whether to escape values
:param int $batch_size: Count of rows to insert at once
:returns: Number of rows inserted or ``false`` on no data to perform an insert operation
:rtype: int|false
Compiles and executes batch ``INSERT`` statements.
.. note:: When more than ``$batch_size`` rows are provided, multiple
``INSERT`` queries will be executed, each trying to insert
up to ``$batch_size`` rows.
.. php:method:: setInsertBatch($key[, $value = ''[, $escape = null]])
.. deprecated:: 4.3.0
Use :php:meth:`CodeIgniter\\Database\\BaseBuilder::setData()` instead.
:param mixed $key: Field name or an array of field/value pairs
:param string $value: Field value, if $key is a single field
:param bool $escape: Whether to escape values
:returns: ``BaseBuilder`` instance (method chaining)
:rtype: ``BaseBuilder``
Adds field/value pairs to be inserted in a table later via ``insertBatch()``.
.. important:: This method is deprecated. It will be removed in future releases.
.. php:method:: upsert([$set = null[, $escape = null]])
:param array $set: An associative array of field/value pairs
:param bool $escape: Whether to escape values
:returns: ``true`` on success, ``false`` on failure
:rtype: bool
Compiles and executes an ``UPSERT`` statement.
.. php:method:: upsertBatch([$set = null[, $escape = null[, $batch_size = 100]]])
:param array $set: Data to upsert
:param bool $escape: Whether to escape values
:param int $batch_size: Count of rows to upsert at once
:returns: Number of rows upserted or ``false`` on failure
:rtype: int|false
Compiles and executes batch ``UPSERT`` statements.
.. note:: MySQL uses ``ON DUPLICATE KEY UPDATE``, the affected-rows value
per row is 1 if the row is inserted as a new row, 2 if an existing row
is updated, and 0 if an existing row is set to its current values.
.. note:: When more than ``$batch_size`` rows are provided, multiple
``UPSERT`` queries will be executed, each trying to upsert
up to ``$batch_size`` rows.
.. php:method:: update([$set = null[, $where = null[, $limit = null]]])
:param array $set: An associative array of field/value pairs
:param string $where: The WHERE clause
:param int $limit: The LIMIT clause
:returns: ``true`` on success, ``false`` on failure
:rtype: bool
Compiles and executes an ``UPDATE`` statement.
.. php:method:: updateBatch([$set = null[, $constraints = null[, $batchSize = 100]]])
:param array|object|null $set: Field name, or an associative array of field/value pairs
:param array|RawSql|string|null $constraints: The field or fields used as keys to update on.
:param int $batchSize: Count of conditions to group in a single query
:returns: Number of rows updated or ``false`` on failure
:rtype: int|false
.. note:: Since v4.3.0, the types of the parameters ``$set`` and ``$constraints`` have changed.
Compiles and executes batch ``UPDATE`` statements.
The ``$constraints`` parameter takes a comma delimited string of columns, an array, an associative array, or ``RawSql``.
.. note:: When more than ``$batchSize`` field/value pairs are provided,
multiple queries will be executed, each handling up to ``$batchSize``
field/value pairs. If we set ``$batchSize`` to 0, then all field/value
pairs will be executed in one query.
.. php:method:: updateFields($set, [$addToDefault = false, [$ignore = null]])
.. versionadded:: 4.3.0
:param mixed $set: Row of columns or array of rows, a row is an array or object
:param bool $addToDefault: Adds an additional column than those in dataset
:param bool $ignore: An array of columns to ignore from those in $set
:returns: ``BaseBuilder`` instance (method chaining)
:rtype: ``BaseBuilder``
Used with ``updateBatch()`` and ``upsertBatch()`` methods. This defines the fields which will be updated.
.. php:method:: onConstraint($set)
.. versionadded:: 4.3.0
:param mixed $set: A set of fields or field used has keys or constraints
:returns: ``BaseBuilder`` instance (method chaining)
:rtype: ``BaseBuilder``
Used with ``updateBatch()`` and ``upsertBatch()`` methods. This takes a comma delimited string of columns, and array, associative array, or RawSql.
.. php:method:: setData($set, [$escape = null, [$alias = '']])
.. versionadded:: 4.3.0
:param mixed $set: Row of columns or array of rows, a row is an array or object
:param bool $escape: Whether to escape values
:param bool $alias: A table alias for dataset
:returns: ``BaseBuilder`` instance (method chaining)
:rtype: ``BaseBuilder``
Used for ``*Batch()`` methods to set data for insert, update, upsert.
.. php:method:: setUpdateBatch($key[, $value = ''[, $escape = null]])
.. deprecated:: 4.3.0
Use :php:meth:`CodeIgniter\\Database\\BaseBuilder::setData()` instead.
:param mixed $key: Field name or an array of field/value pairs
:param string $value: Field value, if $key is a single field
:param bool $escape: Whether to escape values
:returns: ``BaseBuilder`` instance (method chaining)
:rtype: ``BaseBuilder``
Adds field/value pairs to be updated in a table later via ``updateBatch()``.
.. important:: This method is deprecated. It will be removed in future releases.
.. php:method:: replace([$set = null])
:param array $set: An associative array of field/value pairs
:returns: ``true`` on success, ``false`` on failure
:rtype: bool
Compiles and executes a ``REPLACE`` statement.
.. php:method:: delete([$where = ''[, $limit = null[, $reset_data = true]]])
:param string $where: The WHERE clause
:param int $limit: The LIMIT clause
:param bool $reset_data: true to reset the query "write" clause
:returns: ``BaseBuilder`` instance (method chaining) or ``false`` on failure
:rtype: ``BaseBuilder|false``
Compiles and executes a ``DELETE`` query.
.. php:method:: deleteBatch([$set = null[, $constraints = null[, $batchSize = 100]]])
:param array|object|null $set: Field name, or an associative array of field/value pairs
:param array|RawSql|string|null $constraints: The field or fields used as keys to delete on.
:param int $batchSize: Count of conditions to group in a single query
:returns: Number of rows deleted or ``false`` on failure
:rtype: int|false
Compiles and executes batch ``DELETE`` query.
.. php:method:: increment($column[, $value = 1])
:param string $column: The name of the column to increment
:param int $value: The amount to increment in the column
Increments the value of a field by the specified amount. If the field
is not a numeric field, like a ``VARCHAR``, it will likely be replaced
with ``$value``.
.. php:method:: decrement($column[, $value = 1])
:param string $column: The name of the column to decrement
:param int $value: The amount to decrement in the column
Decrements the value of a field by the specified amount. If the field
is not a numeric field, like a ``VARCHAR``, it will likely be replaced
with ``$value``.
.. php:method:: truncate()
:returns: ``true`` on success, ``false`` on failure, string on test mode
:rtype: bool|string
Executes a ``TRUNCATE`` statement on a table.
.. note:: If the database platform in use doesn't support ``TRUNCATE``,
a ``DELETE`` statement will be used instead.
.. php:method:: emptyTable()
:returns: ``true`` on success, ``false`` on failure
:rtype: bool
Deletes all records from a table via a ``DELETE`` statement.
.. php:method:: getCompiledSelect([$reset = true])
:param bool $reset: Whether to reset the current QB values or not
:returns: The compiled SQL statement as a string
:rtype: string
Compiles a ``SELECT`` statement and returns it as a string.
.. php:method:: getCompiledInsert([$reset = true])
:param bool $reset: Whether to reset the current QB values or not
:returns: The compiled SQL statement as a string
:rtype: string
Compiles an ``INSERT`` statement and returns it as a string.
.. php:method:: getCompiledUpdate([$reset = true])
:param bool $reset: Whether to reset the current QB values or not
:returns: The compiled SQL statement as a string
:rtype: string
Compiles an ``UPDATE`` statement and returns it as a string.
.. php:method:: getCompiledDelete([$reset = true])
:param bool $reset: Whether to reset the current QB values or not
:returns: The compiled SQL statement as a string
:rtype: string
Compiles a ``DELETE`` statement and returns it as a string.