diff options
Diffstat (limited to 'user_guide_src/source/database')
| -rw-r--r-- | user_guide_src/source/database/configuration.rst | 20 | ||||
| -rw-r--r-- | user_guide_src/source/database/forge.rst | 2 | ||||
| -rw-r--r-- | user_guide_src/source/database/query_builder.rst | 32 | ||||
| -rw-r--r-- | user_guide_src/source/database/results.rst | 133 |
4 files changed, 142 insertions, 45 deletions
diff --git a/user_guide_src/source/database/configuration.rst b/user_guide_src/source/database/configuration.rst index d21c79e44..8026be63a 100644 --- a/user_guide_src/source/database/configuration.rst +++ b/user_guide_src/source/database/configuration.rst @@ -152,9 +152,9 @@ when the database classes are initialized. Explanation of Values: ---------------------- -====================== ================================================================================================== +====================== =========================================================================================================== Name Config Description -====================== ================================================================================================== +====================== =========================================================================================================== **dsn** The DSN connect string (an all-in-one configuration sequence). **hostname** The hostname of your database server. Often this is 'localhost'. **username** The username used to connect to the database. @@ -179,6 +179,17 @@ Explanation of Values: customizable by the end user. **schema** The database schema, defaults to 'public'. Used by PostgreSQL and ODBC drivers. **encrypt** Whether or not to use an encrypted connection. + + - 'mysql' (deprecated), 'sqlsrv' and 'pdo/sqlsrv' drivers accept TRUE/FALSE + - 'mysqli' and 'pdo/mysql' drivers accept an array with the following options: + + - 'ssl_key' - Path to the private key file + - 'ssl_cert' - Path to the public key certificate file + - 'ssl_ca' - Path to the certificate authority file + - 'ssl_capath' - Path to a directory containing trusted CA certificats in PEM format + - 'ssl_cipher' - List of *allowed* ciphers to be used for the encryption, separated by colons (':') + - 'ssl_verify' - TRUE/FALSE; Whether to verify the server certificate or not ('mysqli' only) + **compress** Whether or not to use client compression (MySQL only). **stricton** TRUE/FALSE (boolean) - Whether to force "Strict Mode" connections, good for ensuring strict SQL while developing an application. @@ -186,10 +197,11 @@ Explanation of Values: :: $db['default']['port'] = 5432; -====================== ================================================================================================== + +====================== =========================================================================================================== .. note:: Depending on what database platform you are using (MySQL, PostgreSQL, etc.) not all values will be needed. For example, when using SQLite you will not need to supply a username or password, and the database name will be the path to your database file. The information above assumes - you are using MySQL.
\ No newline at end of file + you are using MySQL. diff --git a/user_guide_src/source/database/forge.rst b/user_guide_src/source/database/forge.rst index a875f7418..646e3a56e 100644 --- a/user_guide_src/source/database/forge.rst +++ b/user_guide_src/source/database/forge.rst @@ -143,6 +143,8 @@ string into the field definitions with add_field() $this->dbforge->add_field("label varchar(100) NOT NULL DEFAULT 'default label'"); +.. note:: Passing raw strings as fields cannot be followed by ``add_key()`` calls on those fields. + .. note:: Multiple calls to add_field() are cumulative. Creating an id field diff --git a/user_guide_src/source/database/query_builder.rst b/user_guide_src/source/database/query_builder.rst index 0a6d98744..737a4230e 100644 --- a/user_guide_src/source/database/query_builder.rst +++ b/user_guide_src/source/database/query_builder.rst @@ -640,18 +640,18 @@ Example:: $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') + // 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 query builder 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') + // 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') + // 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 @@ -730,7 +730,7 @@ or update functions:** :: $this->db->set('name', $name); - $this->db->insert('mytable'); // Produces: INSERT INTO mytable (name) VALUES ('{$name}') + $this->db->insert('mytable'); // Produces: INSERT INTO mytable (`name`) VALUES ('{$name}') If you use multiple function called they will be assembled properly based on whether you are doing an insert or an update:: @@ -740,18 +740,20 @@ based on whether you are doing an insert or an update:: $this->db->set('status', $status); $this->db->insert('mytable'); -**set()** will also accept an optional third parameter ($escape), that +**set()** will also accept an optional third parameter (``$escape``), that will prevent data from being escaped if set to FALSE. To illustrate the -difference, here is set() used both with and without the escape +difference, here is ``set()`` used both with and without the escape parameter. :: $this->db->set('field', 'field+1', FALSE); - $this->db->insert('mytable'); // gives INSERT INTO mytable (field) VALUES (field+1) - $this->db->set('field', 'field+1'); - $this->db->insert('mytable'); // gives INSERT INTO mytable (field) VALUES ('field+1') + $this->db->where('id', 2); + $this->db->update('mytable'); // gives UPDATE mytable SET field = field+1 WHERE id = 2 + $this->db->set('field', 'field+1'); + $this->db->where('id', 2); + $this->db->update('mytable'); // gives UPDATE `mytable` SET `field` = 'field+1' WHERE `id` = 2 You can also pass an associative array to this function:: @@ -792,7 +794,11 @@ is an example using an array:: $this->db->where('id', $id); $this->db->update('mytable', $data); - // Produces: // UPDATE mytable // SET title = '{$title}', name = '{$name}', date = '{$date}' // WHERE id = $id + // Produces: + // + // UPDATE mytable + // SET title = '{$title}', name = '{$name}', date = '{$date}' + // WHERE id = $id Or you can supply an object:: @@ -807,7 +813,11 @@ Or you can supply an object:: $object = new Myclass; $this->db->where('id', $id); $this->db->update('mytable', $object); - // Produces: // UPDATE mytable // SET title = '{$title}', name = '{$name}', date = '{$date}' // WHERE id = $id + // Produces: + // + // UPDATE `mytable` + // SET `title` = '{$title}', `name` = '{$name}', `date` = '{$date}' + // WHERE id = `$id` .. note:: All values are escaped automatically producing safer queries. diff --git a/user_guide_src/source/database/results.rst b/user_guide_src/source/database/results.rst index ac44566d3..6fea7c6be 100644 --- a/user_guide_src/source/database/results.rst +++ b/user_guide_src/source/database/results.rst @@ -19,7 +19,7 @@ This method returns the query result as an array of **objects**, or loop, like this:: $query = $this->db->query("YOUR QUERY"); - + foreach ($query->result() as $row) { echo $row->title; @@ -29,22 +29,7 @@ loop, like this:: The above method is an alias of ``result_object()``. -If you run queries that might **not** produce a result, you are -encouraged to test the result first:: - - $query = $this->db->query("YOUR QUERY"); - - if ($query->num_rows() > 0) - { - foreach ($query->result() as $row) - { - echo $row->title; - echo $row->name; - echo $row->body; - } - } - -You can also pass a string to result() which represents a class to +You can also pass a string to ``result()`` which represents a class to instantiate for each result object (note: this class must be loaded) :: @@ -64,7 +49,7 @@ 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']; @@ -83,11 +68,11 @@ 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(); + + if (isset($row)) { - $row = $query->row(); - echo $row->title; echo $row->name; echo $row->body; @@ -113,11 +98,11 @@ Identical to the above ``row()`` method, except it returns an array. Example:: $query = $this->db->query("YOUR QUERY"); - - if ($query->num_rows() > 0) + + $row = $query->row_array(); + + if (isset($row)) { - $row = $query->row_array(); - echo $row['title']; echo $row['name']; echo $row['body']; @@ -157,7 +142,7 @@ it returns the current row and moves the internal data pointer ahead. :: $query = $this->db->query("YOUR QUERY"); - + while ($row = $query->unbuffered_row()) { echo $row->title; @@ -173,6 +158,94 @@ the returned value's type:: $query->unbuffered_row('array'); // associative array ********************* +Custom Result Objects +********************* + +You can have the results returned as an instance of a custom class instead +of a ``stdClass`` or array, as the ``result()`` and ``result_array()`` +methods allow. This requires that the class is already loaded into memory. +The object will have all values returned from the database set as properties. +If these have been declared and are non-public then you should provide a +``__set()`` method to allow them to be set. + +Example:: + + class User { + + public $id; + public $email; + public $username; + + protected $last_login; + + public function last_login($format) + { + return $this->last_login->format($format); + } + + public function __set($name, $value) + { + if ($name === 'last_login') + { + $this->last_login = DateTime::createFromFormat('U', $value); + } + } + + public function __get($name) + { + if (isset($this->$name)) + { + return $this->$name; + } + } + } + +In addition to the two methods listed below, the following methods also can +take a class name to return the results as: ``first_row()``, ``last_row()``, +``next_row()``, and ``previous_row()``. + +**custom_result_object()** + +Returns the entire result set as an array of instances of the class requested. +The only parameter is the name of the class to instantiate. + +Example:: + + $query = $this->db->query("YOUR QUERY"); + + $rows = $query->custom_result_object('User'); + + foreach ($rows as $row) + { + echo $row->id; + echo $row->email; + echo $row->last_login('Y-m-d'); + } + +**custom_row_object()** + +Returns a single row from your query results. The first parameter is the row +number of the results. The second parameter is the class name to instantiate. + +Example:: + + $query = $this->db->query("YOUR QUERY"); + + $row = $query->custom_row_object(0, 'User'); + + if (isset($row)) + { + echo $row->email; // access attributes + echo $row->last_login('Y-m-d'); // access class methods + } + +You can also use the ``row()`` method in exactly the same way. + +Example:: + + $row = $query->custom_row_object(0, 'User'); + +********************* Result Helper Methods ********************* @@ -182,7 +255,7 @@ 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(); .. note:: Not all database drivers have a native way of getting the total @@ -196,7 +269,7 @@ The number of FIELDS (columns) returned by the query. Make sure to call the method using your query result object:: $query = $this->db->query('SELECT * FROM my_table'); - + echo $query->num_fields(); **free_result()** @@ -210,7 +283,7 @@ result has been generated in order to cut down on memory consumption. Example:: $query = $this->db->query('SELECT title FROM my_table'); - + foreach ($query->result() as $row) { echo $row->title; |