DB Class

The DB class allows you to build and execute database queries and fetch the result.

query($sql, $type = null)

The query method returns a new Database_Query object. A Database_Query object can be used to execute a SQL query, or to bind variables to the query.

The first parameter is a SQL string which can contain placeholders. See Query binding.

The result of the execution depends on the second parameter. If no type was passed, Fuel tries to detect the type automatically. For example, Fuel chooses DB::SELECT if the SQL query begins with 'SELECT', and the result of the execution will be fetched rows. Same is true for the SQL query begins with 'INSERT', 'UPDATE' or 'DELETE'. In each case, the result will be the same as when the query was done by Query Builder. See Inserting, Updating, and Deleting.

Static Yes
Parameters
Param Default Description
$query required SQL query
$type null SQL query type. Possible types are: DB::SELECT, DB::INSERT, DB::UPDATE and DB::DELETE.
Returns Returns a Database_Query object of the requested type.
Example 
// Will prepare: SELECT * FROM `users`
$query = DB::query('SELECT * FROM `users`');

Note that it is important to pass the correct type. The database driver will treat them differently. If you have a query that must return a resultset, but is not a SELECT, use DB::SELECT to make sure the result is returned correctly.

instance()

The instance method returns an instance of Database_Connection. It allows you to directly access a database instance, for example when you have a need to use multiple database connections at the same time.

Static Yes
Parameters
Param Default Description
$db null The database connection name.
Returns an instance of Database_Connection.
Throws FuelException, if the connection name passed is not defined in your database configuration.
Example 
// get the default connection
$db = DB::instance();

// get a named connection
$db = DB::instance('backup');

last_query()

The last_query method returns the last executed SQL query.

Static Yes
Parameters
Param Default Description
$db null The database connection.
Returns The last executed SQL query.
Example 
// execute a query
$user = DB::select()->from('users')->where('id', 1)->execute();

echo DB::last_query();
// SELECT * FROM `users` WHERE `id` = 1

select()

The select method returns a new Database_Query_Builder_Select object.

Static Yes
Parameters
Param Default Description
$columns null Columns to select.
Returns Returns a Database_Query_Builder_Select object.
Example 
// Will prepare: SELECT *
$query = DB::select();

// Will prepare: SELECT `id`, `name`
$query = DB::select('id', 'name');

select_array($columns)

The select_array method returns a new Database_Query_Builder_Select object.

Static Yes
Parameters
Param Default Description
$columns array(); Array of columns to select.
Returns Returns a Database_Query_Builder_Select object.
Example 
// Will prepare: SELECT *
$query = DB::select_array();

// Will prepare: SELECT `id`, `name`
$query = DB::select_array(array('id', 'name'));

insert($table = null, $columns = array())

The insert method returns a new Database_Query_Builder_Insert object.

Static Yes
Parameters
Param Default Description
$table null The table to insert into.
$columns array(); Array of columns to insert.
Returns Returns a Database_Query_Builder_Insert object.
Example 
// Will prepare: INSERT INTO `table_name`
$query = DB::insert('table_name');

// Will prepare: INSERT INTO `table_name` (`id`, `name`)
$query = DB::insert('table_name', array('id', 'name'));

update($table = null)

The update method returns a new Database_Query_Builder_Update object.

Static Yes
Parameters
Param Default Description
$table null Table to update.
Returns Returns a Database_Query_Builder_Update object.
Example 
// Will prepare: UPDATE `table_name`
$query = DB::update('table_name');

delete($table = null)

The delete method returns a new Database_Query_Builder_Delete object.

Static Yes
Parameters
Param Default Description
$table null The table to delete from.
Returns Returns a Database_Query_Builder_Delete object.
Example 
// Will prepare: DELETE FROM `table_name`
$query = DB::delete('table_name');

expr($expression)

The expr method returns a new Database_Expression object.

Static Yes
Parameters
Param Default Description
$expression null The expression.
Returns Returns a Database_Expression object.
Example 
// returns new Database_Expression('COUNT(table_name.table_column)');
$expr = DB::expr('COUNT(table_name.table_column)');

// prevents incorrect quoting of this expression in the generated query
$expr = DB::expr('columnname + 1');

You have to use DB:expr() in your query for all expressions, i.e. for everything that is not a fieldname, to make sure the value you pass isn't enclosed in quotes or backticks.

