Databases

Description

The database class provides a simple way of handling database connections in your application.

Methods

connection([string $name = null])

The connection method returns a connection object based on the configuration you choose to load.

// Returns connection object using the "default" database configuration defined in the config file

$connection = Database::connection();

// Returns connection object using the "mydb" database configuration defined in the config file

$connection = Database::connection('mydb');

query(string $query [, array array $params = array() [, int $fetch = Database::FETCH_ALL]])

The query method executes a query and returns data for SELECT queries, number of rows affected for DELETE and UPDATE queries and a boolean value for other queries.

The different fetch modes are FETCH_ALL, FETCH_FIRST and FETCH_COLUMN.

$connection->query('INSERT INTO `foo` (`bar`, `baz`) VALUES (?, ?)', array('fruit', 'banana'));

// You can also use the following syntax to perform a query against the default connection

Database::query('INSERT INTO `foo` (`bar`, `baz`) VALUES (?, ?)', array('fruit', 'banana'));

// There's also a handy syntax for assigning arrays for use in "IN" clauses

Database::query('SELECT * FROM `foo` WHERE `bar` IN ([?])', array(array('banana', 'apple', 'orange')));

all(string $query [, array array $params = array()])

The all method executes a query and returns an array containing all of the result set rows.

$rows = $connection->all('SELECT * from `foo` WHERE `bar` = ?', array($bar));

// You can also use the following syntax to perform a query against the default connection

$rows = Database::all('SELECT * from `foo` WHERE `bar` = ?', array($bar));

first(string $query [, array array $params = array()])

The first method executes a query and returns the first row of the result set.

$row = $connection->first('SELECT * from `foo` WHERE `bar` = ?', array($bar));

// You can also use the following syntax to perform a query against the default connection

$row = Database::first('SELECT * from `foo` WHERE `bar` = ?', array($bar));

column(string $query [, array array $params = array()])

The column method executes a query and returns the value of the first column of the first row of the result set.

$email = $connection->column('SELECT `email` from `users` WHERE `id` = ?', array(1));

// You can also use the following syntax to perform a query against the default connection

$email = Database::column('SELECT `email` from `users` WHERE `id` = ?', array(1));

table(string $table)

The table method returns an instance of the query builder.

$rows = $connection->table('foo')->where('bar', '=', $bar)->all();

// You can also use the following syntax to perform a query against the default connection

$rows = Database::table('foo')->where('bar', '=', $bar)->all();

transaction(closure $queries)

The transaction method provides a handy shortcut for performing database transactions. It returns TRUE if the transaction was successful and FALSE if it failed.

Transactions only work if the storage engine you're using supports them.

$success = $connection->transaction(function($connection)
{
	$connection->table('accounts')->where('user_id', '=', 10)->decrement('cash', 100);

	$connection->table('accounts')->where('user_id', '=', 20)->increment('cash', 100);
});

// You can also use the following syntax to perform a query against the default connection

$success = Database::transaction(function($connection)
{
	$connection->table('accounts')->where('user_id', '=', 10)->decrement('cash', 100);

	$connection->table('accounts')->where('user_id', '=', 20)->increment('cash', 100);
});

PDO

You can also access the PDO object directly when needed.

try
{
	$connection->pdo->beginTransaction();
	
	$connection->query('DROP TABLE `foo`');

	$connection->query('INSERT INTO `foo` (`bar`, `baz`) VALUES (?, ?)', array('fruit', 'banana'));

	$connection->pdo->commit();
}
catch(PDOException $e)
{
	$connection->pdo->rollBack();
}