diff options
Diffstat (limited to 'user_guide_src')
| -rw-r--r-- | user_guide_src/source/changelog.rst | 1 | ||||
| -rw-r--r-- | user_guide_src/source/database/configuration.rst | 3 | ||||
| -rw-r--r-- | user_guide_src/source/database/query_builder.rst | 230 | ||||
| -rw-r--r-- | user_guide_src/source/libraries/input.rst | 40 |
4 files changed, 190 insertions, 84 deletions
diff --git a/user_guide_src/source/changelog.rst b/user_guide_src/source/changelog.rst index 242881c99..f01ff8a5d 100644 --- a/user_guide_src/source/changelog.rst +++ b/user_guide_src/source/changelog.rst @@ -473,6 +473,7 @@ Release Date: Not Released - Changed default value of the ``$xss_clean`` parameter to NULL for all methods that utilize it, the default value is now determined by the ``$config['global_xss_filtering']`` setting. - Added method ``post_get()`` and changed ``get_post()`` to search in GET data first. Both methods' names now properly match their GET/POST data search priorities. - Changed method ``_fetch_from_array()`` to parse array notation in field name. + - Changed method ``_fetch_from_array()`` to allow retrieving multiple fields at once. - Added an option for ``_clean_input_keys()`` to return FALSE instead of terminating the whole script. - Deprecated the ``is_cli_request()`` method, it is now an alias for the new :func:`is_cli()` common function. - Added an ``$xss_clean`` parameter to method ``user_agent()`` and removed the ``$user_agent`` property. diff --git a/user_guide_src/source/database/configuration.rst b/user_guide_src/source/database/configuration.rst index 34cefffbd..9f52ad2a2 100644 --- a/user_guide_src/source/database/configuration.rst +++ b/user_guide_src/source/database/configuration.rst @@ -141,7 +141,8 @@ Query Builder The :doc:`Query Builder Class <query_builder>` is globally enabled or disabled by setting the $query_builder variable in the database -configuration file to TRUE/FALSE (boolean). If you are not using the +configuration file to TRUE/FALSE (boolean). The default setting is TRUE. +If you are not using the query builder class, setting it to FALSE will utilize fewer resources when the database classes are initialized. diff --git a/user_guide_src/source/database/query_builder.rst b/user_guide_src/source/database/query_builder.rst index 5bfdfdb52..3203ff103 100644 --- a/user_guide_src/source/database/query_builder.rst +++ b/user_guide_src/source/database/query_builder.rst @@ -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 @@ -28,7 +30,7 @@ Selecting Data The following functions allow you to build SQL **SELECT** statements. $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 +41,8 @@ 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) + // Produces: 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,13 @@ $query, which can be used to show the results:: Please visit the :doc:`result functions <results>` page for a full discussion regarding result generation. +:returns: DB_Result for a successful "read", + TRUE for a successful "write", FALSE if an error + $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:: @@ -79,14 +85,15 @@ will be reset (by default it will be reset, just like when using `$this->db->get // Produces 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()**. +:returns: The SQL select string $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,8 +105,11 @@ Please read the about the where function below for more information. .. note:: get_where() was formerly known as getwhere(), which has been removed +:returns: DB_Result for a successful "read", + TRUE for a successful "write", FALSE if an error + $this->db->select() -=================== +------------------- Permits you to write the SELECT portion of your query:: @@ -119,9 +129,10 @@ with backticks. This is useful if you need a compound select statement. $this->db->select('(SELECT SUM(payments.amount) FROM payments WHERE payments.invoice_id=4') AS amount_paid', FALSE); $query = $this->db->get('mytable'); +:returns: The query builder object $this->db->select_max() -======================= +----------------------- Writes a "SELECT MAX(field)" portion for your query. You can optionally include a second parameter to rename the resulting field. @@ -135,8 +146,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 +158,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 +170,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 +181,11 @@ the resulting field. $this->db->select_sum('age'); $query = $this->db->get('members'); // Produces: SELECT SUM(age) as age FROM members +:returns: The query builder object + $this->db->from() -================= +----------------- Permits you to write the FROM portion of your query:: @@ -186,8 +196,10 @@ 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. +:returns: The query builder object + $this->db->join() -================= +----------------- Permits you to write the JOIN portion of your query:: @@ -211,8 +223,14 @@ outer, and right outer. $this->db->join('comments', 'comments.id = blogs.id', 'left'); // Produces: LEFT JOIN comments ON comments.id = blogs.id +:returns: The query builder object + +************************* +Looking for Specific Data +************************* + $this->db->where() -================== +------------------ This function enables you to set **WHERE** clauses using one of four methods: @@ -277,9 +295,7 @@ with backticks. $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 +306,10 @@ instances are joined by OR:: .. note:: or_where() was formerly known as orwhere(), which has been removed. +:returns: The query builder object + $this->db->where_in() -===================== +--------------------- Generates a WHERE field IN ('item', 'item') SQL query joined with AND if appropriate @@ -303,8 +321,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 +332,10 @@ appropriate $this->db->or_where_in('username', $names); // Produces: OR username IN ('Frank', 'Todd', 'James') +:returns: The query builder object $this->db->where_not_in() -========================= +------------------------- Generates a WHERE field NOT IN ('item', 'item') SQL query joined with AND if appropriate @@ -329,8 +347,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 +358,15 @@ if appropriate $this->db->or_where_not_in('username', $names); // Produces: OR username NOT IN ('Frank', 'Todd', 'James') +:returns: The query builder object + + +************************ +Looking for Similar Data +************************ $this->db->like() -================= +----------------- This method enables you to generate **LIKE** clauses, useful for doing searches. @@ -383,8 +406,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 +416,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 +432,10 @@ instances are joined by OR:: $this->db->or_not_like('body', 'match'); // WHERE `title` LIKE '%match% OR `body` NOT LIKE '%match%' ESCAPE '!' +:returns: The query builder object + $this->db->group_by() -===================== +--------------------- Permits you to write the GROUP BY portion of your query:: @@ -426,8 +448,10 @@ You can also pass an array of multiple values as well:: .. note:: group_by() was formerly known as groupby(), which has been removed. +:returns: The query builder object + $this->db->distinct() -===================== +--------------------- Adds the "DISTINCT" keyword to a query @@ -436,9 +460,10 @@ Adds the "DISTINCT" keyword to a query $this->db->distinct(); $this->db->get('table'); // Produces: SELECT DISTINCT * FROM table +:returns: The query builder object $this->db->having() -=================== +------------------- Permits you to write the HAVING portion of your query. There are 2 possible syntaxes, 1 argument or 2:: @@ -462,13 +487,18 @@ 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". +:returns: The query builder object + +**************** +Ordering results +**************** + $this->db->order_by() -===================== +--------------------- Lets you set an ORDER BY clause. @@ -512,8 +542,14 @@ be ignored, unless you specify a numeric seed value. .. note:: Random ordering is not currently supported in Oracle and will default to ASC instead. +:returns: The query builder object + +**************************** +Limiting or Counting Results +**************************** + $this->db->limit() -================== +------------------ Lets you limit the number of rows you would like returned by the query:: @@ -525,8 +561,10 @@ 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) +:returns: The query builder object + $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,14 +575,18 @@ where(), or_where(), like(), or_like(), etc. Example:: $this->db->from('my_table'); echo $this->db->count_all_results(); // Produces an integer, like 17 +:returns: Count of all the records returned by a query + $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:: echo $this->db->count_all('my_table'); // Produces an integer, like 25 +:returns: Count of all the records in the specified table + ************** Query grouping ************** @@ -568,37 +610,34 @@ 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. +:returns: The query builder object + ************** Inserting Data ************** $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,8 +674,11 @@ object. .. note:: All values are escaped automatically producing safer queries. +:returns: DB_Query on success, FALSE on failure + $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. @@ -672,8 +714,10 @@ using `$this->db->insert()` which resets values or reset directly using .. note:: This method doesn't work for batched inserts. +:returns: The SQL insert string + $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 +744,14 @@ associative array of values. .. note:: All values are escaped automatically producing safer queries. +:returns: Count of the number of records inserted on success, FALSE on failure + +************* +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 +779,10 @@ 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()``. +:returns: DB_query object on success, FALSE on failure + $this->db->set() -================ +---------------- This function enables you to set values for inserts or updates. @@ -788,12 +840,10 @@ Or an object:: $this->db->set($object); $this->db->insert('mytable'); -************* -Updating Data -************* +:returns: The query builder object $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 +889,10 @@ Or as an array:: You may also use the $this->db->set() function described above when performing updates. +:returns: DB_query object on success, FALSE on failure $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 +933,10 @@ 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. +:returns: Count of the number of records affected on success, FALSE on failure + $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. @@ -892,12 +945,14 @@ For more information view documentation for `$this->db->get_compiled_insert()`. .. note:: This method doesn't work for batched updates. +:returns: The SQL update string + ************* Deleting Data ************* $this->db->delete() -=================== +------------------- Generates a delete SQL string and runs the query. @@ -930,17 +985,21 @@ 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(). +:returns: DB_Query on success, FALSE on failure + $this->db->empty_table() -======================== +------------------------ Generates a delete SQL string and runs the query.:: $this->db->empty_table('mytable'); // Produces: DELETE FROM mytable +:returns: DB_Query on success, FALSE on failure + $this->db->truncate() -===================== +--------------------- Generates a truncate SQL string and runs the query. @@ -959,13 +1018,20 @@ Generates a truncate SQL string and runs the query. .. note:: If the TRUNCATE command isn't available, truncate() will execute as "DELETE FROM table". +:returns: DB_Query on success, FALSE on failure + $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()`_. +:returns: The SQL delete string + + + *************** Method Chaining *************** @@ -994,23 +1060,25 @@ 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. +:returns: void + +An example of caching +--------------------- + Here's a usage example:: $this->db->start_cache(); @@ -1033,8 +1101,12 @@ Here's a usage example:: where, like, group_by, having, order_by, set +*********************** +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 +1135,6 @@ 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. + +:returns: void diff --git a/user_guide_src/source/libraries/input.rst b/user_guide_src/source/libraries/input.rst index f9dbf1686..112347129 100644 --- a/user_guide_src/source/libraries/input.rst +++ b/user_guide_src/source/libraries/input.rst @@ -108,7 +108,7 @@ Class Reference .. method:: post([$index = NULL[, $xss_clean = NULL]]) - :param string $index: POST parameter name + :param mixed $index: POST parameter name :param bool $xss_clean: Whether to apply XSS filtering :returns: $_POST if no parameters supplied, otherwise the POST value if found or NULL if not :rtype: mixed @@ -136,10 +136,20 @@ Class Reference $this->input->post(NULL, TRUE); // returns all POST items with XSS filter $this->input->post(NULL, FALSE); // returns all POST items without XSS filter + + To return an array of multiple POST parameters, pass all the required keys + as an array. + :: + $this->input->post(array('field1', 'field2')); + + Same rule applied here, to retrive the parameters with XSS filtering enabled, set the + second parameter to boolean TRUE. + :: + $this->input->post(array('field1', 'field2'), TRUE); .. method:: get([$index = NULL[, $xss_clean = NULL]]) - :param string $index: GET parameter name + :param mixed $index: GET parameter name :param bool $xss_clean: Whether to apply XSS filtering :returns: $_GET if no parameters supplied, otherwise the GET value if found or NULL if not :rtype: mixed @@ -157,6 +167,16 @@ Class Reference $this->input->get(NULL, TRUE); // returns all GET items with XSS filter $this->input->get(NULL, FALSE); // returns all GET items without XSS filtering + + To return an array of multiple GET parameters, pass all the required keys + as an array. + :: + $this->input->get(array('field1', 'field2')); + + Same rule applied here, to retrive the parameters with XSS filtering enabled, set the + second parameter to boolean TRUE. + :: + $this->input->get(array('field1', 'field2'), TRUE); .. method:: post_get($index[, $xss_clean = NULL]) @@ -188,7 +208,7 @@ Class Reference .. method:: cookie([$index = NULL[, $xss_clean = NULL]]) - :param string $index: COOKIE parameter name + :param mixed $index: COOKIE name :param bool $xss_clean: Whether to apply XSS filtering :returns: $_COOKIE if no parameters supplied, otherwise the COOKIE value if found or NULL if not :rtype: mixed @@ -198,10 +218,15 @@ Class Reference $this->input->cookie('some_cookie'); $this->input->cookie('some_cookie, TRUE); // with XSS filter + + To return an array of multiple cookie values, pass all the required keys + as an array. + :: + $this->input->cookie(array('some_cookie', 'some_cookie2')); .. method:: server($index[, $xss_clean = NULL]) - :param string $index: Value name + :param mixed $index: Value name :param bool $xss_clean: Whether to apply XSS filtering :returns: $_SERVER item value if found, NULL if not :rtype: mixed @@ -211,9 +236,14 @@ Class Reference $this->input->server('some_data'); + To return an array of multiple ``$_SERVER`` values, pass all the required keys + as an array. + :: + $this->input->server(array('SERVER_PROTOCOL', 'REQUEST_URI')); + .. method:: input_stream([$index = NULL[, $xss_clean = NULL]]) - :param string $index: Key name + :param mixed $index: Key name :param bool $xss_clean: Whether to apply XSS filtering :returns: Input stream array if no parameters supplied, otherwise the specified value if found or NULL if not :rtype: mixed |