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

If you intend to write your own queries you can disable this
class in your database config file, allowing the core database library
and adapter to utilize fewer resources.

The key thing to notice in the above example is that the second query did not
utilize $this->db->from() and did not pass a table name into the first
parameter. The reason for this outcome is because the query has not been
executed using $this->db->get() which resets values or reset directly
using $this->db->reset_query().

$this->db->get_where()

Identical to the above function except that it permits you to add a
“where” clause in the second parameter, instead of using the db->where()
function:

If you are selecting all (*) from a table you do not need to
use this function. When omitted, CodeIgniter assumes that you wish
to select all fields and automatically adds ‘SELECT *’.

$this->db->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.

Writes a SELECTMAX(field) portion for your query. You can optionally
include a second parameter to rename the resulting field.

$this->db->select_max('age');$query=$this->db->get('members');// Produces: SELECT MAX(age) as age FROM members$this->db->select_max('age','member_age');$query=$this->db->get('members');// Produces: SELECT MAX(age) as member_age FROM members

$this->db->select_min()

Writes a “SELECT MIN(field)” portion for your query. As with
select_max(), You can optionally include a second parameter to rename
the resulting field.

$this->db->select_min('age');$query=$this->db->get('members');// Produces: SELECT MIN(age) as age FROM members

$this->db->select_avg()

Writes a “SELECT AVG(field)” portion for your query. As with
select_max(), You can optionally include a second parameter to rename
the resulting field.

$this->db->select_avg('age');$query=$this->db->get('members');// Produces: SELECT AVG(age) as age FROM members

$this->db->select_sum()

Writes a “SELECT SUM(field)” portion for your query. As with
select_max(), You can optionally include a second parameter to rename
the resulting field.

$this->db->select_sum('age');$query=$this->db->get('members');// Produces: SELECT SUM(age) as age FROM members

Permits you to determine the number of rows in a particular Active
Record query. Queries will accept Query Builder restrictors such as
where(), or_where(), like(), or_like(), etc. Example:

echo$this->db->count_all_results('my_table');// Produces an integer, like 25$this->db->like('title','match');$this->db->from('my_table');echo$this->db->count_all_results();// Produces an integer, like 17

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
second parameter:

echo$this->db->count_all_results('my_table',FALSE);

$this->db->count_all()

Permits you to determine the number of rows in a particular table.
Submit the table name in the first parameter. Example:

The key thing to notice in the above example is that the second query did not
utlize $this->db->from() nor did it pass a table name into the first
parameter. The reason this worked is because the query has not been executed
using $this->db->insert() which resets values or reset directly using
$this->db->reset_query().

Note

This method doesn’t work for batched inserts.

$this->db->insert_batch()

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
function. Here is an example using an array:

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.

set() will also accept an optional third parameter ($escape), that
will prevent data from being escaped if set to FALSE. To illustrate the
difference, here is set() used both with and without the escape
parameter.

While not “true” caching, Query Builder enables you to save (or “cache”)
certain parts of your queries for reuse at a later point in your
script’s execution. Normally, when an Query Builder call is completed,
all stored information is reset for the next call. With caching, you can
prevent this reset, and reuse information easily.

Cached calls are cumulative. If you make 2 cached select() calls, and
then 2 uncached select() calls, this will result in 4 select() calls.
There are three Caching functions available:

$this->db->start_cache()

This function must be called to begin caching. All Query Builder queries
of the correct type (see below for supported queries) are stored for
later use.

Resetting Query Builder allows you to start fresh with your query without
executing it first using a method like $this->db->get() or $this->db->insert().
Just like the methods that execute a query, this will not reset items you’ve
cached using Query Builder Caching.

This is useful in situations where you are using Query Builder to generate SQL
(ex. $this->db->get_compiled_select()) but then choose to, for instance,
run the query:

// Note that the second parameter of the get_compiled_select method is FALSE$sql=$this->db->select(array('field1','field2'))->where('field3',5)->get_compiled_select('mytable',FALSE);// ...// Do something crazy with the SQL code... like add it to a cron script for// later execution or something...// ...$data=$this->db->get()->result_array();// Would execute and return an array of results of the following query:// SELECT field1, field1 from mytable where field3 = 5;

Note

Double calls to get_compiled_select() while you’re using the
Query Builder Caching functionality and NOT resetting your queries
will results in the cache being merged twice. That in turn will
i.e. if you’re caching a select() - select the same field twice.