Fork me on GitHub

Dbal

composer require nextras/dbal

Nextras\Dbal

Connection

The connection instance is the main object which provides API for working with your database. Connection's constructor accepts a configuration array. The possible keys depend on the current driver, although some configuration keys are the same for all drivers:

driver driver name, use mysqli or pgsql
host database server name
username username for authentication
password password for authentication
database name of database
charset charset encoding of connection
applicationTz time zone for returned DateTime objects
connectionTz time zone for connection
simpleStorageTz time zone for simple time stamp type
$connection = new Nextras\Dbal\Connection([
    'driver'   => 'mysqli',
    'host'     => 'localhost',
    'username' => 'root',
    'password' => '****',
    'database' => 'test',
]);

By default, the connection is lazy; it connects to database when needed. You can force the connection by calling connect() method; you can also disconnect() or reconnect() your connection. Use ping() method to stay in touch.


Querying

Use query() method to run SQL queries. Query method accepts “pseudo SQL” statement. Dbal support parameter placeholders, which are passed separately and will be properly escaped and sanitized. Take a look on Parameter Placeholders chapter.

$connection->query('SELECT * FROM foo WHERE id = %i', 1);
// SELECT * FROM foo WHERE id = 1

$connection->query('SELECT * FROM foo WHERE title = %s', 'foo" OR 1=1');
// SELECT * FROM foo WHERE title = "foo\" OR 1=1"

“Pseudo SQL” supports [] square brackets for easily escaping of column names. However, if you pass a column name as parameter retrieved from user, use the proper %column placeholder.

$connection->query('SELECT * FROM [foo] WHERE %column = %i', 'id, 1);
// SELECT * FROM `foo` WHERE `id` = 1

To retrieve the last inserted id use getLastInsertedId() method. For PostgreSQL, the method accepts a sequence name. The number of affected rows is available through getAffectedRows() method.

Each query() returns a new Nextras\Dbal\Result\Result object. Result object allows you to iterate over the fetched data and fetch each row into Nextras\Dbal\Result\Row object:

$users = $connection->query('SELECT * FROM [users]');
foreach ($users as $row) {
    $row->name;
}

Result object implements SeekableIterator. You can use fetch() method to fetch a row, fetchField() to fetch the first field form the first row, or fetchAll() to return array of rows' objects.

$maximum = $connection->query('SELECT MAX([age]) FROM [users]')->fetchField();

Transactions

The connection object provides convenient API for working with transactions. You can easily beginTransaction(), commitTransaction() and rollbackTransaction(). Usually, you need to react to an exception by calling rollback method. For such use case there is a transactional() method that make its callback atomic.

$connection->transactional(function(Connection $connection) {
    $connection->query('INSERT INTO users %values', [
        'name' => 'new user'
    ]);
    $connection->query('INSERT INTO urls %values', [
        'url' => 'new-user',
        'user_id' => $connection->getLastInsertedId();
    ]);
});