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/configuration.rst | 119 | ||||
| -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 | 121 |
13 files changed, 475 insertions, 178 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..687f0d920 100644 --- a/user_guide_src/source/database/configuration.rst +++ b/user_guide_src/source/database/configuration.rst @@ -12,7 +12,21 @@ 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; + $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 @@ -21,7 +35,21 @@ 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; + $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:: @@ -51,54 +79,45 @@ when the database classes are initialized. Explanation of Values: ---------------------- -- **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; - +====================== ================================================================================================== + 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 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 7dcf1df9c..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,42 +194,31 @@ 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 --------------------------------- -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. +=============== ======================= ======================= ======================================================================== +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 |