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

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

or use DB::select_array which allows you to dynamically build your array of column names.

// Will execute SELECT `id`, `name` FROM `users`
$colums = array('id', 'name');
$result = DB::select_array($columns)->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'];

Query caching

The query builder also supports caching a query's result to help you bring down your database access. For this goal it uses the Cache class behind the scenes but handles both retrieval and regeneration of the caches.
The cached() method takes 2 arguements: the expiration time (cache validity in seconds) and a second arguement to set a custom key for the query (by default a md5 hash of the SQL). That will allow you to manually delete specific queries more easily and group sets of query caches into specific directories.

// Run a query and cache it for 1 hour
// If you run the exact same query the next time it will return the cached result.
// This will happens within the 3600 seconds of the cached one, if not it will run
// and cache another time.
$query = DB::query("SELECT * FROM users")->cached(3600)->execute();

// You can specify a key to handle the delete of cached results, useful if you
// know that will be updated and need to delete it before it gets shown.
$query = DB::query("SELECT * FROM users")->cached(3600, "foo.bar")->execute();

// This delete the previous query from cache
Cache::delete("foo.bar");
// Or delete all caches in the "foo" directory
Cache::delete_all("foo");

// By default all queries are put in a "db" directory
// Thus to delete all query caches for which the key wasn't set manully
Cache::delete_all("db");