diff options
Diffstat (limited to 'user_guide_src/source/database/query_builder.rst')
| -rw-r--r-- | user_guide_src/source/database/query_builder.rst | 745 |
1 files changed, 611 insertions, 134 deletions
diff --git a/user_guide_src/source/database/query_builder.rst b/user_guide_src/source/database/query_builder.rst index 5bfdfdb52..b06396e96 100644 --- a/user_guide_src/source/database/query_builder.rst +++ b/user_guide_src/source/database/query_builder.rst @@ -2,8 +2,8 @@ Query Builder Class ################### -CodeIgniter gives you access to a Query Builder class. This pattern -allows information to be retrieved, inserted, and updated in your +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 @@ -19,7 +19,9 @@ system. class in your database config file, allowing the core database library and adapter to utilize fewer resources. -.. contents:: Page Contents +.. contents:: + :local: + :depth: 1 ************** Selecting Data @@ -27,8 +29,7 @@ Selecting Data The following functions allow you to build SQL **SELECT** statements. -$this->db->get() -================ +**$this->db->get()** Runs the selection query and returns the result. Can be used by itself to retrieve all records from a table:: @@ -39,7 +40,9 @@ The second and third parameters enable you to set a limit and offset clause:: $query = $this->db->get('mytable', 10, 20); - // Produces: SELECT * FROM mytable LIMIT 20, 10 (in MySQL. Other databases have slightly different syntax) + + // Executes: SELECT * FROM mytable LIMIT 20, 10 + // (in MySQL. Other databases have slightly different syntax) You'll notice that the above function is assigned to a variable named $query, which can be used to show the results:: @@ -54,10 +57,9 @@ $query, which can be used to show the results:: Please visit the :doc:`result functions <results>` page for a full discussion regarding result generation. -$this->db->get_compiled_select() -================================ +**$this->db->get_compiled_select()** -Compiles the selection query just like `$this->db->get()`_ but does not *run* +Compiles the selection query just like **$this->db->get()** but does not *run* the query. This method simply returns the SQL query as a string. Example:: @@ -65,28 +67,27 @@ Example:: $sql = $this->db->get_compiled_select('mytable'); echo $sql; - // Produces string: SELECT * FROM mytable + // Prints string: SELECT * FROM mytable The second parameter enables you to set whether or not the query builder query will be reset (by default it will be reset, just like when using `$this->db->get()`):: echo $this->db->limit(10,20)->get_compiled_select('mytable', FALSE); - // Produces string: SELECT * FROM mytable LIMIT 20, 10 + + // Prints string: SELECT * FROM mytable LIMIT 20, 10 // (in MySQL. Other databases have slightly different syntax) echo $this->db->select('title, content, date')->get_compiled_select(); - // Produces string: SELECT title, content, date FROM mytable LIMIT 20, 10 + // Prints string: SELECT title, content, date FROM mytable LIMIT 20, 10 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 +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()`_. +executed using **$this->db->get()** which resets values or reset directly +using **$this->db->reset_query()**. - -$this->db->get_where() -====================== +**$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() @@ -98,32 +99,32 @@ Please read the about the where function below for more information. .. note:: get_where() was formerly known as getwhere(), which has been removed -$this->db->select() -=================== +**$this->db->select()** Permits you to write the SELECT portion of your query:: $this->db->select('title, content, date'); - $query = $this->db->get('mytable'); // Produces: SELECT title, content, date FROM mytable + $query = $this->db->get('mytable'); + // Executes: SELECT title, content, date FROM mytable .. note:: If you are selecting all (\*) from a table you do not need to - use this function. When omitted, CodeIgniter assumes you wish to SELECT * + 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 -with backticks. This is useful if you need a compound select statement. +``$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. :: $this->db->select('(SELECT SUM(payments.amount) FROM payments WHERE payments.invoice_id=4') AS amount_paid', FALSE); $query = $this->db->get('mytable'); +**$this->db->select_max()** -$this->db->select_max() -======================= - -Writes a "SELECT MAX(field)" portion for your query. You can optionally +Writes a ``SELECT MAX(field)`` portion for your query. You can optionally include a second parameter to rename the resulting field. :: @@ -135,8 +136,7 @@ include a second parameter to rename the resulting field. $query = $this->db->get('members'); // Produces: SELECT MAX(age) as member_age FROM members -$this->db->select_min() -======================= +**$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 @@ -148,8 +148,7 @@ the resulting field. $query = $this->db->get('members'); // Produces: SELECT MIN(age) as age FROM members -$this->db->select_avg() -======================= +**$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 @@ -161,8 +160,7 @@ the resulting field. $query = $this->db->get('members'); // Produces: SELECT AVG(age) as age FROM members -$this->db->select_sum() -======================= +**$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 @@ -173,9 +171,7 @@ the resulting field. $this->db->select_sum('age'); $query = $this->db->get('members'); // Produces: SELECT SUM(age) as age FROM members - -$this->db->from() -================= +**$this->db->from()** Permits you to write the FROM portion of your query:: @@ -186,8 +182,7 @@ Permits you to write the FROM portion of your query:: .. note:: As shown earlier, the FROM portion of your query can be specified in the $this->db->get() function, so use whichever method you prefer. -$this->db->join() -================= +**$this->db->join()** Permits you to write the JOIN portion of your query:: @@ -211,8 +206,11 @@ outer, and right outer. $this->db->join('comments', 'comments.id = blogs.id', 'left'); // Produces: LEFT JOIN comments ON comments.id = blogs.id -$this->db->where() -================== +************************* +Looking for Specific Data +************************* + +**$this->db->where()** This function enables you to set **WHERE** clauses using one of four methods: @@ -239,6 +237,7 @@ methods: // WHERE name = 'Joe' AND title = 'boss' AND status = 'active' #. **Custom key/value method:** + You can include an operator in the first parameter in order to control the comparison: @@ -269,17 +268,14 @@ methods: $this->db->where($where); -$this->db->where() accepts an optional third parameter. If you set it to -FALSE, CodeIgniter will not try to protect your field or table names -with backticks. +``$this->db->where()`` accepts an optional third parameter. If you set it to +FALSE, CodeIgniter will not try to protect your field or table names. :: $this->db->where('MATCH (field) AGAINST ("value")', NULL, FALSE); - -$this->db->or_where() -===================== +**$this->db->or_where()** This function is identical to the one above, except that multiple instances are joined by OR:: @@ -290,8 +286,7 @@ instances are joined by OR:: .. note:: or_where() was formerly known as orwhere(), which has been removed. -$this->db->where_in() -===================== +**$this->db->where_in()** Generates a WHERE field IN ('item', 'item') SQL query joined with AND if appropriate @@ -303,8 +298,7 @@ appropriate // Produces: WHERE username IN ('Frank', 'Todd', 'James') -$this->db->or_where_in() -======================== +**$this->db->or_where_in()** Generates a WHERE field IN ('item', 'item') SQL query joined with OR if appropriate @@ -315,9 +309,7 @@ appropriate $this->db->or_where_in('username', $names); // Produces: OR username IN ('Frank', 'Todd', 'James') - -$this->db->where_not_in() -========================= +**$this->db->where_not_in()** Generates a WHERE field NOT IN ('item', 'item') SQL query joined with AND if appropriate @@ -329,8 +321,7 @@ AND if appropriate // Produces: WHERE username NOT IN ('Frank', 'Todd', 'James') -$this->db->or_where_not_in() -============================ +**$this->db->or_where_not_in()** Generates a WHERE field NOT IN ('item', 'item') SQL query joined with OR if appropriate @@ -341,9 +332,11 @@ if appropriate $this->db->or_where_not_in('username', $names); // Produces: OR username NOT IN ('Frank', 'Todd', 'James') +************************ +Looking for Similar Data +************************ -$this->db->like() -================= +**$this->db->like()** This method enables you to generate **LIKE** clauses, useful for doing searches. @@ -383,8 +376,7 @@ searches. // WHERE `title` LIKE '%match%' ESCAPE '!' AND `page1` LIKE '%match%' ESCAPE '!' AND `page2` LIKE '%match%' ESCAPE '!' -$this->db->or_like() -==================== +**$this->db->or_like()** This method is identical to the one above, except that multiple instances are joined by OR:: @@ -394,16 +386,14 @@ instances are joined by OR:: .. note:: ``or_like()`` was formerly known as ``orlike()``, which has been removed. -$this->db->not_like() -===================== +**$this->db->not_like()** This method is identical to ``like()``, except that it generates NOT LIKE statements:: $this->db->not_like('title', 'match'); // WHERE `title` NOT LIKE '%match% ESCAPE '!' -$this->db->or_not_like() -======================== +**$this->db->or_not_like()** This method is identical to ``not_like()``, except that multiple instances are joined by OR:: @@ -412,8 +402,7 @@ instances are joined by OR:: $this->db->or_not_like('body', 'match'); // WHERE `title` LIKE '%match% OR `body` NOT LIKE '%match%' ESCAPE '!' -$this->db->group_by() -===================== +**$this->db->group_by()** Permits you to write the GROUP BY portion of your query:: @@ -426,8 +415,7 @@ You can also pass an array of multiple values as well:: .. note:: group_by() was formerly known as groupby(), which has been removed. -$this->db->distinct() -===================== +**$this->db->distinct()** Adds the "DISTINCT" keyword to a query @@ -436,9 +424,7 @@ Adds the "DISTINCT" keyword to a query $this->db->distinct(); $this->db->get('table'); // Produces: SELECT DISTINCT * FROM table - -$this->db->having() -=================== +**$this->db->having()** Permits you to write the HAVING portion of your query. There are 2 possible syntaxes, 1 argument or 2:: @@ -462,13 +448,15 @@ setting it to FALSE. $this->db->having('user_id', 45, FALSE); // Produces: HAVING user_id = 45 -$this->db->or_having() -====================== +**$this->db->or_having()** Identical to having(), only separates multiple clauses with "OR". -$this->db->order_by() -===================== +**************** +Ordering results +**************** + +**$this->db->order_by()** Lets you set an ORDER BY clause. @@ -512,8 +500,11 @@ be ignored, unless you specify a numeric seed value. .. note:: Random ordering is not currently supported in Oracle and will default to ASC instead. -$this->db->limit() -================== +**************************** +Limiting or Counting Results +**************************** + +**$this->db->limit()** Lets you limit the number of rows you would like returned by the query:: @@ -525,8 +516,7 @@ The second parameter lets you set a result offset. $this->db->limit(10, 20); // Produces: LIMIT 20, 10 (in MySQL. Other databases have slightly different syntax) -$this->db->count_all_results() -============================== +**$this->db->count_all_results()** Permits you to determine the number of rows in a particular Active Record query. Queries will accept Query Builder restrictors such as @@ -537,8 +527,7 @@ where(), or_where(), like(), or_like(), etc. Example:: $this->db->from('my_table'); echo $this->db->count_all_results(); // Produces an integer, like 17 -$this->db->count_all() -====================== +**$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:: @@ -568,28 +557,23 @@ you to create queries with complex WHERE clauses. Nested groups are supported. E .. note:: groups need to be balanced, make sure every group_start() is matched by a group_end(). -$this->db->group_start() -======================== +**$this->db->group_start()** Starts a new group by adding an opening parenthesis to the WHERE clause of the query. -$this->db->or_group_start() -=========================== +**$this->db->or_group_start()** Starts a new group by adding an opening parenthesis to the WHERE clause of the query, prefixing it with 'OR'. -$this->db->not_group_start() -============================ +**$this->db->not_group_start()** Starts a new group by adding an opening parenthesis to the WHERE clause of the query, prefixing it with 'NOT'. -$this->db->or_not_group_start() -=============================== +**$this->db->or_not_group_start()** Starts a new group by adding an opening parenthesis to the WHERE clause of the query, prefixing it with 'OR NOT'. -$this->db->group_end() -====================== +**$this->db->group_end()** Ends the current group by adding an closing parenthesis to the WHERE clause of the query. @@ -597,8 +581,7 @@ Ends the current group by adding an closing parenthesis to the WHERE clause of t Inserting Data ************** -$this->db->insert() -=================== +**$this->db->insert()** 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 @@ -635,9 +618,9 @@ object. .. note:: All values are escaped automatically producing safer queries. -$this->db->get_compiled_insert() -================================ -Compiles the insertion query just like `$this->db->insert()`_ but does not +**$this->db->get_compiled_insert()** + +Compiles the insertion query just like $this->db->insert() but does not *run* the query. This method simply returns the SQL query as a string. Example:: @@ -654,7 +637,7 @@ Example:: // Produces string: INSERT INTO mytable (title, name, date) VALUES ('My title', 'My name', 'My date') The second parameter enables you to set whether or not the query builder query -will be reset (by default it will be--just like `$this->db->insert()`_):: +will be reset (by default it will be--just like $this->db->insert()):: echo $this->db->set('title', 'My Title')->get_compiled_insert('mytable', FALSE); @@ -672,8 +655,7 @@ using `$this->db->insert()` which resets values or reset directly using .. note:: This method doesn't work for batched inserts. -$this->db->insert_batch() -========================= +**$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 @@ -700,8 +682,11 @@ associative array of values. .. note:: All values are escaped automatically producing safer queries. -$this->db->replace() -==================== +************* +Updating Data +************* + +**$this->db->replace()** This method executes a REPLACE statement, which is basically the SQL standard for (optional) DELETE + INSERT, using *PRIMARY* and *UNIQUE* @@ -729,8 +714,7 @@ will be deleted with our new row data replacing it. Usage of the ``set()`` method is also allowed and all fields are automatically escaped, just like with ``insert()``. -$this->db->set() -================ +**$this->db->set()** This function enables you to set values for inserts or updates. @@ -788,12 +772,7 @@ Or an object:: $this->db->set($object); $this->db->insert('mytable'); -************* -Updating Data -************* - -$this->db->update() -=================== +**$this->db->update()** Generates an update string and runs the query based on the data you supply. You can pass an **array** or an **object** to the function. Here @@ -839,9 +818,7 @@ Or as an array:: You may also use the $this->db->set() function described above when performing updates. - -$this->db->update_batch() -========================= +**$this->db->update_batch()** 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 function. @@ -882,8 +859,7 @@ array of values, the third parameter is the where key. due to the very nature of how it works. Instead, ``update_batch()`` returns the number of rows affected. -$this->db->get_compiled_update() -================================ +**$this->db->get_compiled_update()** This works exactly the same way as ``$this->db->get_compiled_insert()`` except that it produces an UPDATE SQL string instead of an INSERT SQL string. @@ -896,8 +872,7 @@ For more information view documentation for `$this->db->get_compiled_insert()`. Deleting Data ************* -$this->db->delete() -=================== +**$this->db->delete()** Generates a delete SQL string and runs the query. @@ -930,17 +905,14 @@ delete data from more than 1 table. If you want to delete all data from a table, you can use the truncate() function, or empty_table(). -$this->db->empty_table() -======================== +**$this->db->empty_table()** Generates a delete SQL string and runs the query.:: $this->db->empty_table('mytable'); // Produces: DELETE FROM mytable - -$this->db->truncate() -===================== +**$this->db->truncate()** Generates a truncate SQL string and runs the query. @@ -959,12 +931,12 @@ Generates a truncate SQL string and runs the query. .. note:: If the TRUNCATE command isn't available, truncate() will execute as "DELETE FROM table". -$this->db->get_compiled_delete() -================================ +**$this->db->get_compiled_delete()** + This works exactly the same way as ``$this->db->get_compiled_insert()`` except that it produces a DELETE SQL string instead of an INSERT SQL string. -For more information view documentation for `$this->db->get_compiled_insert()`_. +For more information view documentation for $this->db->get_compiled_insert(). *************** Method Chaining @@ -994,23 +966,23 @@ 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->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. -$this->db->stop_cache() -======================= +**$this->db->stop_cache()** This function can be called to stop caching. -$this->db->flush_cache() -======================== +**$this->db->flush_cache()** This function deletes all items from the Query Builder cache. +An example of caching +--------------------- + Here's a usage example:: $this->db->start_cache(); @@ -1033,8 +1005,11 @@ Here's a usage example:: where, like, group_by, having, order_by, set -$this->db->reset_query() -======================== +*********************** +Resetting Query Builder +*********************** + +**$this->db->reset_query()** 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(). @@ -1063,4 +1038,506 @@ run the query:: .. 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.
\ No newline at end of file + i.e. if you're caching a ``select()`` - select the same field twice. + +*************** +Class Reference +*************** + +.. class:: CI_DB_query_builder + + .. method:: reset_query() + + :returns: CI_DB_query_builder instance (method chaining) + :rtype: CI_DB_query_builder + + Resets the current Query Builder state. Useful when you want + to build a query that can be cancelled under certain conditions. + + .. method:: start_cache() + + :returns: CI_DB_query_builder instance (method chaining) + :rtype: CI_DB_query_builder + + Starts the Query Builder cache. + + .. method:: stop_cache() + + :returns: CI_DB_query_builder instance (method chaining) + :rtype: CI_DB_query_builder + + Stops the Query Builder cache. + + .. method:: flush_cache() + + :returns: CI_DB_query_builder instance (method chaining) + :rtype: CI_DB_query_builder + + Empties the Query Builder cache. + + .. method:: set_dbprefix([$prefix = '']) + + :param string $prefix: The new prefix to use + :returns: The DB prefix in use + :rtype: string + + Sets the database prefix, without having to reconnect. + + .. method:: dbprefix([$table = '']) + + :param string $table: The table name to prefix + :returns: The prefixed table name + :rtype: string + + Prepends a database prefix, if one exists in configuration. + + .. method:: count_all_results([$table = '']) + + :param string $table: Table name + :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. + + .. method:: get([$table = ''[, $limit = NULL[, $offset = NULL]]]) + + :param string $table: The table to query + :param int $limit: The LIMIT clause + :param int $offset: The OFFSET clause + :returns: CI_DB_result instance (method chaining) + :rtype: CI_DB_result + + Compiles and runs SELECT statement based on the already + called Query Builder methods. + + .. method:: get_where([$table = ''[, $where = NULL[, $limit = NULL[, $offset = NULL]]]]) + + :param mixed $table: The table(s) to fetch data from; string or array + :param string $where: The WHERE clause + :param int $limit: The LIMIT clause + :param int $offset: The OFFSET clause + :returns: CI_DB_result instance (method chaining) + :rtype: CI_DB_result + + Same as ``get()``, but also allows the WHERE to be added directly. + + .. method:: select([$select = '*'[, $escape = NULL]]) + + :param string $select: The SELECT portion of a query + :param bool $escape: Whether to escape values and identifiers + :returns: CI_DB_query_builder instance (method chaining) + :rtype: CI_DB_query_builder + + Adds a SELECT clause to a query. + + .. method:: select_avg([$select = ''[, $alias = '']]) + + :param string $select: Field to compute the average of + :param string $alias: Alias for the resulting value name + :returns: CI_DB_query_builder instance (method chaining) + :rtype: CI_DB_query_builder + + Adds a SELECT AVG(field) clause to a query. + + .. method:: select_max([$select = ''[, $alias = '']]) + + :param string $select: Field to compute the maximum of + :param string $alias: Alias for the resulting value name + :returns: CI_DB_query_builder instance (method chaining) + :rtype: CI_DB_query_builder + + Adds a SELECT MAX(field) clause to a query. + + .. method:: select_min([$select = ''[, $alias = '']]) + + :param string $select: Field to compute the minimum of + :param string $alias: Alias for the resulting value name + :returns: CI_DB_query_builder instance (method chaining) + :rtype: CI_DB_query_builder + + Adds a SELECT MIN(field) clause to a query. + + .. method:: select_sum([$select = ''[, $alias = '']]) + + :param string $select: Field to compute the sum of + :param string $alias: Alias for the resulting value name + :returns: CI_DB_query_builder instance (method chaining) + :rtype: CI_DB_query_builder + + Adds a SELECT SUM(field) clause to a query. + + .. method:: distinct([$val = TRUE]) + + :param bool $val: Desired value of the "distinct" flag + :returns: CI_DB_query_builder instance (method chaining) + :rtype: CI_DB_query_builder + + Sets a flag which tells the query builder to add + a DISTINCT clause to the SELECT portion of the query. + + .. method:: from($from) + + :param mixed $from: Table name(s); string or array + :returns: CI_DB_query_builder instance (method chaining) + :rtype: CI_DB_query_builder + + Specifies the FROM clause of a query. + + .. method:: join($table, $cond[, $type = ''[, $escape = NULL]]) + + :param string $table: Table name to join + :param string $cond: The JOIN ON condition + :param string $type: The JOIN type + :param bool $escape: Whether to escape values and identifiers + :returns: CI_DB_query_builder instance (method chaining) + :rtype: CI_DB_query_builder + + Adds a JOIN clause to a query. + + .. method:: where($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 boolean $escape: Whether to escape values and identifiers + :returns: DB_query_builder instance + :rtype: object + + Generates the WHERE portion of the query. + Separates multiple calls with 'AND'. + + .. method:: or_where($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 boolean $escape: Whether to escape values and identifiers + :returns: DB_query_builder instance + :rtype: object + + Generates the WHERE portion of the query. + Separates multiple calls with 'OR'. + + .. method:: or_where_in([$key = NULL[, $values = NULL[, $escape = NULL]]]) + + :param string $key: The field to search + :param array $values: The values searched on + :param boolean $escape: Whether to escape values and identifiers + :returns: DB_query_builder instance + :rtype: object + + Generates a WHERE field IN('item', 'item') SQL query, + joined with 'OR' if appropriate. + + .. method:: or_where_not_in([$key = NULL[, $values = NULL[, $escape = NULL]]]) + + :param string $key: The field to search + :param array $values: The values searched on + :param boolean $escape: Whether to escape values and identifiers + :returns: DB_query_builder instance + :rtype: object + + Generates a WHERE field NOT IN('item', 'item') SQL query, + joined with 'OR' if appropriate. + + .. method:: where_in([$key = NULL[, $values = NULL[, $escape = NULL]]]) + + :param string $key: Name of field to examine + :param array $values: Array of target values + :param boolean $escape: Whether to escape values and identifiers + :returns: DB_query_builder instance + :rtype: object + + Generates a WHERE field IN('item', 'item') SQL query, + joined with 'AND' if appropriate. + + .. method:: where_not_in([$key = NULL[, $values = NULL[, $escape = NULL]]]) + + :param string $key: Name of field to examine + :param array $values: Array of target values + :param boolean $escape: Whether to escape values and identifiers + :returns: DB_query_builder instance + :rtype: object + + Generates a WHERE field NOT IN('item', 'item') SQL query, + joined with 'AND' if appropriate. + + .. method:: group_start() + + :returns: CI_DB_query_builder instance (method chaining) + :rtype: CI_DB_query_builder + + Starts a group expression, using ANDs for the conditions inside it. + + .. method:: or_group_start() + + :returns: CI_DB_query_builder instance (method chaining) + :rtype: CI_DB_query_builder + + Starts a group expression, using ORs for the conditions inside it. + + .. method:: not_group_start() + + :returns: CI_DB_query_builder instance (method chaining) + :rtype: CI_DB_query_builder + + Starts a group expression, using AND NOTs for the conditions inside it. + + .. method:: or_not_group_start() + + :returns: CI_DB_query_builder instance (method chaining) + :rtype: CI_DB_query_builder + + Starts a group expression, using OR NOTs for the conditions inside it. + + .. method:: group_end() + + :returns: DB_query_builder instance + :rtype: object + + Ends a group expression. + + .. method:: like($field[, $match = ''[, $side = 'both'[, $escape = NULL]]]) + + :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: CI_DB_query_builder instance (method chaining) + :rtype: CI_DB_query_builder + + Adds a LIKE clause to a query, separating multiple calls with AND. + + .. method:: or_like($field[, $match = ''[, $side = 'both'[, $escape = NULL]]]) + + :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: CI_DB_query_builder instance (method chaining) + :rtype: CI_DB_query_builder + + Adds a LIKE clause to a query, separating multiple class with OR. + + .. method:: not_like($field[, $match = ''[, $side = 'both'[, $escape = NULL]]]) + + :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: CI_DB_query_builder instance (method chaining) + :rtype: CI_DB_query_builder + + Adds a NOT LIKE clause to a query, separating multiple calls with AND. + + .. method:: or_not_like($field[, $match = ''[, $side = 'both'[, $escape = NULL]]]) + + :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: CI_DB_query_builder instance (method chaining) + :rtype: CI_DB_query_builder + + Adds a NOT LIKE clause to a query, separating multiple calls with OR. + + .. 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: CI_DB_query_builder instance (method chaining) + :rtype: CI_DB_query_builder + + Adds a HAVING clause to a query, separating multiple calls with AND. + + .. method:: or_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: CI_DB_query_builder instance (method chaining) + :rtype: CI_DB_query_builder + + Adds a HAVING clause to a query, separating multiple calls with OR. + + .. method:: group_by($by[, $escape = NULL]) + + :param mixed $by: Field(s) to group by; string or array + :returns: CI_DB_query_builder instance (method chaining) + :rtype: CI_DB_query_builder + + Adds a GROUP BY clause to a query. + + .. method:: order_by($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: CI_DB_query_builder instance (method chaining) + :rtype: CI_DB_query_builder + + Adds an ORDER BY clause to a query. + + .. 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: CI_DB_query_builder instance (method chaining) + :rtype: CI_DB_query_builder + + Adds LIMIT and OFFSET clauses to a query. + + .. method:: offset($offset) + + :param int $offset: Number of rows to skip + :returns: CI_DB_query_builder instance (method chaining) + :rtype: CI_DB_query_builder + + Adds an OFFSET clause to a query. + + .. method:: set($key[, $value = ''[, $escape = NULL]]) + + :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 and identifiers + :returns: CI_DB_query_builder instance (method chaining) + :rtype: CI_DB_query_builder + + Adds field/value pairs to be passed later to ``insert()``, + ``update()`` or ``replace()``. + + .. method:: insert([$table = ''[, $set = NULL[, $escape = NULL]]]) + + :param string $table: Table name + :param array $set: An associative array of field/value pairs + :param bool $escape: Whether to escape values and identifiers + :returns: TRUE on success, FALSE on failure + :rtype: bool + + Compiles and executes an INSERT statement. + + .. method:: insert_batch([$table = ''[, $set = NULL[, $escape = NULL]]]) + + :param string $table: Table name + :param array $set: Data to insert + :param bool $escape: Whether to escape values and identifiers + :returns: Number of rows inserted or FALSE on failure + :rtype: mixed + + Compiles and executes batch INSERT statements. + + .. method:: set_insert_batch($key[, $value = ''[, $escape = NULL]]) + + :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 and identifiers + :returns: CI_DB_query_builder instance (method chaining) + :rtype: CI_DB_query_builder + + Adds field/value pairs to be inserted in a table later via ``insert_batch()``. + + .. method:: update([$table = ''[, $set = NULL[, $where = NULL[, $limit = NULL]]]]) + + :param string $table: Table name + :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. + + .. method:: update_batch([$table = ''[, $set = NULL[, $value = NULL]]]) + + :param string $table: Table name + :param array $set: Field name, or an associative array of field/value pairs + :param string $value: Field value, if $set is a single field + :returns: Number of rows updated or FALSE on failure + :rtype: mixed + + Compiles and executes batch UPDATE statements. + + .. method:: set_update_batch($key[, $value = ''[, $escape = NULL]]) + + :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 and identifiers + :returns: CI_DB_query_builder instance (method chaining) + :rtype: CI_DB_query_builder + + Adds field/value pairs to be updated in a table later via ``update_batch()``. + + .. method:: replace([$table = ''[, $set = NULL]]) + + :param string $table: Table name + :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. + + .. method:: delete([$table = ''[, $where = ''[, $limit = NULL[, $reset_data = TRUE]]]]) + + :param mixed $table: The table(s) to delete from; string or array + :param string $where: The WHERE clause + :param int $limit: The LIMIT clause + :param bool $reset_data: TRUE to reset the query "write" clause + :returns: CI_DB_query_builder instance (method chaining) or FALSE on failure + :rtype: mixed + + Compiles and executes a DELETE query. + + .. method:: truncate([$table = '']) + + :param string $table: Table name + :returns: TRUE on success, FALSE on failure + :rtype: bool + + 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. + + .. method:: empty_table([$table = '']) + + :param string $table: Table name + :returns: TRUE on success, FALSE on failure + :rtype: bool + + Deletes all records from a table via a DELETE statement. + + .. method:: get_compiled_select([$table = ''[, $reset = TRUE]]) + + :param string $table: Table name + :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. + + .. method:: get_compiled_insert([$table = ''[, $reset = TRUE]]) + + :param string $table: Table name + :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. + + .. method:: get_compiled_update([$table = ''[, $reset = TRUE]]) + + :param string $table: Table name + :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. + + .. method:: get_compiled_delete([$table = ''[, $reset = TRUE]]) + + :param string $table: Table name + :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. |