diff options
Diffstat (limited to 'user_guide_src/source/database')
| -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/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 |
12 files changed, 393 insertions, 94 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/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 --------------------------------- |