quote($string, $db = null)

The quote method returns a quoted string for an SQL query.

Static Yes
Parameters
Param Default Description
$string required The string to quote
$db null The database connection.
Returns Returns a Database_Expression object.
Example 
// returns 'something\'s quoted'.
$query = DB::quote("'something's quoted'");

// returns 'something\'s quoted' through a defined database connection.
$query = DB::quote("'something's quoted'", $db_connection);

quote_identifier($string, $db)

The quote_identifier method returns a quoted string for an SQL query.

Static Yes
Parameters
Param Default Description
$string required The string, or array of strings, to quote.
$db null The database connection.
Returns Returns a quoted string for an SQL query.
Example 
// `users`.`name`
print_r(DB::quote_identifier('users.name'));

// array(
//    [0] => `users`.`name`
// )
print_r(DB::quote_identifier(array('users.name')));

quote_table($string, $db = null)

The quote_table method returns a quoted string for an SQL query.

Static Yes
Parameters
Param Default Description
$string required The string, or array of strings, to quote.
$db null The database connection.
Returns Returns a quoted string for an SQL query.
Example 
// `users`
print_r(DB::quote_table('users'));

// array(
//    [0] => `users`
// )
print_r(DB::quote_identifier(array('users')));

table_prefix($table, $db = null)

The table_prefix method returns the table name with the configured prefix. If not, then just the prefix is returned.

Static Yes
Parameters
Param Default Description
$table required The table name to prefix.
$db null The database connection.
Returns Returns the prefixed table name or the table name.
Example 
// prefixed_table_name
print_r(DB::table_prefix('table_name'));

escape($string, $db = null)

The escape method returns a escaped string for an SQL query.

Static Yes
Parameters
Param Default Description
$string required The string to escape.
$db null The database connection.
Returns Returns an escaped string for an SQL query.
Example 
// 'or *\' \"'
print_r(DB::escape('or *\' "'));

list_columns($table, $like = null, $db = null)

The list_columns method return a lists all of the columns in a table. Optionally, a LIKE string can be used to search for specific fields.

Static Yes
Parameters
Param Default Description
$table null The table to look in.
$like null The column to search for.
$db null The database connection.
Returns Returns an array with field data.
Example 
/*
Array
(
	[id] => Array
	(
		[type] => int
		[min] => -2147483648
		[max] => 2147483647
		[column_name] => id
		[column_default] =>
		[data_type] => int
		[is_nullable] =>
		[ordinal_position] => 1
		[display] => 255
		[comment] =>
		[extra] => auto_increment
		[key] => PRI
		[privileges] => select,insert,update,references
	)

	[name] => Array
	(
		[type] => string
		[column_name] => name
		[column_default] =>
		[data_type] => varchar
		[is_nullable] =>
		[ordinal_position] => 3
		[character_maximum_length] => 255
		[collation_name] => utf8_unicode_ci
		[comment] =>
		[extra] =>
		[key] =>
		[privileges] => select,insert,update,references
	)
)
*/
print_r(DB::list_columns('users'));

/*
Array
(
	[name] => Array
	(
		[type] => string
		[column_name] => name
		[column_default] =>
		[data_type] => varchar
		[is_nullable] =>
		[ordinal_position] => 3
		[character_maximum_length] => 255
		[collation_name] => utf8_unicode_ci
		[comment] =>
		[extra] =>
		[key] =>
		[privileges] => select,insert,update,references
	)
)
*/
print_r(DB::list_columns('users','%name%'));

list_indexes($table, $like = null, $db = null)

The list_indexes method return a lists all of the indexes on a table. Optionally, a LIKE string can be used to search for specific index names.

Static Yes
Parameters
Param Default Description
$table null The table to look in.
$like null The indexes to search for.
$db null The database connection.
Returns Returns an array with field data.
Example 
/*
Array
(
    [name] => PRIMARY
    [column] => id
    [order] => 1
    [type] => BTREE
    [primary] => true
    [unique] => true
    [null] => false
    [ascending] => true
)
Array
(
    [name] => TypeValue
    [column] => type
    [order] => 1
    [type] => BTREE
    [primary] => false
    [unique] => true
    [null] => false
    [ascending] => true
)
Array
(
    [name] => TypeValue
    [column] => value
    [order] => 2
    [type] => BTREE
    [primary] => false
    [unique] => true
    [null] => false
    [ascending] => true
)

// here, TypeValue is a compound index with two columns, the order indicates
// the sequence in which the columns are defined in the index

*/
print_r(DB::list_columns('table'));

