docs/classes/database/usage.html at develop · royappa/docs

378 lines (304 loc) · 12.5 KB
<!DOCTYPE html>
	<meta charset="utf-8">
	<title>Database - Usage - Fuel Documentation</title>
	<link href="../../assets/css/main.css" media="screen" rel="stylesheet" />
	<script type="text/javascript" src="../../assets/js/jquery-1.4.4.min.js"></script>
	<script type="text/javascript" src="../../assets/js/nav.js"></script>
	<script type="text/javascript" src="../../assets/js/highlight.pack.js"></script>
	<script type="text/javascript">
		$(function() {
			show_nav('classes', '../../');
		hljs.tabReplace = '    ';
		hljs.initHighlightingOnLoad();
		<h1>Fuel Documentation</h1>
	<div id="main-nav"></div>
	<section id="content">
		<h2>Database Usage</h2>
			Normal database interactions are to go through the <a href="./db.html">DB class</a>.
			The following examples will give you a feel for how to go about using databases in Fuel.
		<p>Database usage is devided into a couple of segments:</p>
			<li><a href="#running-queries">Running queries</a></li>
			<li><a href="#selecting">Selecting</a>,</li>
			<li><a href="#results">Results</a>,</li>
			<li><a href="#filtering">Filtering</a> (where, order by, limit, offset),</li>
			<li><a href="#inserting">Inserting</a>,</li>
			<li><a href="#updating">Updating</a>,</li>
			<li><a href="#deleting">Deleting</a> data</li>
			<li><a href="#joins">Joins</a></li>
			<li><a href="#escaping">Escaping</a></li>
		<article>
			<h2 id="running-queries">Running queries</h2>
			<p>First we prepare a query using <a href="./db.html#method_query">DB::query</a>.</p>
			<pre class="php"><code>// returns a new Database_Query
$query = DB::query('SELECT * FROM `users`');
</code></pre>
			<p>Now we can execute that query:</p>
			<pre class="php"><code>$query = DB::query('SELECT * FROM `users`');
// return a new Database_MySQLi_Result
$query->execute();
// Or execute is on a different database group
$query->execute('another_group');
$query->execute(Database_Connection::instance('another_group'));
// And we can chain then like this:
$result = DB::query('SELECT * FROM `users`')->execute();
</code></pre>
		</article>
		<article>
			<h2 id="selecting">Selection data</h2>
				First let's select data using <a href="./db.html#method_query">DB::query</a>.
				As we are going to fetch a result from this query, we need to let the it know
				what kind of query we are going to run.
			<pre class="php"><code>
$result = DB::query('SELECT * FROM `users`', DB::SELECT)->execute();
			</code></pre>
				We can also select data using <a href="./db.html#method_select">DB::select</a> or
				<a href="./db.html#method_select_array">DB::select_array</a>. 
			<pre class="php"><code>// Will execute SELECT `id`, `name` FROM `users`
$result = DB::select('id','name')->from('users')->execute();
			</code></pre>
			<p>If you want to alias columns, use arrays instead of strings</p>
			<pre class="php"><code>// Will execute SELECT `name` as `the_name` FROM `users`;
$result = DB::select(array('name','the_name'))->from('users')->execute();
</code></pre>
			<p>To select distinct values set distinct to <em>true</em>:</p>
			<pre class="php"><code>// SELECT DISTINCT `name` FROM `users`
$users = DB::select('name')->from('users')->distinct(true)->execute();
print_r($users->as_array());
    [0] => Array
            [name] => Dirk
    [1] => Array
            [name] => John
</code></pre>
			<h4 id="results">Results</h4>
				Executing a select query will generate a result object containing the requested database records.
				By default the result is fetched as associative arrays. Here is an exaple how to influence this
				behaviour.
			<pre class="php"><code>// Will fetch the result as an associative array.
$result = DB::select('id','name')->from('users')->as_assoc()->execute();
// Will fetch the result as an object.
$result = DB::select('id','name')->from('users')->as_object()->execute();
// Will fetch the result as an Model_Users object.
$result = DB::select()->from('users')->as_object('Model_Users')->execute();
			</code></pre>	
			<p>Want to know how many records you have fetched? It's dead simple!</p>
			<pre class="php"><code>
$result = DB::select('*')->from('users')->execute();
// Just count the results, it returns an int.
$num_rows = count($result);
			</code></pre>
			<p>To access these results you eighter loop through the result object directly, or get the result array.</p>
			<pre class="php"><code>$result = DB::select()->from('users')->execute();
foreach($result as $item)
	// do something with $item
$result_array = $result->as_array();
foreach($result_array as $item)
	// do something with $item
</code></pre>
			<p>Optionaly we specify the array key and value to be returned from <em>as_array</em></p>
			<pre class="php"><code>$result = DB::select()->from('users')->execute();
$on_key = $result->as_array('id');
foreach($on_key as $id => $item)
	// $id will contain the records id
	// do something with $item or it's $id
$key_value = $result->as_array('id', 'email');
foreach($key_value as $id => $email)
	// now $email will be the email field.
	// so you can do something with $id or $email
</code></pre>
		</article>
		<article>
			<h2 id="filtering">Filtering</h2>
			<h4 id="where_statements">Where statements</h4>
				In order to set the conditions on our queries we can set WHERE conditions.
				These examples also apply to updating and deleting.
			<pre class="php"><code>// Will execute SELECT * FROM `users` WHERE `id` = 1
$result = DB::select()->from('users')->where('id', 1)->execute();
</code></pre>
			<p>To influence the operator supply it like so:</p>
			<pre class="php"><code>// Will execute SELECT * FROM `users` WHERE `id` = 1
$result = DB::select()->from('users')->where('id', '=' 1)->execute();
// Will execute SELECT * FROM `users` WHERE `id` IN (1, 2, 3)
$id_array = array(1,2,3);
$result = DB::select()->from('users')->where('id', 'in', $id_array)->execute();
// Will execute SELECT * FROM `users` WHERE `id` BETWEEN 1 AND 2
$result = DB::select()->from('users')->where('id', 'between', array(1, 2))->execute();
// Will execute SELECT * FROM `users` WHERE `id` != 1
$result = DB::select()->from('users')->where('id', '!=', 1)->execute();
// Will execute SELECT * FROM `users` WHERE `name` LIKE "john%"
$who = "john%";
$result = DB::select()->from('users')->where('id', 'like', $who)->execute();
</code></pre>
			<p id="grouped_where">Grouped where statements are also supported:</p>
			<pre class="php"><code>// SELECT * FROM `users` WHERE (`name` = 'John' AND `email` = 'john@example.com')
// OR (`name` = 'mike' OR `name` = 'dirk')
$result = DB::select()->from('users')->where_open()
	->where('name', 'John')
	->and_where('email', 'john@example.com')
->where_close()
->or_where_open()
	->where('name', 'mike')
	->or_where('name', 'dirk')
->or_where_close()->execute();
</code></pre>
			<p id="where_between_in">The <em>BETWEEN</em> and <em>IN</em> also go through the <em>where</em> method:</p>
			<pre class="php"><code>// SELECT * FROM `users` WHERE `id` BETWEEN 1 AND 10
$users = DB::select()->from('users')->where('id', 'between', array(1, 10))->execute();
// SELECT * FROM `users` WHERE `name` IN ('john', 'simon', 'dirk')
$users = DB::select()->from('users')->where('name', 'in', array('john', 'simon', 'dirk'))->execute();
</code></pre>
			<h4 id="order_by">Order by statement</h4>
			<p>For sorting data we use the order_by function.</p>
			<pre class="php"><code>//SELECT * FROM `users` ORDER BY `name` ASC
DB::select()->from('users')->order_by('name','asc');
// SELECT * FROM `users` ORDER BY `name` ASC, `surname` DESC
DB::select()->from('users')->order_by('name','asc')->order_by('surname', 'desc');
// You can ommit the direction by leaving the second parameter out.
// SELECT * FROM `users` ORDER BY `name`
DB::select()->from('users')->order_by('name');
</code></pre>
			<h4 id="limit_offset">Limit and offset</h4>
			<p>For limiting the number of rows fetched we use the limit and offset function. Note that the offset function is only available when selecting data.</p>
			<pre class="php"><code></code>// SELECT * FROM `users` LIMIT 1
DB::select()->from('users')->limit(1);
// SELECT * FROM `users` LIMIT 10 OFFSET 5
DB::select()->from('users')->limit(10)->offset(5);
// SELECT * FROM `users` ORDER BY `id` ASC LIMIT 10
DB::select('users')->order_by('id','asc')->limit(10);
		</article>
		<article>
			<h2 id="updating">Updating</h2>
				For updating data we use <a href="./db.html#method_update">DB::update</a>.
				If successfully executed an update query will return an integer representing
				the amount of affected rows.
			<p>To update a single column.</p>
			<pre class="php"><code>// Will execute UPDATE `users` SET `name` = "John Random" WHERE `id` = "2";
$result = DB::update('users')
	->value("name", "John Random")
	->where('id', '=', '2')
	->execute();
</code></pre>
			<p>To update multiple columns.</p>
			<pre class="php"><code>// Will execute UPDATE `users`
// SET `group` = "Peter Griffon", `email` = "peter@thehindenpeter.com"
// WHERE `id` = "16";
$result = DB::update('users')
	->set(array(
		'name'  => "Peter Griffon",
		'email' => "peter@thehindenpeter.com"
	->where('id', '=', '16')
	->execute();
</code></pre>
		</article>
		<article>
			<h2 id="inserting">Inserting</h2>
				For inserting data we use <a href="./db.html#method_insert">DB::insert</a>.
				If succesfully executed an insert query will return an array containing a
				list of insert id and rows created.
			<pre class="php"><code>// Will execute INSERT INTO `users`(`name`,`email`,`password`) 
// VALUES ("John Random", "john@example.com", "s0_s3cr3t")
list($insert_id, $rows_affected) = DB::insert('users')->set(array(
	'name' => 'John Random',
	'email' => 'john@example.com',
	'password' => 's0_s3cr3t',
))->execute();
</code></pre>
			<p>You can also set the columns and values seperatly</p>
			<pre class="php"><code>// Will execute INSERT INTO `users`(`name`,`email`,`password`)
// VALUES ("John Random", "john@example.com", "s0_s3cr3t")
list($insert_id, $rows_affected) = DB::insert('users')->columns(array(
	'name', 'email', 'password'
))->values(array(
	'John Random', 'john@example.com', 's0_s3cr3t'
))->execute();
</code></pre>
		</article>
		<article>
			<h2 id="deleting">Deleting</h2>
				To delete records, use <a href="./db.html#method_delete">DB::delete</a>.
				When executed it will return the number of rows affected.
			<pre class="php"><code>// Empty the whole users table
$result = DB::delete('users')->execute(); // (int) 20
// Executes DELETE FROM `users` WHERE `email` LIKE "%@example.com"
$result = DB::delete('users')->where('email', 'like', '%@example.com')->execute(); // (int) 7
</code></pre>
		</article>
		<article>
			<h2 id="joins">Joins</h2>			
			<p>When selecting data, you can also join other tables into the result.</p>
			<pre class="php"><code>// Will execute SELECT * FROM `users` LEFT JOIN `roles` ON `roles`.`id` = `users`.`role_id`
$result = DB::select()->from('users')->join('roles','LEFT')->on('roles.id', '=', 'users.role_id');
// Will execute SELECT * FROM `users` RIGHT OUTER JOIN `roles` ON `roles`.`id` = `users`.`role_id`
$result = DB::select()->from('users')->join('roles','right outer')->on('roles.id', '=', 'users.role_id');
</code></pre>
		</article>
		<article>
			<h2 id="escaping">Escaping</h2>
			<p>Fields and values in database calls are escaped by default. In some cases you'll want to not escape data. The DB class provides a method to create <em>database expressions</em>, <a href="./db.html#method_expr">DB::expr</a>. If you don't want a value to get escaped, just wrap it in a database expression.</p>
			<p>Database expressions are especially helpful when dealing with thing like MySQL's native function (like <em>COUNT</em>) and predefined constands (like <em>DEFAULT</em>).</p>
			<pre class="php"><code>// Set a field to it's default
DB::update('users')->where('id', '=', 1)->set(array(
	'some_column' => DB::expr('DEFAULT'),
))->execute();
// SELECT COUNT(*) FROM `users`
$result = DB::select(DB::expr('COUNT(*) as count'))->from('users')->execute();
// Get the current/first result
$result_arr = $result->current();
// Get the number of rows
$count = $result_arr['count'];
</code></pre>
		</article>
	</section>
	<section id="footer">
			<a href="http://fuelphp.com">Fuel</a> is released under the MIT license.<br />
			&copy; 2010 - 2011 Fuel Development Team
	</section>
Provide feedback

Saved searches

Use saved searches to filter your results more quickly

FilesExpand file tree

usage.html

Latest commit

History

usage.html

File metadata and controls
