diff options
Diffstat (limited to 'user_guide/database/active_record.html')
-rw-r--r-- | user_guide/database/active_record.html | 101 |
1 files changed, 63 insertions, 38 deletions
diff --git a/user_guide/database/active_record.html b/user_guide/database/active_record.html index 30c45fdde..566b260c9 100644 --- a/user_guide/database/active_record.html +++ b/user_guide/database/active_record.html @@ -27,7 +27,7 @@ <div id="masthead"> <table cellpadding="0" cellspacing="0" border="0" style="width:100%"> <tr> -<td><h1>CodeIgniter User Guide Version 2.0.0</h1></td> +<td><h1>CodeIgniter User Guide Version 2.0.2</h1></td> <td id="breadcrumb_right"><a href="../toc.html">Table of Contents Page</a></td> </tr> </table> @@ -59,12 +59,12 @@ Active Record <p>CodeIgniter uses a modified version of the Active Record Database Pattern. This pattern allows information to be retrieved, inserted, and updated in your database with minimal scripting. In some cases only one or two lines of code are necessary to perform a database action. -CodeIgniter does not require that each database table be its own class file. It instead provides a more simplified interface.</p> +CodeIgniter does not require that each database table be its own class file. It instead provides a more simplified interface.</p> <p>Beyond simplicity, a major benefit to using the Active Record features is that it allows you to create database independent applications, since the query syntax -is generated by each database adapter. It also allows for safer queries, since the values are escaped automatically by the system.</p> +is generated by each database adapter. It also allows for safer queries, since the values are escaped automatically by the system.</p> -<p class="important"><strong>Note:</strong> If you intend to write your own queries you can disable this class in your database config file, allowing the core database library and adapter to utilize fewer resources.<br /></p> +<p class="important"><strong>Note:</strong> If you intend to write your own queries you can disable this class in your database config file, allowing the core database library and adapter to utilize fewer resources.<br /></p> <ul> <li><a href="#select">Selecting Data</a></li> @@ -84,7 +84,7 @@ is generated by each database adapter. It also allows for safer queries, since <h2>$this->db->get();</h2> -<p>Runs the selection query and returns the result. Can be used by itself to retrieve all records from a table:</p> +<p>Runs the selection query and returns the result. Can be used by itself to retrieve all records from a table:</p> <code>$query = $this->db->get('mytable');<br /> <br /> @@ -126,7 +126,7 @@ $this->db->select('title, content, date');<br /> $query = $this->db->get('mytable');<br /> <br /> // Produces: SELECT title, content, date FROM mytable</code></p> -<p class="important"><strong>Note:</strong> If you are selecting all (*) from a table you do not need to use this function. When omitted, CodeIgniter assumes you wish to SELECT *</p> +<p class="important"><strong>Note:</strong> If you are selecting all (*) from a table you do not need to use this function. When omitted, CodeIgniter assumes you wish to SELECT *</p> <p>$this->db->select() accepts an optional second parameter. If you set it to FALSE, CodeIgniter will not try to protect your field or table names with backticks. This is useful if you need a compound select statement.</p> <p><code>$this->db->select('(SELECT SUM(payments.amount) FROM payments WHERE payments.invoice_id=4') AS amount_paid', FALSE); <br /> @@ -278,7 +278,7 @@ $this->db->or_where('id >', $id); <h2>$this->db->where_in();</h2> -<p>Generates a WHERE field IN ('item', 'item') SQL query joined with AND if appropriate</p> +<p>Generates a WHERE field IN ('item', 'item') SQL query joined with AND if appropriate</p> <p><code> $names = array('Frank', 'Todd', 'James');<br /> $this->db->where_in('username', $names);<br /> @@ -322,7 +322,7 @@ $this->db->or_where('id >', $id); <code>$this->db->like('title', 'match');<br /> $this->db->like('body', 'match'); <br /><br /> - // WHERE title LIKE '%match%' AND body LIKE '%match%</code> + // WHERE title LIKE '%match%' AND body LIKE '%match%</code> If you want to control where the wildcard (%) is placed, you can use an optional third argument. Your options are 'before', 'after' and 'both' (which is the default). <code>$this->db->like('title', 'match', 'before'); <br /> @@ -340,7 +340,7 @@ $this->db->or_where('id >', $id); $array = array('title' => $match, 'page1' => $match, 'page2' => $match);<br /><br /> $this->db->like($array); - <br /><br />// WHERE title LIKE '%match%' AND page1 LIKE '%match%' AND page2 LIKE '%match%'</code></li> + <br /><br />// WHERE title LIKE '%match%' AND page1 LIKE '%match%' AND page2 LIKE '%match%'</code></li> </ol> @@ -351,7 +351,7 @@ $this->db->or_where('id >', $id); $this->db->like('title', 'match');<br /> $this->db->or_like('body', $match); <br /> -<br />// WHERE title LIKE '%match%' OR body LIKE '%match%'</code> +<br />// WHERE title LIKE '%match%' OR body LIKE '%match%'</code> @@ -367,7 +367,7 @@ $this->db->or_like('body', $match); <code> $this->db->like('title', 'match');<br /> $this->db->or_not_like('body', 'match'); <br /> <br /> -// WHERE title LIKE '%match% OR body NOT LIKE '%match%'</code> +// WHERE title LIKE '%match% OR body NOT LIKE '%match%'</code> <h2>$this->db->group_by();</h2> <p>Permits you to write the GROUP BY portion of your query:</p> @@ -385,7 +385,7 @@ $this->db->or_not_like('body', 'match'); <br /> <h2> $this->db->distinct();<br /> </h2> -<p>Adds the "DISTINCT" keyword to a query</p> +<p>Adds the "DISTINCT" keyword to a query</p> <p><code>$this->db->distinct();<br /> $this->db->get('table');<br /> <br /> @@ -397,7 +397,7 @@ $this->db->or_not_like('body', 'match'); <br /> <br /> // Produces: HAVING user_id = 45<br /> <br /> -$this->db->having('user_id', 45); <br /> +$this->db->having('user_id', 45); <br /> // Produces: HAVING user_id = 45<br /> <br /> </code> @@ -409,16 +409,16 @@ $this->db->having('user_id', 45); <br /> <br /> // Produces: HAVING title = 'My Title', id < 45</code></p> <p>If you are using a database that CodeIgniter escapes queries for, you can prevent escaping content by passing an optional third argument, and setting it to FALSE.</p> -<p><code>$this->db->having('user_id', 45); <br /> +<p><code>$this->db->having('user_id', 45); <br /> // Produces: HAVING `user_id` = 45 in some databases such as MySQL <br /> - $this->db->having('user_id', 45, FALSE); <br /> + $this->db->having('user_id', 45, FALSE); <br /> // Produces: HAVING user_id = 45</code></p> <h2>$this->db->or_having();</h2> <p>Identical to having(), only separates multiple clauses with "OR".</p> <h2>$this->db->order_by();</h2> <p>Lets you set an ORDER BY clause. The first parameter contains the name of the column you would like to order by. -The second parameter lets you set the direction of the result. Options are <kbd>asc</kbd> or <kbd>desc</kbd>, or <kbd>random</kbd>. </p> +The second parameter lets you set the direction of the result. Options are <kbd>asc</kbd> or <kbd>desc</kbd>, or <kbd>random</kbd>. </p> <code>$this->db->order_by("title", "desc"); <br /> @@ -455,12 +455,12 @@ $this->db->limit(10);<br /> <code> $this->db->limit(10, 20);<br /> <br /> -// Produces: LIMIT 20, 10 (in MySQL. Other databases have slightly different syntax)</code> +// Produces: LIMIT 20, 10 (in MySQL. Other databases have slightly different syntax)</code> <h2>$this->db->count_all_results();</h2> -<p>Permits you to determine the number of rows in a particular Active Record query. Queries will accept Active Record restrictors such as where(), or_where(), like(), or_like(), etc. Example:</p> +<p>Permits you to determine the number of rows in a particular Active Record query. Queries will accept Active Record restrictors such as where(), or_where(), like(), or_like(), etc. Example:</p> <code>echo $this->db->count_all_results('<var>my_table</var>');<br /> // Produces an integer, like 25<br /> @@ -472,7 +472,7 @@ echo $this->db->count_all_results();<br /> <h2>$this->db->count_all();</h2> -<p>Permits you to determine the number of rows in a particular table. Submit the table name in the first parameter. Example:</p> +<p>Permits you to determine the number of rows in a particular table. Submit the table name in the first parameter. Example:</p> <code>echo $this->db->count_all('<var>my_table</var>');<br /> <br /> @@ -485,14 +485,14 @@ echo $this->db->count_all_results();<br /> <h2>$this->db->insert();</h2> <p>Generates an insert string based on the data you supply, and runs the query. You can either pass an -<strong>array</strong> or an <strong>object</strong> to the function. Here is an example using an array:</p> +<strong>array</strong> or an <strong>object</strong> to the function. Here is an example using an array:</p> <code> $data = array(<br /> - 'title' => 'My title' ,<br /> - 'name' => 'My Name' ,<br /> - 'date' => 'My date'<br /> - );<br /> + 'title' => 'My title' ,<br /> + 'name' => 'My Name' ,<br /> + 'date' => 'My date'<br /> +);<br /> <br /> $this->db->insert('mytable', $data); <br /><br /> @@ -505,9 +505,9 @@ $this->db->insert('mytable', $data); <code> /*<br /> class Myclass {<br /> - var $title = 'My Title';<br /> - var $content = 'My Content';<br /> - var $date = 'My Date';<br /> + var $title = 'My Title';<br /> + var $content = 'My Content';<br /> + var $date = 'My Date';<br /> }<br /> */<br /> <br /> @@ -517,10 +517,35 @@ $this->db->insert('mytable', $object); <br /><br /> // Produces: INSERT INTO mytable (title, content, date) VALUES ('My Title', 'My Content', 'My Date')</code> -<p>The first parameter will contain the table name, the second is an associative array of values.</p> +<p>The first parameter will contain the table name, the second is an object.</p> <p class="important"><strong>Note:</strong> All values are escaped automatically producing safer queries.</p> +<h2>$this->db->insert_batch();</h2> +<p>Generates an insert string based on the data you supply, and runs the query. You can either pass an +<strong>array</strong> or an <strong>object</strong> to the function. Here is an example using an array:</p> + +<code> +$data = array(<br/> + array(<br /> + 'title' => 'My title' ,<br /> + 'name' => 'My Name' ,<br /> + 'date' => 'My date'<br /> + ),<br /> + array(<br /> + 'title' => 'Another title' ,<br /> + 'name' => 'Another Name' ,<br /> + 'date' => 'Another date'<br /> + )<br/> +);<br /> +<br /> +$this->db->update_batch('mytable', $data); +<br /><br /> +// Produces: INSERT INTO mytable (title, name, date) VALUES ('My title', 'My name', 'My date'), ('Another title', 'Another name', 'Another date')</code> + +<p>The first parameter will contain the table name, the second is an associative array of values.</p> + +<p class="important"><strong>Note:</strong> All values are escaped automatically producing safer queries.</p> @@ -563,9 +588,9 @@ $this->db->insert('mytable'); <code> /*<br /> class Myclass {<br /> - var $title = 'My Title';<br /> - var $content = 'My Content';<br /> - var $date = 'My Date';<br /> + var $title = 'My Title';<br /> + var $content = 'My Content';<br /> + var $date = 'My Date';<br /> }<br /> */<br /> <br /> @@ -581,7 +606,7 @@ $this->db->insert('mytable'); <h1>Updating Data</h1> <h2>$this->db->update();</h2> -<p>Generates an update string and runs the query based on the data you supply. You can pass an +<p>Generates an update string and runs the query based on the data you supply. You can pass an <strong>array</strong> or an <strong>object</strong> to the function. Here is an example using an array:</p> @@ -605,9 +630,9 @@ $this->db->update('mytable', $data); <code> /*<br /> class Myclass {<br /> - var $title = 'My Title';<br /> - var $content = 'My Content';<br /> - var $date = 'My Date';<br /> + var $title = 'My Title';<br /> + var $content = 'My Content';<br /> + var $date = 'My Date';<br /> }<br /> */<br /> <br /> @@ -686,7 +711,7 @@ $this->db->truncate('mytable'); <br /> <h1><a name="chaining"> </a>Method Chaining</h1> -<p>Method chaining allows you to simplify your syntax by connecting multiple functions. Consider this example:</p> +<p>Method chaining allows you to simplify your syntax by connecting multiple functions. Consider this example:</p> <code> <dfn>$this->db</dfn><kbd>-></kbd><var>select</var>('title')<kbd>-></kbd><var>from</var>('mytable')<kbd>-></kbd><var>where</var>('id', $id)<kbd>-></kbd><var>limit</var>(10, 20);<br /> @@ -727,14 +752,14 @@ $this->db->get('tablename');<br /> $this->db->select('field2');<br /> $this->db->get('tablename');<br /> <br /> -//Generates: SELECT `field1`, `field2` FROM (`tablename`)<br /> +//Generates: SELECT `field1`, `field2` FROM (`tablename`)<br /> <br /> $this->db->flush_cache();<br /> <br /> $this->db->select('field2');<br /> $this->db->get('tablename');<br /> <br /> -//Generates: SELECT `field2` FROM (`tablename`)</code></p> +//Generates: SELECT `field2` FROM (`tablename`)</code></p> <p class="important"> <strong>Note:</strong> The following statements can be cached: select, from, join, where, like, group_by, having, order_by, set</p> <p> </p> |