diff options
Diffstat (limited to 'user_guide_src/source/database')
| -rw-r--r-- | user_guide_src/source/database/active_record.rst | 119 | ||||
| -rw-r--r-- | user_guide_src/source/database/configuration.rst | 42 | ||||
| -rw-r--r-- | user_guide_src/source/database/connecting.rst | 23 |
3 files changed, 174 insertions, 10 deletions
diff --git a/user_guide_src/source/database/active_record.rst b/user_guide_src/source/database/active_record.rst index e1fc00bc5..228d1d509 100644 --- a/user_guide_src/source/database/active_record.rst +++ b/user_guide_src/source/database/active_record.rst @@ -54,6 +54,37 @@ $query, which can be used to show the results:: Please visit the :doc:`result functions <results>` page for a full discussion regarding result generation. +$this->db->get_compiled_select() +================================ + +Compiles the selection query just like `$this->db->get()`_ but does not *run* +the query. This method simply returns the SQL query as a string. + +Example:: + + $sql = $this->db->get_compiled_select('mytable'); + echo $sql; + + // Produces string: SELECT * FROM mytable + +The second parameter enables you to set whether or not the active record query +will be reset (by default it will be—just like `$this->db->get()`):: + + echo $this->db->limit(10,20)->get_compiled_select('mytable', FALSE); + // Produces string: SELECT * FROM mytable LIMIT 20, 10 + // (in MySQL. Other databases have slightly different syntax) + + echo $this->db->select('title, content, date')->get_compiled_select(); + + // Produces string: SELECT title, content, date FROM mytable + +The key thing to notice in the above example is that the second query did not +utilize `$this->db->from()`_ and did not pass a table name into the first +parameter. The reason for this outcome is because the query has not been +executed using `$this->db->get()`_ which resets values or reset directly +using `$this-db->reset_query()`_. + + $this->db->get_where() ====================== @@ -65,7 +96,7 @@ function:: Please read the about the where function below for more information. -Note: get_where() was formerly known as getwhere(), which has been +.. note:: get_where() was formerly known as getwhere(), which has been removed $this->db->select() @@ -167,7 +198,7 @@ Permits you to write the JOIN portion of your query:: $query = $this->db->get(); // Produces: - // SELECT * FROM blogs // JOIN comments ON comments.id = blogs.id + // SELECT * FROM blogs JOIN comments ON comments.id = blogs.id Multiple function calls can be made if you need several joins in one query. @@ -540,6 +571,41 @@ object. .. note:: All values are escaped automatically producing safer queries. +$this->db->get_compiled_insert() +================================ +Compiles the insertion query just like `$this->db->insert()`_ but does not +*run* the query. This method simply returns the SQL query as a string. + +Example:: + + $data = array( + 'title' => 'My title', + 'name' => 'My Name', + 'date' => 'My date' + ); + + $sql = $this->db->set($data)->get_compiled_insert('mytable'); + echo $sql; + + // Produces string: INSERT INTO mytable (title, name, date) VALUES ('My title', 'My name', 'My date') + +The second parameter enables you to set whether or not the active record query +will be reset (by default it will be--just like `$this->db->insert()`_):: + + echo $this->db->set('title', 'My Title')->get_compiled_insert('mytable', FALSE); + + // Produces string: INSERT INTO mytable (title) VALUES ('My Title') + + echo $this->db->set('content', 'My Content')->get_compiled_insert(); + + // Produces string: INSERT INTO mytable (title, content) VALUES ('My Title', 'My Content') + +The key thing to notice in the above example is that the second query did not +utlize `$this->db->from()`_ nor did it pass a table name into the first +parameter. The reason this worked is because the query has not been executed +using `$this->db->insert()`_ which resets values or reset directly using +`$this->db->reset_query()`_. + $this->db->insert_batch() ========================= @@ -717,6 +783,14 @@ array of values, the third parameter is the where key. .. note:: All values are escaped automatically producing safer queries. +$this->db->get_compiled_update() +================================ + +This works exactly the same way as ``$this->db->get_compiled_insert()`` except +that it produces an UPDATE SQL string instead of an INSERT SQL string. + +For more information view documentation for `$this->get_compiled_insert()`_. + ************* Deleting Data @@ -762,7 +836,7 @@ $this->db->empty_table() Generates a delete SQL string and runs the query.:: - $this->db->empty_table('mytable'); // Produces // DELETE FROM mytable + $this->db->empty_table('mytable'); // Produces: DELETE FROM mytable $this->db->truncate() @@ -773,7 +847,7 @@ Generates a truncate SQL string and runs the query. :: $this->db->from('mytable'); - $this->db->truncate(); + $this->db->truncate(); // or @@ -784,6 +858,13 @@ Generates a truncate SQL string and runs the query. .. note:: If the TRUNCATE command isn't available, truncate() will execute as "DELETE FROM table". + +$this->db->get_compiled_delete() +================================ +This works exactly the same way as ``$this->db->get_compiled_insert()`` except +that it produces a DELETE SQL string instead of an INSERT SQL string. + +For more information view documentation for `$this->get_compiled_insert()`_. *************** Method Chaining @@ -797,8 +878,6 @@ multiple functions. Consider this example:: ->limit(10, 20) ->get('mytable'); -.. note:: Method chaining only works with PHP 5. - .. _ar-caching: ********************* @@ -854,3 +933,31 @@ Here's a usage example:: where, like, group_by, having, order_by, set + +******************* +Reset Active Record +******************* + +Resetting Active Record allows you to start fresh with your query without +executing it first using a method like $this->db->get() or $this->db->insert(). +Just like the methods that execute a query, this will *not* reset items you've +cached using `Active Record Caching`_. + +This is useful in situations where you are using Active Record to generate SQL +(ex. ``$this->db->get_compiled_select()``) but then choose to, for instance, +run the query:: + + // Note that the second parameter of the get_compiled_select method is FALSE + $sql = $this->db->select(array('field1','field2')) + ->where('field3',5) + ->get_compiled_select('mytable', FALSE); + + // ... + // Do something crazy with the SQL code... like add it to a cron script for + // later execution or something... + // ... + + $data = $this->db->get()->result_array(); + + // Would execute and return an array of results of the following query: + // SELECT field1, field1 from mytable where field3 = 5; diff --git a/user_guide_src/source/database/configuration.rst b/user_guide_src/source/database/configuration.rst index 687f0d920..433c67152 100644 --- a/user_guide_src/source/database/configuration.rst +++ b/user_guide_src/source/database/configuration.rst @@ -28,6 +28,48 @@ prototype:: $db['default']['autoinit'] = TRUE; $db['default']['stricton'] = FALSE; +You can also specify failovers for the situation when the main connection cannot connect for some reason. +These failovers can be specified by setting the failover for a connection like this:: + + $db['default']['failover'] = array( + array( + 'hostname' => 'localhost1', + 'username' => '', + 'password' => '', + 'database' => '', + 'dbdriver' => 'mysql', + 'dbprefix' => '', + 'pconnect' => TRUE, + 'db_debug' => TRUE, + 'cache_on' => FALSE, + 'cachedir' => '', + 'char_set' => 'utf8', + 'dbcollat' => 'utf8_general_ci', + 'swap_pre' => '', + 'autoinit' => TRUE, + 'stricton' => FALSE + ), + array( + 'hostname' => 'localhost2', + 'username' => '', + 'password' => '', + 'database' => '', + 'dbdriver' => 'mysql', + 'dbprefix' => '', + 'pconnect' => TRUE, + 'db_debug' => TRUE, + 'cache_on' => FALSE, + 'cachedir' => '', + 'char_set' => 'utf8', + 'dbcollat' => 'utf8_general_ci', + 'swap_pre' => '', + 'autoinit' => TRUE, + 'stricton' => FALSE + ) + ); + +You can specify as many failovers as you like. + The reason we use a multi-dimensional array rather than a more simple one is to permit you to optionally store multiple sets of connection values. If, for example, you run multiple environments (development, diff --git a/user_guide_src/source/database/connecting.rst b/user_guide_src/source/database/connecting.rst index 64adc3047..a834cc0f7 100644 --- a/user_guide_src/source/database/connecting.rst +++ b/user_guide_src/source/database/connecting.rst @@ -57,7 +57,19 @@ file. To connect manually to a desired database you can pass an array of values:: - $config['hostname'] = "localhost"; $config['username'] = "myusername"; $config['password'] = "mypassword"; $config['database'] = "mydatabase"; $config['dbdriver'] = "mysql"; $config['dbprefix'] = ""; $config['pconnect'] = FALSE; $config['db_debug'] = TRUE; $config['cache_on'] = FALSE; $config['cachedir'] = ""; $config['char_set'] = "utf8"; $config['dbcollat'] = "utf8_general_ci"; $this->load->database($config); + $config['hostname'] = "localhost"; + $config['username'] = "myusername"; + $config['password'] = "mypassword"; + $config['database'] = "mydatabase"; + $config['dbdriver'] = "mysql"; + $config['dbprefix'] = ""; + $config['pconnect'] = FALSE; + $config['db_debug'] = TRUE; + $config['cache_on'] = FALSE; + $config['cachedir'] = ""; + $config['char_set'] = "utf8"; + $config['dbcollat'] = "utf8_general_ci"; + $this->load->database($config); For information on each of these values please see the :doc:`configuration page <configuration>`. @@ -68,14 +80,16 @@ page <configuration>`. Or you can submit your database values as a Data Source Name. DSNs must have this prototype:: - $dsn = 'dbdriver://username:password@hostname/database'; $this->load->database($dsn); + $dsn = 'dbdriver://username:password@hostname/database'; + $this->load->database($dsn); To override default config values when connecting with a DSN string, add the config variables as a query string. :: - $dsn = 'dbdriver://username:password@hostname/database?char_set=utf8&dbcollat=utf8_general_ci&cache_on=true&cachedir=/path/to/cache'; $this->load->database($dsn); + $dsn = 'dbdriver://username:password@hostname/database?char_set=utf8&dbcollat=utf8_general_ci&cache_on=true&cachedir=/path/to/cache'; + $this->load->database($dsn); Connecting to Multiple Databases ================================ @@ -83,7 +97,8 @@ Connecting to Multiple Databases If you need to connect to more than one database simultaneously you can do so as follows:: - $DB1 = $this->load->database('group_one', TRUE); $DB2 = $this->load->database('group_two', TRUE); + $DB1 = $this->load->database('group_one', TRUE); + $DB2 = $this->load->database('group_two', TRUE); Note: Change the words "group_one" and "group_two" to the specific group names you are connecting to (or you can pass the connection values |