Fuel Documentation

Database Usage

Normal database interactions are to go through the DB class. The following examples will give you a feel for how to go about using the databse in fuel.

Database usage is devided into a couple of segments:

Running queries

First we prepare a query using DB::query.

// returns a new Database_Query
$query = DB::query('SELECT * FROM `users`');

Now we can execute that query:

$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');
// or
$query->execute(Database_Connection::instance('another_group'));

// And we can chain then like this:
$result = DB::query('SELECT * FROM `users`')->execute();

Selection data

First let's select data using DB::query. 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.


$result = DB::query('SELECT * FROM `users`', DB::SELECT)->execute();
			

We can also select data using DB::select or DB::select_array.

// Will execute SELECT `id`, `name` FROM `users`
$result = DB::select('id','name')->from('users')->execute();
			

If you want to alias columns, use arrays instead of strings

// Will execute SELECT `name` as `the_name` FROM `users`;
$result = DB::select(array('name','the_name'))->from('users')->execute();

Results

Executing a select query will generate a result object containing the requested database records. By default the result is fetched as objects. Here is an exaple how to influence this behaviour.

// 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('id','name')->from('users')->as_object('Model_Users')->execute();
			

Want to know how many records you have fetched? It's dead simple!


$result = DB::select('*')->from('users')->execute();
// Just count the results, it returns an int.
$num_rows = count($result);
			

To access these results you eighter loop through the result object directly, or get the result array.

$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
}

Optionaly we specify the array key and value to be returned from as_array

$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
}

Filtering

In order to set the conditions on our queries we can set WHERE conditions. These examples also apply to updating and deleting.

// 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` != 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();

Grouped where statements are also supported

// 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();

Inserting

For inserting data we use DB::insert. If succesfully executed an insert query will return an array containing a list of insert id and rows created.

// 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();

You can also set the columns and values seperatly

// 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();

Deleting

To delete records, use DB::delete. When executed it will return the number of rows affected.

// 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

Joins

When selecting data, you can also join other tables into the result.

// 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');