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_Builder object. The exact object depends on the type passed. If no type was passed, Fuel chooses DB::SELECT if the SQL query begins with 'SELECT', and it will return a Database_Query_Builder_Select object.

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

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_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;
}