Documentation

Query results

A database query always returns the result in an iterator object.
There are two types of result object available, each with their pros and cons.

• Database_Result
• Database_Cached

while ($row = $pdoResult->fetch())
{
	// do something with $row
}

$rows = $pdoResult->fetchAll(PDO::FETCH_NUM); // or FETCH_ASSOC, depending on your db config
foreach ($rows as $row)
{
	// do something with $row
}

// this returns a Database_Result, independent of the global setting
$result = \DB::select()->from('table')->caching(false)->execute();

// this returns a Database_Cached, independent of the global setting
$result = \DB::select()->from('table')->caching(true)->execute();

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_xxx_Result or Database_xxx_Cached object
$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`
$columns = 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 example 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();

// Will fetch the result as an Model_Users object (a model from the Users module).
$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();
// Just count the results, it returns an int.
$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)
{
	// 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 its $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('name', '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()->from('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 successfully 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 separately

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

// join on a literal value 1 instead of a column name
$result = DB::select()->from('users')->join('roles','right outer')->on('roles.id', '=', DB::expr('1'));

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 constants (like DEFAULT).

// Set a field to its default
DB::update('users')->where('id', '=', 1)->set(array(
	'some_column' => DB::expr('DEFAULT'),
))->execute();

// SELECT COUNT(*) as 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 binding

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'; // set the variable we're going to bind
$query = "SELECT * FROM users WHERE username = :name"; // our query

// bind the variable and run the query, produces SELECT * FROM users WHERE username = 'John'
$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.

// create the query object
$query = DB::query("SELECT * FROM users WHERE username = :name");

$name = 'unknown';                // set the variable with a default value
$query->bind('name', $name);     // and bind it to the query

// after some code, change the bound variable
$name = 'Sally';

// bind the variable and run the query, produces SELECT * FROM users WHERE username = '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!

// this will generate an exception!
$query = DB::query("SELECT * FROM users WHERE username = :name")->bind('name', 'value')->execute();

You can assign by value using the param() method:

// this will work though
$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:

// create the query object
$query = DB::query("SELECT * FROM users WHERE username = :name AND state = :state");

$name = 'John'; // set the variable we're going to bind

// bind the variable and literal
$query->parameters(array('name' => &$name, 'state' => 'new'));

// and run the query, produces SELECT * FROM users WHERE username = 'John' AND 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.

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 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.

// 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.
// Empty query results will not be cached.
$query = DB::query("SELECT * FROM users")->cached(3600, "foo.bar", false)->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");