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