diff options
Diffstat (limited to 'user_guide/libraries/database/active_record.html')
| -rw-r--r-- | user_guide/libraries/database/active_record.html | 610 |
1 files changed, 610 insertions, 0 deletions
diff --git a/user_guide/libraries/database/active_record.html b/user_guide/libraries/database/active_record.html new file mode 100644 index 000000000..8fc3b8131 --- /dev/null +++ b/user_guide/libraries/database/active_record.html @@ -0,0 +1,610 @@ +<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
+<html>
+<head>
+
+<title>Code Igniter User Guide</title>
+
+<style type='text/css' media='all'>@import url('../../userguide.css');</style>
+<link rel='stylesheet' type='text/css' media='all' href='../../userguide.css' />
+
+<script type="text/javascript" src="../../scripts/nav.js"></script>
+<script type="text/javascript" src="../../scripts/prototype.lite.js"></script>
+<script type="text/javascript" src="../../scripts/moo.fx.js"></script>
+<script type="text/javascript">
+window.onload = function() {
+ myHeight = new fx.Height('nav', {duration: 400});
+ myHeight.hide();
+}
+</script>
+
+<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
+<meta http-equiv='expires' content='-1' />
+<meta http-equiv= 'pragma' content='no-cache' />
+<meta name='robots' content='all' />
+<meta name='author' content='Rick Ellis' />
+<meta name='description' content='Code Igniter User Guide' />
+
+</head>
+<body>
+
+<!-- START NAVIGATION -->
+<div id="nav"><div id="nav_inner"><script type="text/javascript">create_menu('../../');</script></div></div>
+<div id="nav2"><a name="top"></a><a href="javascript:void(0);" onclick="myHeight.toggle();"><img src="../../images/nav_toggle.jpg" width="153" height="44" border="0" title="Toggle Table of Contents" alt="Toggle Table of Contents" /></a></div>
+<div id="masthead">
+<table cellpadding="0" cellspacing="0" border="0" style="width:100%">
+<tr>
+<td><h1>Code Igniter User Guide Version 1.4.0</h1></td>
+<td id="breadcrumb_right"><a href="../../toc.html">Full Table of Contents</a></td>
+</tr>
+</table>
+</div>
+<!-- END NAVIGATION -->
+
+
+<!-- START BREADCRUMB -->
+<table cellpadding="0" cellspacing="0" border="0" style="width:100%">
+<tr>
+<td id="breadcrumb">
+<a href="http://www.codeigniter.com/">Code Igniter Home</a> ›
+<a href="../../index.html">User Guide Home</a> ›
+<a href="index.html">Database Library</a> ›
+Active Record
+</td>
+<td id="searchbox"><form method="get" action="http://www.google.com/search"><input type="hidden" name="as_sitesearch" id="as_sitesearch" value="www.codeigniter.com/user_guide/" />Search User Guide <input type="text" class="input" style="width:200px;" name="q" id="q" size="31" maxlength="255" value="" /> <input type="submit" class="submit" name="sa" value="Go" /></form></td>
+</tr>
+</table>
+<!-- END BREADCRUMB -->
+
+
+<br clear="all" />
+
+
+<!-- START CONTENT -->
+<div id="content">
+
+
+<h1>Active Record Class</h1>
+
+
+<p>Code Igniter 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.
+Code Igniter 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>
+
+<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.</p>
+
+<br />
+
+<ul>
+<li><a href="#select">Selecting Data</a></li>
+<li><a href="#insert">Inserting Data</a></li>
+<li><a href="#update">Updating Data</a></li>
+<li><a href="#delete">Deleting Data</a></li>
+<li><a href="#chaining">Method Chaining</a></li>
+</ul>
+
+
+
+
+<a name="select"> </a>
+<h1>Selecting Data</h1>
+
+<p>The following functions allow you to build SQL <strong>SELECT</strong> statements.</p>
+
+<p><strong>Note: If you are using PHP 5 you can use method chaining for more compact syntax. This is described at the end of the page.</strong></p>
+
+
+<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>
+
+<code>$query = $this->db->get('mytable');<br />
+<br />
+// Produces: SELECT * FROM mytable</code>
+
+<p>The second and third parameters enable you do set a limit and offset clause:</p>
+
+<code>$query = $this->db->get('mytable', 10, 20);<br />
+<br />
+// Produces: SELECT * FROM mytable LIMIT 20, 10 (in MySQL. Other databases have slightly different syntax)</code>
+
+
+
+<p>You'll notice that the above function is assigned to a variable named <kbd>$query</kbd>, which can be used to show the results:</p>
+
+<code>$query = $this->db->get('mytable');<br />
+<br />
+foreach ($query->result() as $row)<br />
+{<br />
+ echo $row->title;<br />
+}</code>
+
+<p>Please visit the <a href="results.html">result functions</a> page for a full discussion regarding result generation.</p>
+
+
+<h2>$this->db->getwhere();</h2>
+
+<p>Identical to the above function except that it permits you to add a "where" clause in the second parameter,
+instead of using the db->where() function:</p>
+
+<code>$query = $this->db->getwhere('mytable', array(id => $id), $limit, $offset);</code>
+
+<p>Please read the about the where function below for more information.</p>
+
+
+<h2>$this->db->select();</h2>
+
+<p>Permits you to write the SELECT portion of your query:</p>
+
+<code>
+$this->db->select('title, content, date');<br />
+<br />
+$query = $this->db->get('mytable');<br />
+<br />
+// Produces: SELECT title, content, date FROM mytable</code>
+
+<p><strong>Note: If you are selecting all (*) from a table you do not need to use this function. When omitted, Code Igniter assumes you wish to SELECT *</strong></p>
+
+
+<h2>$this->db->from();</h2>
+
+<p>Permits you to write the FROM portion of your query:</p>
+
+<code>
+$this->db->select('title, content, date');<br />
+$this->db->from('mytable');<br />
+<br />
+$query = $this->db->get();<br />
+<br />
+// Produces: SELECT title, content, date FROM mytable</code>
+
+<p><strong>Note: As shown earlier, the FROM portion of your query can be specified in the <dfn>$this->db->get()</dfn> function, so use whichever method
+you prefer.</strong></p>
+
+<h2>$this->db->join();</h2>
+
+<p>Permits you to write the JOIN portion of your query:</p>
+
+<code>
+$this->db->select('*');<br />
+$this->db->from('blogs');<br />
+$this->db->join('comments', 'comments.id = blogs.id');<br />
+<br />
+$query = $this->db->get();<br />
+<br />
+// Produces: <br />
+// SELECT * FROM blogs<br />
+// JOIN comments ON comments.id = blogs.id<br />
+</code>
+
+<p>Multiple function calls can be made if you need several joins in one query.</p>
+
+<p>If you need something other than a natural JOIN you can specify it via the third parameter of the function.
+Options are: left, right, outer, inner, left outer, and right outer.</p>
+
+<code>
+$this->db->join('comments', 'comments.id = blogs.id', <strong>'left'</strong>);<br />
+<br />
+// Produces: LEFT JOIN comments ON comments.id = blogs.id</code>
+
+
+
+
+
+<h2>$this->db->where();</h2>
+<p>This function enables you to set <strong>WHERE</strong> clauses using one of four methods:</p>
+
+<p class="important"><strong>Note:</strong> All values passed to this function are escaped automatically, producing safer queries.</p>
+
+<ol>
+ <li><strong>Simple key/value method:</strong>
+
+ <code>$this->db->where('name', $name);
+ <br /><br />// Produces: WHERE name = 'Joe'
+ </code>
+
+ <p>Notice that the equal sign is added for you.</p>
+
+ <p>If you use multiple function calls they will be chained together with <var>AND</var> between them:</p>
+
+ <code>$this->db->where('name', $name);<br />
+ $this->db->where('title', $title);<br />
+ $this->db->where('status', $status);
+ <br /><br />// WHERE = 'Joe' AND title = 'boss' AND status = 'active'
+ </code>
+
+
+ </li>
+
+ <li><strong>Custom key/value method:</strong>
+
+ <p>You can include an operator in the first parameter in order to to control the comparison:</p>
+
+ <code>$this->db->where('name !=', $name);<br />
+ $this->db->where('id <', $id);
+ <br /><br />// Produces: WHERE name != 'Joe' AND id < 45
+ </code>
+
+
+
+ </li>
+ <li><strong>Associative array method:</strong>
+
+
+ <code>
+ $array = array('name' => $name, 'title' => $title, 'status' => $status);<br /><br />
+
+ $this->db->where($array);
+ <br /><br />// Produces: WHERE name = 'Joe' AND title = 'boss' AND status = 'active'
+ </code>
+
+ <p>You can include your own operators using this method as well:</p>
+
+ <code>
+ $array = array('name !=' => $name, 'id <' => $id, 'date >' => $date);<br /><br />
+
+ $this->db->where($array);</code>
+
+ </li>
+ <li><strong>Custom string:</strong>
+
+ <p>You can write your own clauses manually:</p>
+
+ <code>
+ $where = "name='Joe' AND status='boss' OR status='active'";<br /><br />
+ $this->db->where($where);</code>
+
+ </li>
+</ol>
+
+
+<h2>$this->db->orwhere();</h2>
+<p>This function is identical to the one above, except that multiple instances are joined by OR:</p>
+
+<code>
+$this->db->where('name !=', $name);<br />
+$this->db->orwhere('id >', $id);
+<br /><br />// Produces: WHERE name != 'Joe' OR id > 50
+</code>
+
+
+
+
+<h2>$this->db->like();</h2>
+<p>This function enables you to generate <strong>LIKE</strong> clauses, useful for doing searches.</p>
+
+<p class="important"><strong>Note:</strong> All values passed to this function are escaped automatically.</p>
+
+
+<ol>
+ <li><strong>Simple key/value method:</strong>
+
+ <code>$this->db->like('title', $match);
+ <br /><br />// Produces: WHERE title LIKE '%match%'
+ </code>
+
+ <p>If you use multiple function calls they will be chained together with <var>AND</var> between them:</p>
+
+ <code>$this->db->like('title', $match);<br />
+ $this->db->like('body', $match);
+ <br /><br />// WHERE title LIKE '%match%' AND body LIKE '%match%'
+ </code>
+
+ </li>
+
+ <li><strong>Associative array method:</strong>
+
+ <code>
+ $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>
+</ol>
+
+
+<h2>$this->db->orlike();</h2>
+<p>This function is identical to the one above, except that multiple instances are joined by OR:</p>
+
+<code>
+$this->db->like('title', $match);<br />
+$this->db->orlike('body', $match);
+<br /><br />// WHERE title LIKE '%match%' OR body LIKE '%match%'
+</code>
+
+
+
+
+<h2>$this->db->groupby();</h2>
+
+<p>Permits you to write the GROUP BY portion of your query:</p>
+
+<code>$this->db->groupby("title");
+<br /><br />// Produces: GROUP BY title
+</code>
+
+<p>You can also pass an array of multiple values as well:</p>
+
+<code>$this->db->groupby(array("title", "date");
+<br /><br />// Produces: GROUP BY title, date
+</code>
+
+
+<h2>$this->db->having();</h2>
+
+<p>Permits you to write the HAVING portion of your query:</p>
+
+<code>$this->db->having('user_id = 45');
+<br /><br />// Produces: HAVING 'user_id = 45'
+</code>
+
+<p>You can also pass an array of multiple values as well:</p>
+
+
+<code>$this->db->having(array('title =' => 'My Title', 'id <' => $id));
+<br /><br />// Produces: HAVING title = 'My Title', 'id < 45'
+</code>
+
+
+
+<h2>$this->db->orderby();</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></p>
+
+<code>$this->db->orderby("title", "desc");
+<br /><br />// Produces: ORDER BY title DESC
+</code>
+
+<p>You can also pass your own string in the first parameter:</p>
+
+<code>$this->db->orderby('title desc, name asc');
+<br /><br />// Produces: ORDER BY title DESC, name ASC
+</code>
+
+
+
+<h2>$this->db->limit();</h2>
+<p>Lets you limit the number of rows you would like returned by the query:
+
+<code>
+$this->db->limit(10);<br />
+<br />
+// Produces: LIMIT 10</code>
+
+
+<p>The second parameter lets you set a result offset.</p>
+
+<code>
+$this->db->limit(10, 20);<br />
+<br />
+// Produces: LIMIT 20, 10 (in MySQL. Other databases have slightly different syntax)</code>
+
+
+<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>
+
+<code>echo $this->db->count_all('<var>my_table</var>');<br />
+<br />
+// Produces an integer, like 25</code>
+
+
+
+
+<a name="insert"> </a>
+<h1>Inserting Data</h1>
+
+<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>
+
+<code>
+$data = array(<br />
+ 'title' => $title,<br />
+ 'name' => $name,<br />
+ 'date' => $date<br />
+ );<br />
+<br />
+$this->db->insert('mytable', $data);
+<br /><br />
+// Produces: INSERT INTO mytable (title, name, date) VALUES ('{$title}', '{$name}', '{$date}')</code>
+
+<p>The first parameter will contain the table name, the second is an associative array of values.</p>
+
+<p>Here is an example using an object:</p>
+
+<code>
+/*<br />
+ class Myclass {<br />
+ var = $title = 'My Title';<br />
+ var = $content = 'My Content';<br />
+ var = $date = 'My Date';<br />
+ }<br />
+*/<br />
+<br />
+$object = new Myclass;<br />
+<br />
+$this->db->insert('mytable', $object);
+<br /><br />
+// Produces: INSERT INTO mytable (title, name, date) VALUES ('{$title}', '{$name}', '{$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>
+
+
+
+
+<h2>$this->db->set();</h2>
+<p>This function enables you to set values for <dfn>inserts</dfn> or <dfn>updates</dfn>.</p>
+
+<p><strong>It can be used instead of passing a data array directly to the insert or update functions:</strong> </p>
+
+<code>$this->db->set('name', $name);
+<br />
+$this->db->insert('mytable');
+<br /><br />
+// Produces: INSERT INTO mytable (name) VALUES ('{$name}')</code>
+
+<p>If you use multiple function called they will be assembled properly based on whether you are doing an insert or an update:</p>
+
+<code>$this->db->set('name', $name);<br />
+$this->db->set('title', $title);<br />
+$this->db->set('status', $status);<br />
+$this->db->insert('mytable');
+</code>
+
+<p>You can also pass an associative array to this function:</p>
+
+<code>
+$array = array('name' => $name, 'title' => $title, 'status' => $status);<br /><br />
+
+$this->db->set($array);<br />
+$this->db->insert('mytable');
+</code>
+
+<p>Or an object:</p>
+
+
+<code>
+/*<br />
+ class Myclass {<br />
+ var = $title = 'My Title';<br />
+ var = $content = 'My Content';<br />
+ var = $date = 'My Date';<br />
+ }<br />
+*/<br />
+<br />
+$object = new Myclass;<br />
+<br />
+$this->db->set($object);<br />
+$this->db->insert('mytable');
+</code>
+
+
+
+<a name="update"> </a>
+<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
+<strong>array</strong> or an <strong>object</strong> to the function. Here is an example using
+an array:</p>
+
+<code>
+$data = array(<br />
+ 'title' => $title,<br />
+ 'name' => $name,<br />
+ 'date' => $date<br />
+ );<br />
+<br />
+$this->db->where('id', $id);<br />
+$this->db->update('mytable', $data);
+<br /><br />
+// Produces:<br />
+// UPDATE mytable <br />
+// SET title = '{$title}', name = '{$name}', date = '{$date}'<br />
+// WHERE id = $id</code>
+
+<p>Or you can supply an object:</p>
+
+<code>
+/*<br />
+ class Myclass {<br />
+ var = $title = 'My Title';<br />
+ var = $content = 'My Content';<br />
+ var = $date = 'My Date';<br />
+ }<br />
+*/<br />
+<br />
+$object = new Myclass;<br />
+<br />
+$this->db->where('id', $id);<br />
+$this->db->update('mytable', $object, $where);
+<br /><br />
+// Produces:<br />
+// UPDATE mytable <br />
+// SET title = '{$title}', name = '{$name}', date = '{$date}'<br />
+// WHERE id = $id</code>
+
+
+
+<p class="important"><strong>Note:</strong> All values are escaped automatically producing safer queries.</p>
+
+<p>You'll notice the use of the <dfn>$this->db->where()</dfn> function, enabling you to set the WHERE clause.
+You can optionally pass this information directly into the update function as a string:</p>
+
+<code>$this->db->update('mytable', $data, "id = 4");</code>
+
+<p>Or as an array:</p>
+
+<code>$this->db->update('mytable', $data, array('id' => $id));</code>
+
+<p>You may also use the <dfn>$this->db->set()</dfn> function described above when performing updates.</p>
+
+
+<a name="delete"> </a>
+<h1>Deleting Data</h1>
+
+
+
+<h2>$this->db->delete();</h2>
+<p>Generates a delete SQL string and runs the query.</p>
+
+<code>
+$this->db->delete('mytable', array('id', $id));
+<br /><br />
+// Produces:<br />
+// DELETE FROM mytable <br />
+// WHERE id = $id</code>
+
+<p>The first parameter is the table name, the second is the where clause. You can also use the <dfn>where()</dfn> or <dfn>orwhere()</dfn> functions instead of passing
+the data to the second parameter of the function:
+
+<code>
+$this->db->where('id', $id);<br />
+$this->db->delete('mytable');
+<br /><br />
+// Produces:<br />
+// DELETE FROM mytable <br />
+// WHERE id = $id</code>
+
+<p class="important"><strong>Note:</strong> All values are escaped automatically producing safer queries.</p>
+
+
+<a name="chaining"> </a>
+<h1>Method Chaining</h1>
+
+<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 />
+<br />
+$query = $this->db->get();</code>
+
+<p class="important"><strong>Note:</strong> Method chaining only works with PHP 5.</p>
+
+
+
+</div>
+<!-- END CONTENT -->
+
+
+<div id="footer">
+<p>
+Previous Topic: <a href="results.html">Query Results</a>
+ ·
+<a href="#top">Top of Page</a> ·
+<a href="../../index.html">User Guide Home</a> ·
+Next Topic: <a href="fields.html">Field Metadata</a>
+<p>
+<p><a href="http://www.codeigniter.com">Code Igniter</a> · Copyright © 2006 · <a href="http://www.pmachine.com">pMachine, Inc.</a></p>
+</div>
+
+</body>
+</html>
\ No newline at end of file |