/*
Array
(
    [name] => TypeValue
    [column] => type
    [order] => 1
    [type] => BTREE
    [primary] =>
    [unique] => 1
    [null] =>
    [ascending] => 1
)
Array
(
    [name] => TypeValue
    [column] => value
    [order] => 2
    [type] => BTREE
    [primary] =>
    [unique] => 1
    [null] =>
    [ascending] => 1
)
*/
print_r(DB::list_columns('table','%Value%'));

list_tables($like = null, $db = null)

The list_tables method return a lists all of the tables in a database. Optionally, a LIKE string can be used to search for specific tables.

Static Yes
Parameters
Param Default Description
$like null The table to search for.
$db null The database connection.
Returns Returns an array with table names.
Example 
/*
Array
(
    [0] => areas
    [1] => fuel_sessions
    [2] => config
    [3] => files
)
*/
DB::list_tables();

/*
Array
(
    [0] => fuel_sessions

)
*/
DB::list_tables('%sessions%');

datatype($type, $db = null)

The datatype method returns a normalized array describing the SQL data type.

Static Yes
Parameters
Param Default Description
$type required The SQL data type.
$db null The database connection.
Returns Returns a normalized array describing the SQL data type.
Example 
/*
Array
(
    [type] => string
    [exact] => 1
)
*/
DB::datatype('char');

count_records($table, $db = null)

The count_records method returns the number of records in a table.

Static Yes
Parameters
Param Default Description
$table required The table to count records from.
$db null The database connection.
Returns Returns the number of records in a table.
Example 
// (int) 14
DB::count_records('users');

count_last_query($db = null)

The count_last_query method returns the number of records in the last query, without LIMIT or OFFSET applied.

Static Yes
Parameters
Param Default Description
$db null The database connection.
Returns Returns the number of records in the last query, without LIMIT or OFFSET applied.
Example 
// SELECT * FROM `users` WHERE `active` = "yes" LIMIT 10;
$limited_result = DB::select()->from('users')->where('active', '=', 'yes')->limit(10)->execute();

// SELECT count(*) as count FROM `users` WHERE `active` = "yes";
DB::count_last_query();

set_charset($charset, $db)

The set_charset method sets the connection character set. This is called automatically by [static::connect].

Static Yes
Parameters
Param Default Description
$charset required The character set name.
$db null The database connection.
Returns void
Example 
DB::set_charset('utf8');

error_info()

The error_info returns an array with information about the last database error.

Static Yes
Parameters None
Returns array($unified_code, $platform_code, $error_text)
Example 
$lasterror = DB::error_info();

in_transaction($db = null)

The in_transaction method checks whether a connection is in transaction.

Static Yes
Parameters
Param Default Description
$db null The database connection.
Returns bool - only meaningful with PDO driver. If in the transaction return true. Otherwise return false.
Example 
DB::in_transaction();

start_transaction($db = null)

The start_transaction method begins a transaction on an instance.

Static Yes
Parameters
Param Default Description
$db null The database connection.
Returns bool - only meaningful with PDO driver. All others always return true, but throw an Exception when the SQL command fails.
Example 
DB::start_transaction();

commit_transaction($db = null)

The commit_transaction method commits all pending transactional queries.

Static Yes
Parameters
Param Default Description
$db null The database connection.
Returns bool - only meaningful with PDO driver. All others always return true, but throw an Exception when the SQL command fails.
Example 
DB::commit_transaction();

rollback_transaction($db = null, $rollback_all = true)

The rollback_transaction method rolls back all pending transactional queries.

Static Yes
Parameters
Param Default Description
$db null The database connection.
$rollback_all true The rollback mode:
true - rollback everything and close transaction;
false - rollback only current level.
Returns bool - only meaningful with PDO driver. All others always return true, but throw an Exception when the SQL command fails.
Example 
DB::rollback_transaction();

Transaction example

// typical transaction code flow
try
{
    DB::start_transaction();

    // some query ...

    DB::commit_transaction();

    // return query result
}
catch (Exception $e)
{
    // rollback pending transactional queries
    DB::rollback_transaction();

    throw $e;
}