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

To select distinct values set distinct to true:

// SELECT DISTINCT `name` FROM `users`
$users = DB::select('name')->from('users')->distinct(true)->execute();

print_r($users->as_array());
/*
Array
(
    [0] => Array
        (
            [name] => Dirk
        )

    [1] => Array
        (
            [name] => John
        )

)
*/

Results

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.

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

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

Where statements

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

To influence the operator supply it like so:

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

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

The BETWEEN and IN also go through the where method:

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

Order by statement

For sorting data we use the order_by function.

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

Limit and offset

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.

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

Updating

For updating data we use DB::update. If successfully executed an update query will return an integer representing the amount of affected rows.

To update a single column.

// Will execute UPDATE `users` SET `name` = "John Random" WHERE `id` = "2";
$result = DB::update('users')
	->value("name", "John Random")
	->where('id', '=', '2')
	->execute();

To update multiple columns.

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

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

Escaping

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 database expressions, DB::expr. If you don't want a value to get escaped, just wrap it in a database expression.

Database expressions are especially helpful when dealing with thing like MySQL's native function (like COUNT) and predefined constands (like DEFAULT).

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