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