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 divided into a couple of segments:
First we prepare a query using DB::query.
$query = DB::query('SELECT * FROM `users`');
Now we can execute that query:
$query = DB::query('SELECT * FROM `users`');
$query->execute();
$query->execute('another_group');
$query->execute(Database_Connection::instance('another_group'));
$result = DB::query('SELECT * FROM `users`')->execute();
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
$result = DB::select('id','name')->from('users')->execute();
or use DB::select_array which
allows you to dynamically build your array of column names.
$columns = array('id', 'name');
$result = DB::select_array($columns)->from('users')->execute();
If you want to alias columns, use arrays instead of strings
$result = DB::select(array('name','the_name'))->from('users')->execute();
To select distinct values set distinct to true:
$users = DB::select('name')->from('users')->distinct(true)->execute();
print_r($users->as_array());
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 example how to influence this
behaviour.
$result = DB::select('id','name')->from('users')->as_assoc()->execute();
$result = DB::select('id','name')->from('users')->as_object()->execute();
$result = DB::select()->from('users')->as_object('Model_Users')->execute();
$result = DB::select()->from('users')->as_object('\\Users\\Model_Users')->execute();
When you pass a class name to as_object(), make sure you include any namespace if needed. If the class
specified does not exist, as_object() will be ignored and an indexed array will be returned.
Want to know how many records you have fetched? It's dead simple!
$result = DB::select('*')->from('users')->execute();
$num_rows = count($result);
To access these results you either loop through the result object directly, or get the result array.
$result = DB::select()->from('users')->execute();
foreach($result as $item)
{
}
$result_array = $result->as_array();
foreach($result_array as $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)
{
}
$key_value = $result->as_array('id', 'email');
foreach($key_value as $id => $email)
{
}
In order to set the conditions on our queries we can set WHERE conditions.
These examples also apply to updating and deleting.
$result = DB::select()->from('users')->where('id', 1)->execute();
To influence the operator supply it like so:
$result = DB::select()->from('users')->where('id', '=', 1)->execute();
$id_array = array(1,2,3);
$result = DB::select()->from('users')->where('id', 'in', $id_array)->execute();
$result = DB::select()->from('users')->where('id', 'between', array(1, 2))->execute();
$result = DB::select()->from('users')->where('id', '!=', 1)->execute();
$who = "john%";
$result = DB::select()->from('users')->where('name', 'like', $who)->execute();
Grouped where statements are also supported:
$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:
$users = DB::select()->from('users')->where('id', 'between', array(1, 10))->execute();
$users = DB::select()->from('users')->where('name', 'in', array('john', 'simon', 'dirk'))->execute();
For sorting data we use the order_by function.
DB::select()->from('users')->order_by('name','asc');
DB::select()->from('users')->order_by('name','asc')->order_by('surname', 'desc');
DB::select()->from('users')->order_by('name');
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.
DB::select()->from('users')->limit(1);
DB::select()->from('users')->limit(10)->offset(5);
DB::select()->from('users')->order_by('id','asc')->limit(10);
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.
$result = DB::update('users')
->value("name", "John Random")
->where('id', '=', '2')
->execute();
To update multiple columns.
$result = DB::update('users')
->set(array(
'name' => "Peter Griffon",
'email' => "peter@thehindenpeter.com"
))
->where('id', '=', '16')
->execute();
For inserting data we use DB::insert.
If successfully executed an insert query will return an array containing a
list of insert id and rows created.
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 separately
list($insert_id, $rows_affected) = DB::insert('users')->columns(array(
'name', 'email', 'password'
))->values(array(
'John Random', 'john@example.com', 's0_s3cr3t'
))->execute();
To delete records, use DB::delete.
When executed it will return the number of rows affected.
$result = DB::delete('users')->execute();
$result = DB::delete('users')->where('email', 'like', '%@example.com')->execute();
When selecting data, you can also join other tables into the result.
$result = DB::select()->from('users')->join('roles','LEFT')->on('roles.id', '=', 'users.role_id');
$result = DB::select()->from('users')->join('roles','right outer')->on('roles.id', '=', 'users.role_id');
$result = DB::select()->from('users')->join('roles','right outer')->on('roles.id', '=', DB::expr('1'));
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 constants (like DEFAULT).
DB::update('users')->where('id', '=', 1)->set(array(
'some_column' => DB::expr('DEFAULT'),
))->execute();
$result = DB::select(DB::expr('COUNT(*) as count'))->from('users')->execute();
$result_arr = $result->current();
$count = $result_arr['count'];
The query builder supports query binding, a technique that allows you to assign variables to your
hand-written queries in a safe and secure manner.
Query binding works by placing uniquely identifyable placeholders in your SQL. The query builder will
replace these placeholders by their corresponding values when the query is compiled for execution (so not
when you bind it!).
You define placeholder using the FuelPHP standard notation, which is a string, prefixed by a colon
(:varname).
$name = 'John';
$query = "SELECT * FROM users WHERE username = :name";
$result = DB::query($query)->bind('name', $name)->execute();
Variables are bound by reference, so you can define your query and your bindings early in your code
and modify the bound variable later.
$query = DB::query("SELECT * FROM users WHERE username = :name");
$name = 'unknown';
$query->bind('name', $name);
$name = 'Sally';
$result = $query->execute();
Because the variable is bound by reference, you can NOT bind a literal. If you
do, you'll get a "Cannot pass parameter 2 by reference" exception!
$query = DB::query("SELECT * FROM users WHERE username = :name")->bind('name', 'value')->execute();
You can assign by value using the param() method:
$query = DB::query("SELECT * FROM users WHERE username = :name")->param('name', 'value')->execute();
And finally, you can use the parameters() method if you want to mix the two:
$query = DB::query("SELECT * FROM users WHERE username = :name AND state = :state");
$name = 'John';
$query->parameters(array('name' => &$name, 'state' => 'new'));
$result = $query->execute();
Some frameworks use question marks as placeholders. This works for them because their
binding system is positional, the first question mark gets replaced by the first variable bound, and
so on. In FuelPHP, order of binding is not relevant, there is a one-to-one link between the placeholder
and the variable bound. It also means you can use the same placeholder multiple times in a single
query.
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 3 arguments: the expiration time (cache validity in seconds)
a second argument to set a custom key for the query (by default a md5 hash of the SQL), and a boolean
that can be used to indicate that you don't want to cache empty results. Using a custom cache key
will allow you to manually delete specific queries more easily and group sets of query caches into
specific directories.
$query = DB::query("SELECT * FROM users")->cached(3600)->execute();
$query = DB::query("SELECT * FROM users")->cached(3600, "foo.bar", false)->execute();
Cache::delete("foo.bar");
Cache::delete_all("foo");
Cache::delete_all("db");
