diff options
Diffstat (limited to 'user_guide_src/source/database')
| -rw-r--r-- | user_guide_src/source/database/active_record.rst | 856 | ||||
| -rw-r--r-- | user_guide_src/source/database/caching.rst | 162 | ||||
| -rw-r--r-- | user_guide_src/source/database/call_function.rst | 39 | ||||
| -rw-r--r-- | user_guide_src/source/database/configuration.rst | 126 | ||||
| -rw-r--r-- | user_guide_src/source/database/connecting.rst | 132 | ||||
| -rw-r--r-- | user_guide_src/source/database/examples.rst | 136 | ||||
| -rw-r--r-- | user_guide_src/source/database/fields.rst | 80 | ||||
| -rw-r--r-- | user_guide_src/source/database/forge.rst | 268 | ||||
| -rw-r--r-- | user_guide_src/source/database/helpers.rst | 98 | ||||
| -rw-r--r-- | user_guide_src/source/database/index.rst | 29 | ||||
| -rw-r--r-- | user_guide_src/source/database/queries.rst | 114 | ||||
| -rw-r--r-- | user_guide_src/source/database/results.rst | 186 | ||||
| -rw-r--r-- | user_guide_src/source/database/table_data.rst | 31 | ||||
| -rw-r--r-- | user_guide_src/source/database/transactions.rst | 127 | ||||
| -rw-r--r-- | user_guide_src/source/database/utilities.rst | 224 |
15 files changed, 2608 insertions, 0 deletions
diff --git a/user_guide_src/source/database/active_record.rst b/user_guide_src/source/database/active_record.rst new file mode 100644 index 000000000..e1fc00bc5 --- /dev/null +++ b/user_guide_src/source/database/active_record.rst @@ -0,0 +1,856 @@ +################### +Active Record Class +################### + +CodeIgniter uses a modified version of the Active Record Database +Pattern. 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 Active Record 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. + +.. contents:: Page Contents + +************** +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:: + + $query = $this->db->get('mytable'); // Produces: SELECT * FROM mytable + +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) + +You'll notice that the above function is assigned to a variable named +$query, which can be used to show the results:: + + $query = $this->db->get('mytable'); + + foreach ($query->result() as $row) + { + echo $row->title; + } + +Please visit the :doc:`result functions <results>` page for a full +discussion regarding result generation. + +$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:: + + $query = $this->db->get_where('mytable', array('id' => $id), $limit, $offset); + +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() +=================== + +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 + + +.. 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 * + +$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('(SELECT SUM(payments.amount) FROM payments WHERE payments.invoice_id=4') AS amount_paid', FALSE); + $query = $this->db->get('mytable'); + + +$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. + +:: + + $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 + + +$this->db->from() +================= + +Permits you to write the FROM portion of your query:: + + $this->db->select('title, content, date'); + $this->db->from('mytable'); + $query = $this->db->get(); // Produces: SELECT title, content, date FROM mytable + +.. 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() +================= + +Permits you to write the JOIN portion of your query:: + + $this->db->select('*'); + $this->db->from('blogs'); + $this->db->join('comments', 'comments.id = blogs.id'); + $query = $this->db->get(); + + // Produces: + // SELECT * FROM blogs // JOIN comments ON comments.id = blogs.id + +Multiple function 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 function. Options are: left, right, outer, inner, left +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() +================== + +This function enables you to set **WHERE** clauses using one of four +methods: + +.. note:: All values passed to this function are escaped automatically, + producing safer queries. + +#. **Simple key/value method:** + + :: + + $this->db->where('name', $name); // Produces: WHERE name = 'Joe' + + Notice that the equal sign is added for you. + + If you use multiple function calls they will be chained together with + AND between them: + + :: + + $this->db->where('name', $name); + $this->db->where('title', $title); + $this->db->where('status', $status); + // 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: + + :: + + $this->db->where('name !=', $name); + $this->db->where('id <', $id); // Produces: WHERE name != 'Joe' AND id < 45 + +#. **Associative array method:** + + :: + + $array = array('name' => $name, 'title' => $title, 'status' => $status); + $this->db->where($array); + // Produces: WHERE name = 'Joe' AND title = 'boss' AND status = 'active' + + You can include your own operators using this method as well: + + :: + + $array = array('name !=' => $name, 'id <' => $id, 'date >' => $date); + $this->db->where($array); + +#. **Custom string:** + You can write your own clauses manually:: + + $where = "name='Joe' AND status='boss' OR status='active'"; + $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('MATCH (field) AGAINST ("value")', NULL, FALSE); + + +$this->db->or_where() +===================== + +This function is identical to the one above, except that multiple +instances are joined by OR:: + + $this->db->where('name !=', $name); + $this->db->or_where('id >', $id); // Produces: WHERE name != 'Joe' OR id > 50 + +.. note:: or_where() was formerly known as orwhere(), which has been + removed. + +$this->db->where_in() +===================== + +Generates a WHERE field IN ('item', 'item') SQL query joined with AND if +appropriate + +:: + + $names = array('Frank', 'Todd', 'James'); + $this->db->where_in('username', $names); + // Produces: WHERE username IN ('Frank', 'Todd', 'James') + + +$this->db->or_where_in() +======================== + +Generates a WHERE field IN ('item', 'item') SQL query joined with OR if +appropriate + +:: + + $names = array('Frank', 'Todd', 'James'); + $this->db->or_where_in('username', $names); + // Produces: OR username IN ('Frank', 'Todd', 'James') + + +$this->db->where_not_in() +========================= + +Generates a WHERE field NOT IN ('item', 'item') SQL query joined with +AND if appropriate + +:: + + $names = array('Frank', 'Todd', 'James'); + $this->db->where_not_in('username', $names); + // Produces: WHERE username NOT IN ('Frank', 'Todd', 'James') + + +$this->db->or_where_not_in() +============================ + +Generates a WHERE field NOT IN ('item', 'item') SQL query joined with OR +if appropriate + +:: + + $names = array('Frank', 'Todd', 'James'); + $this->db->or_where_not_in('username', $names); + // Produces: OR username NOT IN ('Frank', 'Todd', 'James') + + +$this->db->like() +================= + +This function enables you to generate **LIKE** clauses, useful for doing +searches. + +.. note:: All values passed to this function are escaped automatically. + +#. **Simple key/value method:** + + :: + + $this->db->like('title', 'match'); // Produces: WHERE title LIKE '%match%' + + If you use multiple function calls they will be chained together with + AND between them:: + + $this->db->like('title', 'match'); + $this->db->like('body', 'match'); + // WHERE title LIKE '%match%' AND body LIKE '%match% + + If you want to control where the wildcard (%) is placed, you can use + an optional third argument. Your options are 'before', 'after' and + 'both' (which is the default). + + :: + + $this->db->like('title', 'match', 'before'); // Produces: WHERE title LIKE '%match' + $this->db->like('title', 'match', 'after'); // Produces: WHERE title LIKE 'match%' + $this->db->like('title', 'match', 'both'); // Produces: WHERE title LIKE '%match%' + +#. **Associative array method:** + + :: + + $array = array('title' => $match, 'page1' => $match, 'page2' => $match); + $this->db->like($array); + // WHERE title LIKE '%match%' AND page1 LIKE '%match%' AND page2 LIKE '%match%' + + +$this->db->or_like() +==================== + +This function is identical to the one above, except that multiple +instances are joined by OR:: + + $this->db->like('title', 'match'); $this->db->or_like('body', $match); + // WHERE title LIKE '%match%' OR body LIKE '%match%' + +.. note:: or_like() was formerly known as orlike(), which has been removed. + +$this->db->not_like() +===================== + +This function is identical to **like()**, except that it generates NOT +LIKE statements:: + + $this->db->not_like('title', 'match'); // WHERE title NOT LIKE '%match% + +$this->db->or_not_like() +======================== + +This function is identical to **not_like()**, except that multiple +instances are joined by OR:: + + $this->db->like('title', 'match'); + $this->db->or_not_like('body', 'match'); + // WHERE title LIKE '%match% OR body NOT LIKE '%match%' + +$this->db->group_by() +===================== + +Permits you to write the GROUP BY portion of your query:: + + $this->db->group_by("title"); // Produces: GROUP BY title + +You can also pass an array of multiple values as well:: + + $this->db->group_by(array("title", "date")); // Produces: GROUP BY title, date + +.. note:: group_by() was formerly known as groupby(), which has been + removed. + +$this->db->distinct() +===================== + +Adds the "DISTINCT" keyword to a query + +:: + + $this->db->distinct(); + $this->db->get('table'); // Produces: SELECT DISTINCT * FROM table + + +$this->db->having() +=================== + +Permits you to write the HAVING portion of your query. There are 2 +possible syntaxes, 1 argument or 2:: + + $this->db->having('user_id = 45'); // Produces: HAVING user_id = 45 + $this->db->having('user_id', 45); // Produces: HAVING user_id = 45 + +You can also pass an array of multiple values as well:: + + $this->db->having(array('title =' => 'My Title', 'id <' => $id)); + // Produces: HAVING title = 'My Title', id < 45 + + +If you are using a database that CodeIgniter escapes queries for, you +can prevent escaping content by passing an optional third argument, and +setting it to FALSE. + +:: + + $this->db->having('user_id', 45); // Produces: HAVING `user_id` = 45 in some databases such as MySQL + $this->db->having('user_id', 45, FALSE); // Produces: HAVING user_id = 45 + + +$this->db->or_having() +====================== + +Identical to having(), only separates multiple clauses with "OR". + +$this->db->order_by() +===================== + +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 or desc, or random. + +:: + + $this->db->order_by("title", "desc"); // Produces: ORDER BY title DESC + +You can also pass your own string in the first parameter:: + + $this->db->order_by('title desc, name asc'); // Produces: ORDER BY title DESC, name ASC + +Or multiple function calls can be made if you need multiple fields. + +:: + + $this->db->order_by("title", "desc"); + $this->db->order_by("name", "asc"); // Produces: ORDER BY title DESC, name ASC + + +.. note:: order_by() was formerly known as orderby(), which has been + removed. + +.. note:: random ordering is not currently supported in Oracle or MSSQL + drivers. These will default to 'ASC'. + +$this->db->limit() +================== + +Lets you limit the number of rows you would like returned by the query:: + + $this->db->limit(10); // Produces: LIMIT 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) + +$this->db->count_all_results() +============================== + +Permits you to determine the number of rows in a particular Active +Record query. Queries will accept Active Record 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 + +$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 + +************** +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 +function. Here is an example using an array:: + + $data = array( + 'title' => 'My title', + 'name' => 'My Name', + 'date' => 'My date' + ); + + $this->db->insert('mytable', $data); + // Produces: INSERT INTO mytable (title, name, date) VALUES ('My title', 'My name', 'My date') + +The first parameter will contain the table name, the second is an +associative array of values. + +Here is an example using an object:: + + /* + class Myclass { + var $title = 'My Title'; + var $content = 'My Content'; + var $date = 'My Date'; + } + */ + + $object = new Myclass; + $this->db->insert('mytable', $object); + // Produces: INSERT INTO mytable (title, content, date) VALUES ('My Title', 'My Content', 'My Date') + +The first parameter will contain the table name, the second is an +object. + +.. note:: All values are escaped automatically producing safer queries. + +$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:: + + $data = array( + array( + 'title' => 'My title', + 'name' => 'My Name', + 'date' => 'My date' + ), + array( + 'title' => 'Another title', + 'name' => 'Another Name', + 'date' => 'Another date' + ) + ); + + $this->db->insert_batch('mytable', $data); + // Produces: INSERT INTO mytable (title, name, date) VALUES ('My title', 'My name', 'My date'), ('Another title', 'Another name', 'Another date') + +The first parameter will contain the table name, the second is an +associative array of values. + +.. note:: All values are escaped automatically producing safer queries. + +$this->db->set() +================ + +This function 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 functions:** + +:: + + $this->db->set('name', $name); + $this->db->insert('mytable'); // Produces: INSERT INTO mytable (name) VALUES ('{$name}') + +If you use multiple function called they will be assembled properly +based on whether you are doing an insert or an update:: + + $this->db->set('name', $name); + $this->db->set('title', $title); + $this->db->set('status', $status); + $this->db->insert('mytable'); + +**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. + +:: + + $this->db->set('field', 'field+1', FALSE); + $this->db->insert('mytable'); // gives INSERT INTO mytable (field) VALUES (field+1) + $this->db->set('field', 'field+1'); + $this->db->insert('mytable'); // gives INSERT INTO mytable (field) VALUES ('field+1') + + +You can also pass an associative array to this function:: + + $array = array( + 'name' => $name, + 'title' => $title, + 'status' => $status + ); + + $this->db->set($array); + $this->db->insert('mytable'); + +Or an object:: + + /* + class Myclass { + var $title = 'My Title'; + var $content = 'My Content'; + var $date = 'My Date'; + } + */ + + $object = new Myclass; + $this->db->set($object); + $this->db->insert('mytable'); + + +************* +Updating Data +************* + +$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 +is an example using an array:: + + $data = array( + 'title' => $title, + 'name' => $name, + 'date' => $date + ); + + $this->db->where('id', $id); + $this->db->update('mytable', $data); + // Produces: // UPDATE mytable // SET title = '{$title}', name = '{$name}', date = '{$date}' // WHERE id = $id + +Or you can supply an object:: + + /* + class Myclass { + var $title = 'My Title'; + var $content = 'My Content'; + var $date = 'My Date'; + } + */ + + $object = new Myclass; + $this->db->where('id', $id); + $this->db->update('mytable', $object); + // Produces: // UPDATE mytable // SET title = '{$title}', name = '{$name}', date = '{$date}' // WHERE id = $id + +.. note:: All values are escaped automatically producing safer queries. + +You'll notice the use of the $this->db->where() function, enabling you +to set the WHERE clause. You can optionally pass this information +directly into the update function as a string:: + + $this->db->update('mytable', $data, "id = 4"); + +Or as an array:: + + $this->db->update('mytable', $data, array('id' => $id)); + +You may also use the $this->db->set() function described above when +performing updates. + +$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. +Here is an example using an array:: + + $data = array( + array( + 'title' => 'My title' , + 'name' => 'My Name 2' , + 'date' => 'My date 2' + ), + array( + 'title' => 'Another title' , + 'name' => 'Another Name 2' , + 'date' => 'Another date 2' + ) + ); + + $this->db->update_batch('mytable', $data, 'title'); + + // Produces: + // UPDATE `mytable` SET `name` = CASE + // WHEN `title` = 'My title' THEN 'My Name 2' + // WHEN `title` = 'Another title' THEN 'Another Name 2' + // ELSE `name` END, + // `date` = CASE + // WHEN `title` = 'My title' THEN 'My date 2' + // WHEN `title` = 'Another title' THEN 'Another date 2' + // ELSE `date` END + // WHERE `title` IN ('My title','Another title') + +The first parameter will contain the table name, the second is an associative +array of values, the third parameter is the where key. + +.. note:: All values are escaped automatically producing safer queries. + + +************* +Deleting Data +************* + +$this->db->delete() +=================== + +Generates a delete SQL string and runs the query. + +:: + + $this->db->delete('mytable', array('id' => $id)); // Produces: // DELETE FROM mytable // WHERE id = $id + +The first parameter is the table name, the second is the where clause. +You can also use the where() or or_where() functions instead of passing +the data to the second parameter of the function:: + + $this->db->where('id', $id); + $this->db->delete('mytable'); + + // Produces: + // DELETE FROM mytable + // WHERE id = $id + + +An array of table names can be passed into delete() if you would like to +delete data from more than 1 table. + +:: + + $tables = array('table1', 'table2', 'table3'); + $this->db->where('id', '5'); + $this->db->delete($tables); + + +If you want to delete all data from a table, you can use the truncate() +function, or 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() +===================== + +Generates a truncate SQL string and runs the query. + +:: + + $this->db->from('mytable'); + $this->db->truncate(); + + // or + + $this->db->truncate('mytable'); + + // Produce: + // TRUNCATE mytable + +.. note:: If the TRUNCATE command isn't available, truncate() will + execute as "DELETE FROM table". + +*************** +Method Chaining +*************** + +Method chaining allows you to simplify your syntax by connecting +multiple functions. Consider this example:: + + $query = $this->db->select('title') + ->where('id', $id) + ->limit(10, 20) + ->get('mytable'); + +.. note:: Method chaining only works with PHP 5. + +.. _ar-caching: + +********************* +Active Record Caching +********************* + +While not "true" caching, Active Record 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 Active Record 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 Active Record queries +of the correct type (see below for supported queries) are stored for +later use. + +$this->db->stop_cache() +======================= + +This function can be called to stop caching. + +$this->db->flush_cache() +======================== + +This function deletes all items from the Active Record cache. + +Here's a usage example:: + + $this->db->start_cache(); + $this->db->select('field1'); + $this->db->stop_cache(); + $this->db->get('tablename'); + //Generates: SELECT `field1` FROM (`tablename`) + + $this->db->select('field2'); + $this->db->get('tablename'); + //Generates: SELECT `field1`, `field2` FROM (`tablename`) + + $this->db->flush_cache(); + $this->db->select('field2'); + $this->db->get('tablename'); + //Generates: SELECT `field2` FROM (`tablename`) + + +.. note:: The following statements can be cached: select, from, join, + where, like, group_by, having, order_by, set + + diff --git a/user_guide_src/source/database/caching.rst b/user_guide_src/source/database/caching.rst new file mode 100644 index 000000000..d73120a93 --- /dev/null +++ b/user_guide_src/source/database/caching.rst @@ -0,0 +1,162 @@ +###################### +Database Caching Class +###################### + +The Database Caching Class permits you to cache your queries as text +files for reduced database load. + +.. important:: This class is initialized automatically by the database + driver when caching is enabled. Do NOT load this class manually. + +.. important:: Not all query result functions are available when you + use caching. Please read this page carefully. + +Enabling Caching +================ + +Caching is enabled in three steps: + +- Create a writable directory on your server where the cache files can + be stored. +- Set the path to your cache folder in your + application/config/database.php file. +- Enable the caching feature, either globally by setting the preference + in your application/config/database.php file, or manually as + described below. + +Once enabled, caching will happen automatically whenever a page is +loaded that contains database queries. + +How Does Caching Work? +====================== + +CodeIgniter's query caching system happens dynamically when your pages +are viewed. When caching is enabled, the first time a web page is +loaded, the query result object will be serialized and stored in a text +file on your server. The next time the page is loaded the cache file +will be used instead of accessing your database. Your database usage can +effectively be reduced to zero for any pages that have been cached. + +Only read-type (SELECT) queries can be cached, since these are the only +type of queries that produce a result. Write-type (INSERT, UPDATE, etc.) +queries, since they don't generate a result, will not be cached by the +system. + +Cache files DO NOT expire. Any queries that have been cached will remain +cached until you delete them. The caching system permits you clear +caches associated with individual pages, or you can delete the entire +collection of cache files. Typically you'll want to use the housekeeping +functions described below to delete cache files after certain events +take place, like when you've added new information to your database. + +Will Caching Improve Your Site's Performance? +============================================= + +Getting a performance gain as a result of caching depends on many +factors. If you have a highly optimized database under very little load, +you probably won't see a performance boost. If your database is under +heavy use you probably will see an improved response, assuming your +file-system is not overly taxed. Remember that caching simply changes +how your information is retrieved, shifting it from being a database +operation to a file-system one. + +In some clustered server environments, for example, caching may be +detrimental since file-system operations are so intense. On single +servers in shared environments, caching will probably be beneficial. +Unfortunately there is no single answer to the question of whether you +should cache your database. It really depends on your situation. + +How are Cache Files Stored? +=========================== + +CodeIgniter places the result of EACH query into its own cache file. +Sets of cache files are further organized into sub-folders corresponding +to your controller functions. To be precise, the sub-folders are named +identically to the first two segments of your URI (the controller class +name and function name). + +For example, let's say you have a controller called blog with a function +called comments that contains three queries. The caching system will +create a cache folder called blog+comments, into which it will write +three cache files. + +If you use dynamic queries that change based on information in your URI +(when using pagination, for example), each instance of the query will +produce its own cache file. It's possible, therefore, to end up with +many times more cache files than you have queries. + +Managing your Cache Files +========================= + +Since cache files do not expire, you'll need to build deletion routines +into your application. For example, let's say you have a blog that +allows user commenting. Whenever a new comment is submitted you'll want +to delete the cache files associated with the controller function that +serves up your comments. You'll find two delete functions described +below that help you clear data. + +Not All Database Functions Work with Caching +============================================ + +Lastly, we need to point out that the result object that is cached is a +simplified version of the full result object. For that reason, some of +the query result functions are not available for use. + +The following functions ARE NOT available when using a cached result +object: + +- num_fields() +- field_names() +- field_data() +- free_result() + +Also, the two database resources (result_id and conn_id) are not +available when caching, since result resources only pertain to run-time +operations. + +****************** +Function Reference +****************** + +$this->db->cache_on() / $this->db->cache_off() +================================================ + +Manually enables/disables caching. This can be useful if you want to +keep certain queries from being cached. Example:: + + // Turn caching on + $this->db->cache_on(); + $query = $this->db->query("SELECT * FROM mytable"); + + // Turn caching off for this one query + $this->db->cache_off(); + $query = $this->db->query("SELECT * FROM members WHERE member_id = '$current_user'"); + + // Turn caching back on + $this->db->cache_on(); + $query = $this->db->query("SELECT * FROM another_table"); + +$this->db->cache_delete() +========================== + +Deletes the cache files associated with a particular page. This is +useful if you need to clear caching after you update your database. + +The caching system saves your cache files to folders that correspond to +the URI of the page you are viewing. For example, if you are viewing a +page at example.com/index.php/blog/comments, the caching system will put +all cache files associated with it in a folder called blog+comments. To +delete those particular cache files you will use:: + + $this->db->cache_delete('blog', 'comments'); + +If you do not use any parameters the current URI will be used when +determining what should be cleared. + +$this->db->cache_delete_all() +=============================== + +Clears all existing cache files. Example:: + + $this->db->cache_delete_all(); + diff --git a/user_guide_src/source/database/call_function.rst b/user_guide_src/source/database/call_function.rst new file mode 100644 index 000000000..9890fc453 --- /dev/null +++ b/user_guide_src/source/database/call_function.rst @@ -0,0 +1,39 @@ +##################### +Custom Function Calls +##################### + +$this->db->call_function(); +============================ + +This function enables you to call PHP database functions that are not +natively included in CodeIgniter, in a platform independent manner. For +example, lets say you want to call the mysql_get_client_info() +function, which is **not** natively supported by CodeIgniter. You could +do so like this:: + + $this->db->call_function('get_client_info'); + +You must supply the name of the function, **without** the mysql\_ +prefix, in the first parameter. The prefix is added automatically based +on which database driver is currently being used. This permits you to +run the same function on different database platforms. Obviously not all +function calls are identical between platforms, so there are limits to +how useful this function can be in terms of portability. + +Any parameters needed by the function you are calling will be added to +the second parameter. + +:: + + $this->db->call_function('some_function', $param1, $param2, etc..); + +Often, you will either need to supply a database connection ID or a +database result ID. The connection ID can be accessed using:: + + $this->db->conn_id; + +The result ID can be accessed from within your result object, like this:: + + $query = $this->db->query("SOME QUERY"); + + $query->result_id;
\ No newline at end of file diff --git a/user_guide_src/source/database/configuration.rst b/user_guide_src/source/database/configuration.rst new file mode 100644 index 000000000..687f0d920 --- /dev/null +++ b/user_guide_src/source/database/configuration.rst @@ -0,0 +1,126 @@ +###################### +Database Configuration +###################### + +CodeIgniter has a config file that lets you store your database +connection values (username, password, database name, etc.). The config +file is located at application/config/database.php. You can also set +database connection values for specific +:doc:`environments <../libraries/config>` by placing **database.php** +it the respective environment config folder. + +The config settings are stored in a multi-dimensional array with this +prototype:: + + $db['default']['hostname'] = "localhost"; + $db['default']['username'] = "root"; + $db['default']['password'] = ""; + $db['default']['database'] = "database_name"; + $db['default']['dbdriver'] = "mysql"; + $db['default']['dbprefix'] = ""; + $db['default']['pconnect'] = TRUE; + $db['default']['db_debug'] = FALSE; + $db['default']['cache_on'] = FALSE; + $db['default']['cachedir'] = ""; + $db['default']['char_set'] = "utf8"; + $db['default']['dbcollat'] = "utf8_general_ci"; + $db['default']['swap_pre'] = ""; + $db['default']['autoinit'] = TRUE; + $db['default']['stricton'] = FALSE; + +The reason we use a multi-dimensional array rather than a more simple +one is to permit you to optionally store multiple sets of connection +values. If, for example, you run multiple environments (development, +production, test, etc.) under a single installation, you can set up a +connection group for each, then switch between groups as needed. For +example, to set up a "test" environment you would do this:: + + $db['test']['hostname'] = "localhost"; + $db['test']['username'] = "root"; + $db['test']['password'] = ""; + $db['test']['database'] = "database_name"; + $db['test']['dbdriver'] = "mysql"; + $db['test']['dbprefix'] = ""; + $db['test']['pconnect'] = TRUE; + $db['test']['db_debug'] = FALSE; + $db['test']['cache_on'] = FALSE; + $db['test']['cachedir'] = ""; + $db['test']['char_set'] = "utf8"; + $db['test']['dbcollat'] = "utf8_general_ci"; + $db['test']['swap_pre'] = ""; + $db['test']['autoinit'] = TRUE; + $db['test']['stricton'] = FALSE; + +Then, to globally tell the system to use that group you would set this +variable located in the config file:: + + $active_group = "test"; + +Note: The name "test" is arbitrary. It can be anything you want. By +default we've used the word "default" for the primary connection, but it +too can be renamed to something more relevant to your project. + +Active Record +------------- + +The :doc:`Active Record Class <active_record>` is globally enabled or +disabled by setting the $active_record variable in the database +configuration file to TRUE/FALSE (boolean). If you are not using the +active record class, setting it to FALSE will utilize fewer resources +when the database classes are initialized. + +:: + + $active_record = TRUE; + +.. note:: that some CodeIgniter classes such as Sessions require Active + Records be enabled to access certain functionality. + +Explanation of Values: +---------------------- + +====================== ================================================================================================== + Name Config Description +====================== ================================================================================================== +**hostname** The hostname of your database server. Often this is "localhost". +**username** The username used to connect to the database. +**password** The password used to connect to the database. +**database** The name of the database you want to connect to. +**dbdriver** The database type. ie: mysql, postgres, odbc, etc. Must be specified in lower case. +**dbprefix** An optional table prefix which will added to the table name when running :doc: + `Active Record <active_record>` queries. This permits multiple CodeIgniter installations + to share one database. +**pconnect** TRUE/FALSE (boolean) - Whether to use a persistent connection. +**db_debug** TRUE/FALSE (boolean) - Whether database errors should be displayed. +**cache_on** TRUE/FALSE (boolean) - Whether database query caching is enabled, + see also :doc:`Database Caching Class <caching>`. +**cachedir** The absolute server path to your database query cache directory. +**char_set** The character set used in communicating with the database. +**dbcollat** The character collation used in communicating with the database + + .. note:: For MySQL and MySQLi databases, this setting is only used + as a backup if your server is running PHP < 5.2.3 or MySQL < 5.0.7 + (and in table creation queries made with DB Forge). There is an + incompatibility in PHP with mysql_real_escape_string() which can + make your site vulnerable to SQL injection if you are using a + multi-byte character set and are running versions lower than these. + Sites using Latin-1 or UTF-8 database character set and collation are + unaffected. + +**swap_pre** A default table prefix that should be swapped with dbprefix. This is useful for distributed + applications where you might run manually written queries, and need the prefix to still be + customizable by the end user. +**autoinit** Whether or not to automatically connect to the database when the library loads. If set to false, + the connection will take place prior to executing the first query. +**stricton** TRUE/FALSE (boolean) - Whether to force "Strict Mode" connections, good for ensuring strict SQL + while developing an application. +**port** The database port number. To use this value you have to add a line to the database config array. + :: + $db['default']['port'] = 5432; +====================== ================================================================================================== + +.. note:: Depending on what database platform you are using (MySQL, + Postgres, etc.) not all values will be needed. For example, when using + SQLite you will not need to supply a username or password, and the + database name will be the path to your database file. The information + above assumes you are using MySQL. diff --git a/user_guide_src/source/database/connecting.rst b/user_guide_src/source/database/connecting.rst new file mode 100644 index 000000000..64adc3047 --- /dev/null +++ b/user_guide_src/source/database/connecting.rst @@ -0,0 +1,132 @@ +########################### +Connecting to your Database +########################### + +There are two ways to connect to a database: + +Automatically Connecting +======================== + +The "auto connect" feature will load and instantiate the database class +with every page load. To enable "auto connecting", add the word database +to the library array, as indicated in the following file: + +application/config/autoload.php + +Manually Connecting +=================== + +If only some of your pages require database connectivity you can +manually connect to your database by adding this line of code in any +function where it is needed, or in your class constructor to make the +database available globally in that class. + +:: + + $this->load->database(); + +If the above function does **not** contain any information in the first +parameter it will connect to the group specified in your database config +file. For most people, this is the preferred method of use. + +Available Parameters +-------------------- + +#. The database connection values, passed either as an array or a DSN + string. +#. TRUE/FALSE (boolean). Whether to return the connection ID (see + Connecting to Multiple Databases below). +#. TRUE/FALSE (boolean). Whether to enable the Active Record class. Set + to TRUE by default. + +Manually Connecting to a Database +--------------------------------- + +The first parameter of this function can **optionally** be used to +specify a particular database group from your config file, or you can +even submit connection values for a database that is not specified in +your config file. Examples: + +To choose a specific group from your config file you can do this:: + + $this->load->database('group_name'); + +Where group_name is the name of the connection group from your config +file. + +To connect manually to a desired database you can pass an array of +values:: + + $config['hostname'] = "localhost"; $config['username'] = "myusername"; $config['password'] = "mypassword"; $config['database'] = "mydatabase"; $config['dbdriver'] = "mysql"; $config['dbprefix'] = ""; $config['pconnect'] = FALSE; $config['db_debug'] = TRUE; $config['cache_on'] = FALSE; $config['cachedir'] = ""; $config['char_set'] = "utf8"; $config['dbcollat'] = "utf8_general_ci"; $this->load->database($config); + +For information on each of these values please see the :doc:`configuration +page <configuration>`. + +.. note:: For the PDO driver, $config['hostname'] should look like + this: 'mysql:host=localhost' + +Or you can submit your database values as a Data Source Name. DSNs must +have this prototype:: + + $dsn = 'dbdriver://username:password@hostname/database'; $this->load->database($dsn); + +To override default config values when connecting with a DSN string, add +the config variables as a query string. + +:: + + $dsn = 'dbdriver://username:password@hostname/database?char_set=utf8&dbcollat=utf8_general_ci&cache_on=true&cachedir=/path/to/cache'; $this->load->database($dsn); + +Connecting to Multiple Databases +================================ + +If you need to connect to more than one database simultaneously you can +do so as follows:: + + $DB1 = $this->load->database('group_one', TRUE); $DB2 = $this->load->database('group_two', TRUE); + +Note: Change the words "group_one" and "group_two" to the specific +group names you are connecting to (or you can pass the connection values +as indicated above). + +By setting the second parameter to TRUE (boolean) the function will +return the database object. + +.. note:: When you connect this way, you will use your object name to issue + commands rather than the syntax used throughout this guide. In other + words, rather than issuing commands with: + + | + | $this->db->query(); + | $this->db->result(); + | etc... + | + | You will instead use: + | + | $DB1->query(); + | $DB1->result(); + | etc... + +Reconnecting / Keeping the Connection Alive +=========================================== + +If the database server's idle timeout is exceeded while you're doing +some heavy PHP lifting (processing an image, for instance), you should +consider pinging the server by using the reconnect() method before +sending further queries, which can gracefully keep the connection alive +or re-establish it. + +:: + + $this->db->reconnect(); + +Manually closing the Connection +=============================== + +While CodeIgniter intelligently takes care of closing your database +connections, you can explicitly close the connection. + +:: + + $this->db->close(); + diff --git a/user_guide_src/source/database/examples.rst b/user_guide_src/source/database/examples.rst new file mode 100644 index 000000000..d1cd48837 --- /dev/null +++ b/user_guide_src/source/database/examples.rst @@ -0,0 +1,136 @@ +################################## +Database Quick Start: Example Code +################################## + +The following page contains example code showing how the database class +is used. For complete details please read the individual pages +describing each function. + +Initializing the Database Class +=============================== + +The following code loads and initializes the database class based on +your :doc:`configuration <configuration>` settings:: + + $this->load->database(); + +Once loaded the class is ready to be used as described below. + +Note: If all your pages require database access you can connect +automatically. See the :doc:`connecting <connecting>` page for details. + +Standard Query With Multiple Results (Object Version) +===================================================== + +:: + + $query = $this->db->query('SELECT name, title, email FROM my_table'); + + foreach ($query->result() as $row) + { + echo $row->title; + echo $row->name; + echo $row->email; + } + + echo 'Total Results: ' . $query->num_rows(); + +The above result() function returns an array of **objects**. Example: +$row->title + +Standard Query With Multiple Results (Array Version) +==================================================== + +:: + + $query = $this->db->query('SELECT name, title, email FROM my_table'); + + foreach ($query->result_array() as $row) + { + echo $row['title']; + echo $row['name']; + echo $row['email']; + } + +The above result_array() function returns an array of standard array +indexes. Example: $row['title'] + +Testing for Results +=================== + +If you run queries that might **not** produce a result, you are +encouraged to test for a result first using the num_rows() function:: + + $query = $this->db->query("YOUR QUERY"); + if ($query->num_rows() > 0) + { + foreach ($query->result() as $row) + { + echo $row->title; + echo $row->name; + echo $row->body; + } + } + +Standard Query With Single Result +================================= + +:: + + $query = $this->db->query('SELECT name FROM my_table LIMIT 1'); + $row = $query->row(); + echo $row->name; + +The above row() function returns an **object**. Example: $row->name + +Standard Query With Single Result (Array version) +================================================= + +:: + + $query = $this->db->query('SELECT name FROM my_table LIMIT 1'); + $row = $query->row_array(); + echo $row['name']; + +The above row_array() function returns an **array**. Example: +$row['name'] + +Standard Insert +=============== + +:: + + $sql = "INSERT INTO mytable (title, name) VALUES (".$this->db->escape($title).", ".$this->db->escape($name).")"; + $this->db->query($sql); + echo $this->db->affected_rows(); + +Active Record Query +=================== + +The :doc:`Active Record Pattern <active_record>` gives you a simplified +means of retrieving data:: + + $query = $this->db->get('table_name'); + + foreach ($query->result() as $row) + { + echo $row->title; + } + +The above get() function retrieves all the results from the supplied +table. The :doc:`Active Record <active_record>` class contains a full +compliment of functions for working with data. + +Active Record Insert +==================== + +:: + + $data = array( + 'title' => $title, + 'name' => $name, + 'date' => $date + ); + + $this->db->insert('mytable', $data); // Produces: INSERT INTO mytable (title, name, date) VALUES ('{$title}', '{$name}', '{$date}') + diff --git a/user_guide_src/source/database/fields.rst b/user_guide_src/source/database/fields.rst new file mode 100644 index 000000000..b706ace7d --- /dev/null +++ b/user_guide_src/source/database/fields.rst @@ -0,0 +1,80 @@ +########## +Field Data +########## + +$this->db->list_fields() +========================= + +Returns an array containing the field names. This query can be called +two ways: + +1. You can supply the table name and call it from the $this->db-> +object:: + + $fields = $this->db->list_fields('table_name'); + + foreach ($fields as $field) + { + echo $field; + } + +2. You can gather the field names associated with any query you run by +calling the function from your query result object:: + + $query = $this->db->query('SELECT * FROM some_table'); + + foreach ($query->list_fields() as $field) + { + echo $field; + } + +$this->db->field_exists() +========================== + +Sometimes it's helpful to know whether a particular field exists before +performing an action. Returns a boolean TRUE/FALSE. Usage example:: + + if ($this->db->field_exists('field_name', 'table_name')) + { + // some code... + } + +.. note:: Replace *field_name* with the name of the column you are looking + for, and replace *table_name* with the name of the table you are + looking for. + +$this->db->field_data() +======================== + +Returns an array of objects containing field information. + +Sometimes it's helpful to gather the field names or other metadata, like +the column type, max length, etc. + +.. note:: Not all databases provide meta-data. + +Usage example:: + + $fields = $this->db->field_data('table_name'); + + foreach ($fields as $field) + { + echo $field->name; + echo $field->type; + echo $field->max_length; + echo $field->primary_key; + } + +If you have run a query already you can use the result object instead of +supplying the table name:: + + $query = $this->db->query("YOUR QUERY"); + $fields = $query->field_data(); + +The following data is available from this function if supported by your +database: + +- name - column name +- max_length - maximum length of the column +- primary_key - 1 if the column is a primary key +- type - the type of the column
\ No newline at end of file diff --git a/user_guide_src/source/database/forge.rst b/user_guide_src/source/database/forge.rst new file mode 100644 index 000000000..bf17e2918 --- /dev/null +++ b/user_guide_src/source/database/forge.rst @@ -0,0 +1,268 @@ +#################### +Database Forge Class +#################### + +The Database Forge Class contains functions that help you manage your +database. + +.. contents:: Table of Contents + +**************************** +Initializing the Forge Class +**************************** + +.. important:: In order to initialize the Forge class, your database + driver must already be running, since the forge class relies on it. + +Load the Forge Class as follows:: + + $this->load->dbforge() + +Once initialized you will access the functions using the $this->dbforge +object:: + + $this->dbforge->some_function() + +$this->dbforge->create_database('db_name') +============================================ + +Permits you to create the database specified in the first parameter. +Returns TRUE/FALSE based on success or failure:: + + if ($this->dbforge->create_database('my_db')) + { + echo 'Database created!'; + } + +$this->dbforge->drop_database('db_name') +========================================== + +Permits you to drop the database specified in the first parameter. +Returns TRUE/FALSE based on success or failure:: + + if ($this->dbforge->drop_database('my_db')) + { + echo 'Database deleted!'; + } + +**************************** +Creating and Dropping Tables +**************************** + +There are several things you may wish to do when creating tables. Add +fields, add keys to the table, alter columns. CodeIgniter provides a +mechanism for this. + +Adding fields +============= + +Fields are created via an associative array. Within the array you must +include a 'type' key that relates to the datatype of the field. For +example, INT, VARCHAR, TEXT, etc. Many datatypes (for example VARCHAR) +also require a 'constraint' key. + +:: + + $fields = array( + 'users' => array( + 'type' => 'VARCHAR', + 'constraint' => '100', + ), + ); + // will translate to "users VARCHAR(100)" when the field is added. + + +Additionally, the following key/values can be used: + +- unsigned/true : to generate "UNSIGNED" in the field definition. +- default/value : to generate a default value in the field definition. +- null/true : to generate "NULL" in the field definition. Without this, + the field will default to "NOT NULL". +- auto_increment/true : generates an auto_increment flag on the + field. Note that the field type must be a type that supports this, + such as integer. + +:: + + $fields = array( + 'blog_id' => array( + 'type' => 'INT', + 'constraint' => 5, + 'unsigned' => TRUE, + 'auto_increment' => TRUE + ), + 'blog_title' => array( + 'type' => 'VARCHAR', + 'constraint' => '100', + ), + 'blog_author' => array( + 'type' =>'VARCHAR', + 'constraint' => '100', + 'default' => 'King of Town', + ), + 'blog_description' => array( + 'type' => 'TEXT', + 'null' => TRUE, + ), + ); + + +After the fields have been defined, they can be added using +$this->dbforge->add_field($fields); followed by a call to the +create_table() function. + +$this->dbforge->add_field() +---------------------------- + +The add fields function will accept the above array. + +Passing strings as fields +------------------------- + +If you know exactly how you want a field to be created, you can pass the +string into the field definitions with add_field() + +:: + + $this->dbforge->add_field("label varchar(100) NOT NULL DEFAULT 'default label'"); + + +.. note:: Multiple calls to add_field() are cumulative. + +Creating an id field +-------------------- + +There is a special exception for creating id fields. A field with type +id will automatically be assinged as an INT(9) auto_incrementing +Primary Key. + +:: + + $this->dbforge->add_field('id'); + // gives id INT(9) NOT NULL AUTO_INCREMENT + + +Adding Keys +=========== + +Generally speaking, you'll want your table to have Keys. This is +accomplished with $this->dbforge->add_key('field'). An optional second +parameter set to TRUE will make it a primary key. Note that add_key() +must be followed by a call to create_table(). + +Multiple column non-primary keys must be sent as an array. Sample output +below is for MySQL. + +:: + + $this->dbforge->add_key('blog_id', TRUE); + // gives PRIMARY KEY `blog_id` (`blog_id`) + + $this->dbforge->add_key('blog_id', TRUE); + $this->dbforge->add_key('site_id', TRUE); + // gives PRIMARY KEY `blog_id_site_id` (`blog_id`, `site_id`) + + $this->dbforge->add_key('blog_name'); + // gives KEY `blog_name` (`blog_name`) + + $this->dbforge->add_key(array('blog_name', 'blog_label')); + // gives KEY `blog_name_blog_label` (`blog_name`, `blog_label`) + + +Creating a table +================ + +After fields and keys have been declared, you can create a new table +with + +:: + + $this->dbforge->create_table('table_name'); + // gives CREATE TABLE table_name + + +An optional second parameter set to TRUE adds an "IF NOT EXISTS" clause +into the definition + +:: + + $this->dbforge->create_table('table_name', TRUE); + // gives CREATE TABLE IF NOT EXISTS table_name + + +Dropping a table +================ + +Executes a DROP TABLE sql + +:: + + $this->dbforge->drop_table('table_name'); + // gives DROP TABLE IF EXISTS table_name + + +Renaming a table +================ + +Executes a TABLE rename + +:: + + $this->dbforge->rename_table('old_table_name', 'new_table_name'); + // gives ALTER TABLE old_table_name RENAME TO new_table_name + + +**************** +Modifying Tables +**************** + +$this->dbforge->add_column() +============================= + +The add_column() function is used to modify an existing table. It +accepts the same field array as above, and can be used for an unlimited +number of additional fields. + +:: + + $fields = array( + 'preferences' => array('type' => 'TEXT') + ); + $this->dbforge->add_column('table_name', $fields); + // gives ALTER TABLE table_name ADD preferences TEXT + +An optional third parameter can be used to specify which existing column +to add the new column after. + +:: + + $this->dbforge->add_column('table_name', $fields, 'after_field'); + + +$this->dbforge->drop_column() +============================== + +Used to remove a column from a table. + +:: + + $this->dbforge->drop_column('table_name', 'column_to_drop'); + + +$this->dbforge->modify_column() +================================ + +The usage of this function is identical to add_column(), except it +alters an existing column rather than adding a new one. In order to +change the name you can add a "name" key into the field defining array. + +:: + + $fields = array( + 'old_name' => array( + 'name' => 'new_name', + 'type' => 'TEXT', + ), + ); + $this->dbforge->modify_column('table_name', $fields); + // gives ALTER TABLE table_name CHANGE old_name new_name TEXT
\ No newline at end of file diff --git a/user_guide_src/source/database/helpers.rst b/user_guide_src/source/database/helpers.rst new file mode 100644 index 000000000..7ea19e9f6 --- /dev/null +++ b/user_guide_src/source/database/helpers.rst @@ -0,0 +1,98 @@ +###################### +Query Helper Functions +###################### + +$this->db->insert_id() +======================= + +The insert ID number when performing database inserts. + +.. note:: If using the PDO driver with PostgreSQL, this function requires + a $name parameter, which specifies the appropriate sequence to check + for the insert id. + +$this->db->affected_rows() +=========================== + +Displays the number of affected rows, when doing "write" type queries +(insert, update, etc.). + +.. note:: In MySQL "DELETE FROM TABLE" returns 0 affected rows. The database + class has a small hack that allows it to return the correct number of + affected rows. By default this hack is enabled but it can be turned off + in the database driver file. + +$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 + +$this->db->platform() +===================== + +Outputs the database platform you are running (MySQL, MS SQL, Postgres, +etc...):: + + echo $this->db->platform(); + +$this->db->version() +==================== + +Outputs the database version you are running:: + + echo $this->db->version(); + +$this->db->last_query(); +========================= + +Returns the last query that was run (the query string, not the result). +Example:: + + $str = $this->db->last_query(); + + // Produces: SELECT * FROM sometable.... + +The following two functions help simplify the process of writing +database INSERTs and UPDATEs. + +$this->db->insert_string(); +============================ + +This function simplifies the process of writing database inserts. It +returns a correctly formatted SQL insert string. Example:: + + $data = array('name' => $name, 'email' => $email, 'url' => $url); + + $str = $this->db->insert_string('table_name', $data); + +The first parameter is the table name, the second is an associative +array with the data to be inserted. The above example produces:: + + INSERT INTO table_name (name, email, url) VALUES ('Rick', 'rick@example.com', 'example.com') + +.. note:: Values are automatically escaped, producing safer queries. + +$this->db->update_string(); +============================ + +This function simplifies the process of writing database updates. It +returns a correctly formatted SQL update string. Example:: + + $data = array('name' => $name, 'email' => $email, 'url' => $url); + + $where = "author_id = 1 AND status = 'active'"; + + $str = $this->db->update_string('table_name', $data, $where); + +The first parameter is the table name, the second is an associative +array with the data to be updated, and the third parameter is the +"where" clause. The above example produces:: + + UPDATE table_name SET name = 'Rick', email = 'rick@example.com', url = 'example.com' WHERE author_id = 1 AND status = 'active' + +.. note:: Values are automatically escaped, producing safer queries. diff --git a/user_guide_src/source/database/index.rst b/user_guide_src/source/database/index.rst new file mode 100644 index 000000000..3b59986be --- /dev/null +++ b/user_guide_src/source/database/index.rst @@ -0,0 +1,29 @@ +################## +The Database Class +################## + +CodeIgniter comes with a full-featured and very fast abstracted database +class that supports both traditional structures and Active Record +patterns. The database functions offer clear, simple syntax. + +- :doc:`Quick Start: Usage Examples <examples>` +- :doc:`Database Configuration <configuration>` +- :doc:`Connecting to a Database <connecting>` +- :doc:`Running Queries <queries>` +- :doc:`Generating Query Results <results>` +- :doc:`Query Helper Functions <helpers>` +- :doc:`Active Record Class <active_record>` +- :doc:`Transactions <transactions>` +- :doc:`Table MetaData <table_data>` +- :doc:`Field MetaData <fields>` +- :doc:`Custom Function Calls <call_function>` +- :doc:`Query Caching <caching>` +- :doc:`Database manipulation with Database Forge <forge>` +- :doc:`Database Utilities Class <utilities>` + +.. toctree:: + :glob: + :titlesonly: + :hidden: + + *
\ No newline at end of file diff --git a/user_guide_src/source/database/queries.rst b/user_guide_src/source/database/queries.rst new file mode 100644 index 000000000..971d5d61d --- /dev/null +++ b/user_guide_src/source/database/queries.rst @@ -0,0 +1,114 @@ +####### +Queries +####### + +$this->db->query(); +=================== + +To submit a query, use the following function:: + + $this->db->query('YOUR QUERY HERE'); + +The query() function returns a database result **object** when "read" +type queries are run, which you can use to :doc:`show your +results <results>`. When "write" type queries are run it simply +returns TRUE or FALSE depending on success or failure. When retrieving +data you will typically assign the query to your own variable, like +this:: + + $query = $this->db->query('YOUR QUERY HERE'); + +$this->db->simple_query(); +=========================== + +This is a simplified version of the $this->db->query() function. It ONLY +returns TRUE/FALSE on success or failure. It DOES NOT return a database +result set, nor does it set the query timer, or compile bind data, or +store your query for debugging. It simply lets you submit a query. Most +users will rarely use this function. + +*************************************** +Working with Database prefixes manually +*************************************** + +If you have configured a database prefix and would like to prepend it to +a table name for use in a native SQL query for example, then you can use +the following:: + + $this->db->dbprefix('tablename'); // outputs prefix_tablename + + +If for any reason you would like to change the prefix programatically +without needing to create a new connection, you can use this method:: + + $this->db->set_dbprefix('newprefix'); + $this->db->dbprefix('tablename'); // outputs newprefix_tablename + + +********************** +Protecting identifiers +********************** + +In many databases it is advisable to protect table and field names - for +example with backticks in MySQL. **Active Record queries are +automatically protected**, however if you need to manually protect an +identifier you can use:: + + $this->db->protect_identifiers('table_name'); + + +This function will also add a table prefix to your table, assuming you +have a prefix specified in your database config file. To enable the +prefixing set TRUE (boolen) via the second parameter:: + + $this->db->protect_identifiers('table_name', TRUE); + + +**************** +Escaping Queries +**************** + +It's a very good security practice to escape your data before submitting +it into your database. CodeIgniter has three methods that help you do +this: + +#. **$this->db->escape()** This function determines the data type so + that it can escape only string data. It also automatically adds + single quotes around the data so you don't have to: + :: + + $sql = "INSERT INTO table (title) VALUES(".$this->db->escape($title).")"; + +#. **$this->db->escape_str()** This function escapes the data passed to + it, regardless of type. Most of the time you'll use the above + function rather than this one. Use the function like this: + :: + + $sql = "INSERT INTO table (title) VALUES('".$this->db->escape_str($title)."')"; + +#. **$this->db->escape_like_str()** This method should be used when + strings are to be used in LIKE conditions so that LIKE wildcards + ('%', '\_') in the string are also properly escaped. + +:: + + $search = '20% raise'; $sql = "SELECT id FROM table WHERE column LIKE '%".$this->db->escape_like_str($search)."%'"; + + +************** +Query Bindings +************** + +Bindings enable you to simplify your query syntax by letting the system +put the queries together for you. Consider the following example:: + + $sql = "SELECT * FROM some_table WHERE id = ? AND status = ? AND author = ?"; + $this->db->query($sql, array(3, 'live', 'Rick')); + +The question marks in the query are automatically replaced with the +values in the array in the second parameter of the query function. + +The secondary benefit of using binds is that the values are +automatically escaped, producing safer queries. You don't have to +remember to manually escape data; the engine does it automatically for +you. diff --git a/user_guide_src/source/database/results.rst b/user_guide_src/source/database/results.rst new file mode 100644 index 000000000..4f93c794d --- /dev/null +++ b/user_guide_src/source/database/results.rst @@ -0,0 +1,186 @@ +######################## +Generating Query Results +######################## + +There are several ways to generate query results: + +result() +======== + +This function returns the query result as an array of **objects**, or +**an empty array** on failure. Typically you'll use this in a foreach +loop, like this:: + + $query = $this->db->query("YOUR QUERY"); + + foreach ($query->result() as $row) + { + echo $row->title; + echo $row->name; + echo $row->body; + } + +The above function is an alias of result_object(). + +If you run queries that might **not** produce a result, you are +encouraged to test the result first:: + + $query = $this->db->query("YOUR QUERY"); + + if ($query->num_rows() > 0) + { + foreach ($query->result() as $row) + { + echo $row->title; + echo $row->name; + echo $row->body; + } + } + +You can also pass a string to result() which represents a class to +instantiate for each result object (note: this class must be loaded) + +:: + + $query = $this->db->query("SELECT * FROM users;"); + + foreach ($query->result('User') as $user) + { + echo $user->name; // call attributes + echo $user->reverse_name(); // or methods defined on the 'User' class + } + +result_array() +=============== + +This function returns the query result as a pure array, or an empty +array when no result is produced. Typically you'll use this in a foreach +loop, like this:: + + $query = $this->db->query("YOUR QUERY"); + + foreach ($query->result_array() as $row) + { + echo $row['title']; + echo $row['name']; + echo $row['body']; + } + +row() +===== + +This function returns a single result row. If your query has more than +one row, it returns only the first row. The result is returned as an +**object**. Here's a usage example:: + + $query = $this->db->query("YOUR QUERY"); + + if ($query->num_rows() > 0) + { + $row = $query->row(); + + echo $row->title; + echo $row->name; + echo $row->body; + } + +If you want a specific row returned you can submit the row number as a +digit in the first parameter:: + + $row = $query->row(5); + +You can also add a second String parameter, which is the name of a class +to instantiate the row with:: + + $query = $this->db->query("SELECT * FROM users LIMIT 1;"); + $query->row(0, 'User'); + + echo $row->name; // call attributes + echo $row->reverse_name(); // or methods defined on the 'User' class + +row_array() +============ + +Identical to the above row() function, except it returns an array. +Example:: + + $query = $this->db->query("YOUR QUERY"); + + if ($query->num_rows() > 0) + { + $row = $query->row_array(); + + echo $row['title']; + echo $row['name']; + echo $row['body']; + } + +If you want a specific row returned you can submit the row number as a +digit in the first parameter:: + + $row = $query->row_array(5); + +In addition, you can walk forward/backwards/first/last through your +results using these variations: + + | **$row = $query->first_row()** + | **$row = $query->last_row()** + | **$row = $query->next_row()** + | **$row = $query->previous_row()** + +By default they return an object unless you put the word "array" in the +parameter: + + | **$row = $query->first_row('array')** + | **$row = $query->last_row('array')** + | **$row = $query->next_row('array')** + | **$row = $query->previous_row('array')** + +*********************** +Result Helper Functions +*********************** + +$query->num_rows() +=================== + +The number of rows returned by the query. Note: In this example, $query +is the variable that the query result object is assigned to:: + + $query = $this->db->query('SELECT * FROM my_table'); + + echo $query->num_rows(); + +$query->num_fields() +===================== + +The number of FIELDS (columns) returned by the query. Make sure to call +the function using your query result object:: + + $query = $this->db->query('SELECT * FROM my_table'); + + echo $query->num_fields(); + +$query->free_result() +====================== + +It frees the memory associated with the result and deletes the result +resource ID. Normally PHP frees its memory automatically at the end of +script execution. However, if you are running a lot of queries in a +particular script you might want to free the result after each query +result has been generated in order to cut down on memory consumptions. +Example:: + + $query = $this->db->query('SELECT title FROM my_table'); + + foreach ($query->result() as $row) + { + echo $row->title; + } + $query->free_result(); // The $query result object will no longer be available + + $query2 = $this->db->query('SELECT name FROM some_table'); + + $row = $query2->row(); + echo $row->name; + $query2->free_result();// The $query2 result object will no longer be available + diff --git a/user_guide_src/source/database/table_data.rst b/user_guide_src/source/database/table_data.rst new file mode 100644 index 000000000..744a05154 --- /dev/null +++ b/user_guide_src/source/database/table_data.rst @@ -0,0 +1,31 @@ +########## +Table Data +########## + +These functions let you fetch table information. + +$this->db->list_tables(); +========================== + +Returns an array containing the names of all the tables in the database +you are currently connected to. Example:: + + $tables = $this->db->list_tables(); + + foreach ($tables as $table) + { + echo $table; + } + +$this->db->table_exists(); +=========================== + +Sometimes it's helpful to know whether a particular table exists before +running an operation on it. Returns a boolean TRUE/FALSE. Usage example:: + + if ($this->db->table_exists('table_name')) + { + // some code... + } + +.. note:: Replace *table_name* with the name of the table you are looking for. diff --git a/user_guide_src/source/database/transactions.rst b/user_guide_src/source/database/transactions.rst new file mode 100644 index 000000000..e9190e59a --- /dev/null +++ b/user_guide_src/source/database/transactions.rst @@ -0,0 +1,127 @@ +############ +Transactions +############ + +CodeIgniter's database abstraction allows you to use transactions with +databases that support transaction-safe table types. In MySQL, you'll +need to be running InnoDB or BDB table types rather than the more common +MyISAM. Most other database platforms support transactions natively. + +If you are not familiar with transactions we recommend you find a good +online resource to learn about them for your particular database. The +information below assumes you have a basic understanding of +transactions. + +CodeIgniter's Approach to Transactions +====================================== + +CodeIgniter utilizes an approach to transactions that is very similar to +the process used by the popular database class ADODB. We've chosen that +approach because it greatly simplifies the process of running +transactions. In most cases all that is required are two lines of code. + +Traditionally, transactions have required a fair amount of work to +implement since they demand that you to keep track of your queries and +determine whether to commit or rollback based on the success or failure +of your queries. This is particularly cumbersome with nested queries. In +contrast, we've implemented a smart transaction system that does all +this for you automatically (you can also manage your transactions +manually if you choose to, but there's really no benefit). + +Running Transactions +==================== + +To run your queries using transactions you will use the +$this->db->trans_start() and $this->db->trans_complete() functions as +follows:: + + $this->db->trans_start(); + $this->db->query('AN SQL QUERY...'); + $this->db->query('ANOTHER QUERY...'); + $this->db->query('AND YET ANOTHER QUERY...'); + $this->db->trans_complete(); + +You can run as many queries as you want between the start/complete +functions and they will all be committed or rolled back based on success +or failure of any given query. + +Strict Mode +=========== + +By default CodeIgniter runs all transactions in Strict Mode. When strict +mode is enabled, if you are running multiple groups of transactions, if +one group fails all groups will be rolled back. If strict mode is +disabled, each group is treated independently, meaning a failure of one +group will not affect any others. + +Strict Mode can be disabled as follows:: + + $this->db->trans_strict(FALSE); + +Managing Errors +=============== + +If you have error reporting enabled in your config/database.php file +you'll see a standard error message if the commit was unsuccessful. If +debugging is turned off, you can manage your own errors like this:: + + $this->db->trans_start(); + $this->db->query('AN SQL QUERY...'); + $this->db->query('ANOTHER QUERY...'); + $this->db->trans_complete(); + + if ($this->db->trans_status() === FALSE) + { + // generate an error... or use the log_message() function to log your error + } + +Enabling Transactions +===================== + +Transactions are enabled automatically the moment you use +$this->db->trans_start(). If you would like to disable transactions you +can do so using $this->db->trans_off():: + + $this->db->trans_off(); + + $this->db->trans_start(); + $this->db->query('AN SQL QUERY...'); + $this->db->trans_complete(); + +When transactions are disabled, your queries will be auto-commited, just +as they are when running queries without transactions. + +Test Mode +========= + +You can optionally put the transaction system into "test mode", which +will cause your queries to be rolled back -- even if the queries produce +a valid result. To use test mode simply set the first parameter in the +$this->db->trans_start() function to TRUE:: + + $this->db->trans_start(TRUE); // Query will be rolled back + $this->db->query('AN SQL QUERY...'); + $this->db->trans_complete(); + +Running Transactions Manually +============================= + +If you would like to run transactions manually you can do so as follows:: + + $this->db->trans_begin(); + + $this->db->query('AN SQL QUERY...'); + $this->db->query('ANOTHER QUERY...'); + $this->db->query('AND YET ANOTHER QUERY...'); + + if ($this->db->trans_status() === FALSE) + { + $this->db->trans_rollback(); + } + else + { + $this->db->trans_commit(); + } + +.. note:: Make sure to use $this->db->trans_begin() when running manual + transactions, **NOT** $this->db->trans_start(). diff --git a/user_guide_src/source/database/utilities.rst b/user_guide_src/source/database/utilities.rst new file mode 100644 index 000000000..b0920109f --- /dev/null +++ b/user_guide_src/source/database/utilities.rst @@ -0,0 +1,224 @@ +###################### +Database Utility Class +###################### + +The Database Utility Class contains functions that help you manage your +database. + +.. contents:: Table of Contents + + +****************** +Function Reference +****************** + +Initializing the Utility Class +============================== + +.. important:: In order to initialize the Utility class, your database + driver must already be running, since the utilities class relies on it. + +Load the Utility Class as follows:: + + $this->load->dbutil() + +Once initialized you will access the functions using the $this->dbutil +object:: + + $this->dbutil->some_function() + +$this->dbutil->list_databases() +================================ + +Returns an array of database names:: + + $dbs = $this->dbutil->list_databases(); + + foreach ($dbs as $db) + { + echo $db; + } + +$this->dbutil->database_exists(); +================================== + +Sometimes it's helpful to know whether a particular database exists. +Returns a boolean TRUE/FALSE. Usage example:: + + if ($this->dbutil->database_exists('database_name')) + { + // some code... + } + +Note: Replace *database_name* with the name of the table you are +looking for. This function is case sensitive. + +$this->dbutil->optimize_table('table_name'); +============================================== + +.. note:: This features is only available for MySQL/MySQLi databases. + +Permits you to optimize a table using the table name specified in the +first parameter. Returns TRUE/FALSE based on success or failure:: + + if ($this->dbutil->optimize_table('table_name')) + { + echo 'Success!'; + } + +.. note:: Not all database platforms support table optimization. + +$this->dbutil->repair_table('table_name'); +============================================ + +.. note:: This features is only available for MySQL/MySQLi databases. + +Permits you to repair a table using the table name specified in the +first parameter. Returns TRUE/FALSE based on success or failure:: + + if ($this->dbutil->repair_table('table_name')) + { + echo 'Success!'; + } + +.. note:: Not all database platforms support table repairs. + +$this->dbutil->optimize_database(); +==================================== + +.. note:: This features is only available for MySQL/MySQLi databases. + +Permits you to optimize the database your DB class is currently +connected to. Returns an array containing the DB status messages or +FALSE on failure. + +:: + + $result = $this->dbutil->optimize_database(); + + if ($result !== FALSE) + { + print_r($result); + } + +.. note:: Not all database platforms support table optimization. + +$this->dbutil->csv_from_result($db_result) +============================================= + +Permits you to generate a CSV file from a query result. The first +parameter of the function must contain the result object from your +query. Example:: + + $this->load->dbutil(); + + $query = $this->db->query("SELECT * FROM mytable"); + + echo $this->dbutil->csv_from_result($query); + +The second, third, and fourth parameters allow you to set the delimiter +newline, and enclosure characters respectively. By default tabs are +used as the delimiter, "\n" is used as a new line, and a double-quote +is used as the enclosure. Example:: + + $delimiter = ","; + $newline = "\r\n"; + $enclosure = '"'; + + echo $this->dbutil->csv_from_result($query, $delimiter, $newline, $enclosure); + +.. important:: This function will NOT write the CSV file for you. It + simply creates the CSV layout. If you need to write the file + use the :doc:`File Helper <../helpers/file_helper>`. + +$this->dbutil->xml_from_result($db_result) +============================================= + +Permits you to generate an XML file from a query result. The first +parameter expects a query result object, the second may contain an +optional array of config parameters. Example:: + + $this->load->dbutil(); + + $query = $this->db->query("SELECT * FROM mytable"); + + $config = array ( + 'root' => 'root', + 'element' => 'element', + 'newline' => "\n", + 'tab' => "\t" + ); + + echo $this->dbutil->xml_from_result($query, $config); + +.. important:: This function will NOT write the XML file for you. It + simply creates the XML layout. If you need to write the file + use the :doc:`File Helper <../helpers/file_helper>`. + +$this->dbutil->backup() +======================= + +Permits you to backup your full database or individual tables. The +backup data can be compressed in either Zip or Gzip format. + +.. note:: This features is only available for MySQL databases. + +.. note:: Due to the limited execution time and memory available to PHP, + backing up very large databases may not be possible. If your database is + very large you might need to backup directly from your SQL server via + the command line, or have your server admin do it for you if you do not + have root privileges. + +Usage Example +------------- + +:: + + // Load the DB utility class + $this->load->dbutil(); + + // Backup your entire database and assign it to a variable + $backup =& $this->dbutil->backup(); + + // Load the file helper and write the file to your server + $this->load->helper('file'); + write_file('/path/to/mybackup.gz', $backup); + + // Load the download helper and send the file to your desktop + $this->load->helper('download'); + force_download('mybackup.gz', $backup); + +Setting Backup Preferences +-------------------------- + +Backup preferences are set by submitting an array of values to the first +parameter of the backup function. Example:: + + $prefs = array( + 'tables' => array('table1', 'table2'), // Array of tables to backup. + 'ignore' => array(), // List of tables to omit from the backup + 'format' => 'txt', // gzip, zip, txt + 'filename' => 'mybackup.sql', // File name - NEEDED ONLY WITH ZIP FILES + 'add_drop' => TRUE, // Whether to add DROP TABLE statements to backup file + 'add_insert' => TRUE, // Whether to add INSERT data to backup file + 'newline' => "\n" // Newline character used in backup file + ); + + $this->dbutil->backup($prefs); + +Description of Backup Preferences +--------------------------------- + +=============== ======================= ======================= ======================================================================== +Preference Default Value Options Description +=============== ======================= ======================= ======================================================================== +**tables** empty array None An array of tables you want backed up. If left blank all tables will be + exported. +**ignore** empty array None An array of tables you want the backup routine to ignore. +**format** gzip gzip, zip, txt The file format of the export file. +**filename** the current date/time None The name of the backed-up file. The name is needed only if you are using + zip compression. +**add_drop** TRUE TRUE/FALSE Whether to include DROP TABLE statements in your SQL export file. +**add_insert** TRUE TRUE/FALSE Whether to include INSERT statements in your SQL export file. +**newline** "\\n" "\\n", "\\r", "\\r\\n" Type of newline to use in your SQL export file. +=============== ======================= ======================= ========================================================================
\ No newline at end of file |