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