diff options
| -rw-r--r-- | user_guide_src/source/database/caching.rst | 12 | ||||
| -rw-r--r-- | user_guide_src/source/database/call_function.rst | 5 | ||||
| -rw-r--r-- | user_guide_src/source/database/configuration.rst | 36 | ||||
| -rw-r--r-- | user_guide_src/source/database/connecting.rst | 27 | ||||
| -rw-r--r-- | user_guide_src/source/database/examples.rst | 58 | ||||
| -rw-r--r-- | user_guide_src/source/database/fields.rst | 43 | ||||
| -rw-r--r-- | user_guide_src/source/database/forge.rst | 88 | ||||
| -rw-r--r-- | user_guide_src/source/database/helpers.rst | 22 | ||||
| -rw-r--r-- | user_guide_src/source/database/queries.rst | 6 | ||||
| -rw-r--r-- | user_guide_src/source/database/results.rst | 96 | ||||
| -rw-r--r-- | user_guide_src/source/database/table_data.rst | 15 | ||||
| -rw-r--r-- | user_guide_src/source/database/transactions.rst | 41 | ||||
| -rw-r--r-- | user_guide_src/source/database/utilities.rst | 74 |
13 files changed, 412 insertions, 111 deletions
diff --git a/user_guide_src/source/database/caching.rst b/user_guide_src/source/database/caching.rst index 7a195a7a1..d73120a93 100644 --- a/user_guide_src/source/database/caching.rst +++ b/user_guide_src/source/database/caching.rst @@ -124,7 +124,17 @@ $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"); + // 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() ========================== diff --git a/user_guide_src/source/database/call_function.rst b/user_guide_src/source/database/call_function.rst index bdc5be0a5..9890fc453 100644 --- a/user_guide_src/source/database/call_function.rst +++ b/user_guide_src/source/database/call_function.rst @@ -34,5 +34,6 @@ database result ID. The connection ID can be accessed using:: The result ID can be accessed from within your result object, like this:: - $query = $this->db->query("SOME QUERY"); $query->result_id; - + $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 index 77b4994a3..c2c5a33ab 100644 --- a/user_guide_src/source/database/configuration.rst +++ b/user_guide_src/source/database/configuration.rst @@ -51,27 +51,27 @@ when the database classes are initialized. Explanation of Values: ---------------------- -- **hostname** - The hostname of your database server. Often this is +- **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. +- **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 +- **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 +- **pconnect** - TRUE/FALSE (boolean) - Whether to use a persistent connection. -- **db_debug** - TRUE/FALSE (boolean) - Whether database errors should +- **db_debug** - TRUE/FALSE (boolean) - Whether database errors should be displayed. -- **cache_on** - TRUE/FALSE (boolean) - Whether database query caching +- **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 +- **cachedir** - The absolute server path to your database query cache directory. -- **char_set** - The character set used in communicating with the +- **char_set** - The character set used in communicating with the database. -- **dbcollat** - The character collation used in communicating with the +- **dbcollat** - The character collation used in communicating with the database. .. note:: For MySQL and MySQLi databases, this setting is only used @@ -83,19 +83,21 @@ Explanation of Values: 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 +- **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 +- **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" +- **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 +- **port** - The database port number. To use this value you have to add a line to the database config - array.:: + array. + +:: $db['default']['port'] = 5432; diff --git a/user_guide_src/source/database/connecting.rst b/user_guide_src/source/database/connecting.rst index 6c549434d..64adc3047 100644 --- a/user_guide_src/source/database/connecting.rst +++ b/user_guide_src/source/database/connecting.rst @@ -92,19 +92,20 @@ as indicated above). By setting the second parameter to TRUE (boolean) the function will return the database object. -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... +.. 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 =========================================== diff --git a/user_guide_src/source/database/examples.rst b/user_guide_src/source/database/examples.rst index bd2cc4d96..d1cd48837 100644 --- a/user_guide_src/source/database/examples.rst +++ b/user_guide_src/source/database/examples.rst @@ -24,7 +24,16 @@ 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(); + $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 @@ -34,7 +43,14 @@ 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']; } + $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'] @@ -45,14 +61,25 @@ 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; } } + $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; + $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 @@ -61,7 +88,9 @@ 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']; + $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'] @@ -71,7 +100,9 @@ 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(); + $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 =================== @@ -79,7 +110,12 @@ 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; } + $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 @@ -90,5 +126,11 @@ 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}') + $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 index 07730f5d3..b706ace7d 100644 --- a/user_guide_src/source/database/fields.rst +++ b/user_guide_src/source/database/fields.rst @@ -11,12 +11,22 @@ 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; } + $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; } + $query = $this->db->query('SELECT * FROM some_table'); + + foreach ($query->list_fields() as $field) + { + echo $field; + } $this->db->field_exists() ========================== @@ -24,11 +34,14 @@ $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... } + 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. +.. 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() ======================== @@ -38,16 +51,25 @@ 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. +.. 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; } + $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(); + $query = $this->db->query("YOUR QUERY"); + $fields = $query->field_data(); The following data is available from this function if supported by your database: @@ -55,5 +77,4 @@ 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 - +- 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 index ee033248c..bf17e2918 100644 --- a/user_guide_src/source/database/forge.rst +++ b/user_guide_src/source/database/forge.rst @@ -29,7 +29,10 @@ $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!'; } + if ($this->dbforge->create_database('my_db')) + { + echo 'Database created!'; + } $this->dbforge->drop_database('db_name') ========================================== @@ -37,7 +40,10 @@ $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!'; } + if ($this->dbforge->drop_database('my_db')) + { + echo 'Database deleted!'; + } **************************** Creating and Dropping Tables @@ -57,7 +63,13 @@ also require a 'constraint' key. :: - $fields = array( 'users' => array( 'type' => 'VARCHAR', 'constraint' => '100', ), ); // will translate to "users VARCHAR(100)" when the field is added. + $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: @@ -72,7 +84,27 @@ Additionally, the following key/values can be used: :: - $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, ), ); + $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 @@ -95,7 +127,7 @@ 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. +.. note:: Multiple calls to add_field() are cumulative. Creating an id field -------------------- @@ -106,7 +138,8 @@ Primary Key. :: - $this->dbforge->add_field('id'); // gives id INT(9) NOT NULL AUTO_INCREMENT + $this->dbforge->add_field('id'); + // gives id INT(9) NOT NULL AUTO_INCREMENT Adding Keys @@ -122,7 +155,18 @@ 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`) + $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 @@ -133,7 +177,8 @@ with :: - $this->dbforge->create_table('table_name'); // gives CREATE TABLE table_name + $this->dbforge->create_table('table_name'); + // gives CREATE TABLE table_name An optional second parameter set to TRUE adds an "IF NOT EXISTS" clause @@ -141,7 +186,8 @@ into the definition :: - $this->dbforge->create_table('table_name', TRUE); // gives CREATE TABLE IF NOT EXISTS table_name + $this->dbforge->create_table('table_name', TRUE); + // gives CREATE TABLE IF NOT EXISTS table_name Dropping a table @@ -151,7 +197,8 @@ Executes a DROP TABLE sql :: - $this->dbforge->drop_table('table_name'); // gives DROP TABLE IF EXISTS table_name + $this->dbforge->drop_table('table_name'); + // gives DROP TABLE IF EXISTS table_name Renaming a table @@ -161,7 +208,8 @@ 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 + $this->dbforge->rename_table('old_table_name', 'new_table_name'); + // gives ALTER TABLE old_table_name RENAME TO new_table_name **************** @@ -177,7 +225,11 @@ 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 + $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. @@ -206,7 +258,11 @@ 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 - - - + $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 index b0a5ce97b..7ea19e9f6 100644 --- a/user_guide_src/source/database/helpers.rst +++ b/user_guide_src/source/database/helpers.rst @@ -28,7 +28,9 @@ $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 + echo $this->db->count_all('my_table'); + + // Produces an integer, like 25 $this->db->platform() ===================== @@ -51,7 +53,9 @@ $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.... + $str = $this->db->last_query(); + + // Produces: SELECT * FROM sometable.... The following two functions help simplify the process of writing database INSERTs and UPDATEs. @@ -62,14 +66,16 @@ $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); + $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. +.. note:: Values are automatically escaped, producing safer queries. $this->db->update_string(); ============================ @@ -77,7 +83,11 @@ $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); + $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 @@ -85,4 +95,4 @@ array with the data to be updated, and the third parameter is the 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. +.. note:: Values are automatically escaped, producing safer queries. diff --git a/user_guide_src/source/database/queries.rst b/user_guide_src/source/database/queries.rst index cfc42c4c3..971d5d61d 100644 --- a/user_guide_src/source/database/queries.rst +++ b/user_guide_src/source/database/queries.rst @@ -41,7 +41,8 @@ the following:: 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 + $this->db->set_dbprefix('newprefix'); + $this->db->dbprefix('tablename'); // outputs newprefix_tablename ********************** @@ -101,7 +102,8 @@ 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')); + $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. diff --git a/user_guide_src/source/database/results.rst b/user_guide_src/source/database/results.rst index a85b89bef..4f93c794d 100644 --- a/user_guide_src/source/database/results.rst +++ b/user_guide_src/source/database/results.rst @@ -11,14 +11,31 @@ 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; } + $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; } } + $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) @@ -40,7 +57,14 @@ 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']; } + $query = $this->db->query("YOUR QUERY"); + + foreach ($query->result_array() as $row) + { + echo $row['title']; + echo $row['name']; + echo $row['body']; + } row() ===== @@ -49,7 +73,16 @@ 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; } + $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:: @@ -59,7 +92,11 @@ digit in the first parameter:: 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 + $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() ============ @@ -67,7 +104,16 @@ 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']; } + $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:: @@ -77,18 +123,18 @@ digit in the first parameter:: 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()** + | **$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')** + | **$row = $query->first_row('array')** + | **$row = $query->last_row('array')** + | **$row = $query->next_row('array')** + | **$row = $query->previous_row('array')** *********************** Result Helper Functions @@ -100,7 +146,9 @@ $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 = $this->db->query('SELECT * FROM my_table'); + + echo $query->num_rows(); $query->num_fields() ===================== @@ -108,7 +156,9 @@ $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 = $this->db->query('SELECT * FROM my_table'); + + echo $query->num_fields(); $query->free_result() ====================== @@ -120,5 +170,17 @@ 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 + $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 index ec7dbbf6c..744a05154 100644 --- a/user_guide_src/source/database/table_data.rst +++ b/user_guide_src/source/database/table_data.rst @@ -10,7 +10,12 @@ $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; } + $tables = $this->db->list_tables(); + + foreach ($tables as $table) + { + echo $table; + } $this->db->table_exists(); =========================== @@ -18,7 +23,9 @@ $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... } + if ($this->db->table_exists('table_name')) + { + // some code... + } -Note: Replace *table_name* with the name of the table you are looking -for. +.. 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 index e82210b96..e9190e59a 100644 --- a/user_guide_src/source/database/transactions.rst +++ b/user_guide_src/source/database/transactions.rst @@ -35,7 +35,11 @@ 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(); + $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 @@ -61,7 +65,15 @@ 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 } + $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 ===================== @@ -70,7 +82,11 @@ 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(); + $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. @@ -83,14 +99,29 @@ 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(); + $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(); } + $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 index ab7d6a149..b0920109f 100644 --- a/user_guide_src/source/database/utilities.rst +++ b/user_guide_src/source/database/utilities.rst @@ -32,7 +32,12 @@ $this->dbutil->list_databases() Returns an array of database names:: - $dbs = $this->dbutil->list_databases(); foreach ($dbs as $db) { echo $db; } + $dbs = $this->dbutil->list_databases(); + + foreach ($dbs as $db) + { + echo $db; + } $this->dbutil->database_exists(); ================================== @@ -40,7 +45,10 @@ $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... } + 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. @@ -53,7 +61,10 @@ $this->dbutil->optimize_table('table_name'); 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!'; } + if ($this->dbutil->optimize_table('table_name')) + { + echo 'Success!'; + } .. note:: Not all database platforms support table optimization. @@ -65,7 +76,10 @@ $this->dbutil->repair_table('table_name'); 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!'; } + if ($this->dbutil->repair_table('table_name')) + { + echo 'Success!'; + } .. note:: Not all database platforms support table repairs. @@ -80,7 +94,12 @@ FALSE on failure. :: - $result = $this->dbutil->optimize_database(); if ($result !== FALSE) { print_r($result); } + $result = $this->dbutil->optimize_database(); + + if ($result !== FALSE) + { + print_r($result); + } .. note:: Not all database platforms support table optimization. @@ -91,7 +110,11 @@ 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); + $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 @@ -115,7 +138,18 @@ 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); + $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 @@ -140,7 +174,19 @@ 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); + // 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 -------------------------- @@ -148,7 +194,17 @@ 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); + $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 --------------------------------- |