Database Abstraction Library for PHP with ActiveRecord features. It based on PDO extension.
- PHP 7.1.0+
- PDO driver
- Enabled extensions: pdo_pgsql and/or pdo_mysql
Installation via Composer is the recommended way to install. Add this line to your composer.json file:
"dawidgorecki/dbal": "~2.0"
or run
composer require dawidgorecki/dbal
use Reven\DBAL\Configuration\DBConfig;
use Reven\DBAL\Configuration\DSN;
$dsn = new DSN(DSN::DRIVER_PGSQL, 'my_db');
// $dsn = new DSN(DSN::DRIVER_MYSQL, 'my_db', 'localhost', DSN::PORT_MYSQL);
$config = new DBConfig($dsn, 'username', 'passwd');
// $config = new DBConfig($dsn, 'username', 'passwd', 'utf8', true);Using Connection Manager:
use Reven\DBAL\ConnectionManager;
use Reven\DBAL\DBALDatabase;
use Reven\DBAL\Exceptions\DBALException;
try {
ConnectionManager::createConnection($config);
// ConnectionManager::createConnection($config1, 'db1', PDO::FETCH_ASSOC);
// ConnectionManager::createConnection($config2, 'db2', PDO::FETCH_OBJ);
} catch (DBALException $e) {
die($e);
}
$dbal = new DBALDatabase(ConnectionManager::getConnection());
// $dbal = new DBALDatabase(ConnectionManager::getConnection('db1'));Using Database Factory:
use Reven\DBAL\DatabaseFactory;
use Reven\DBAL\DBALDatabase;
use Reven\DBAL\Exceptions\DBALException;
try {
$pdo = DatabaseFactory::getConnection($config, PDO::FETCH_ASSOC);
} catch (DBALException $e) {
die($e);
}
$dbal = new DBALDatabase($pdo);You can turn off all exceptions thrown by DBALDatabase by setting false as second parameter in object constructor.
$dbal = new DBALDatabase($pdo, false);Getting error message and query string:
$dbal->getLastError();
$dbal->getQueryString();Returns a PDO instance representing a connection to a database
$dbal->getPDO();Initiates a transaction (turns off autocommit mode)
$dbal->startTransaction();Commits a transaction, returning the database connection to autocommit mode
$dbal->commit();Rolls back the current transaction
$dbal->rollback();Returns all rows of the query result
$users = $dbal->fetchAll("SELECT * FROM users");
/*
Array
(
[0] => Array
(
[id] => 1
[name] => Dawid
[age] => 31
)
)
*/Returns first row of the query result
$user = $dbal->fetchFirst("SELECT * FROM users ORDER BY id", [], PDO::FETCH_ASSOC);
/*
Array
(
[id] => 1
[name] => Dawid
[age] => 31
)
*/Returns first row of the query result as numeric indexed array
$user = $dbal->fetchArray("SELECT * FROM users ORDER BY id");
/*
Array
(
[0] => 1
[1] => Dawid
[2] => 31
)
*/Returns first row of the query result as associative array
$user = $dbal->fetchAssoc("SELECT * FROM users WHERE name = ?", ["Dawid"]);
/*
Array
(
[id] => 1
[name] => Dawid
[age] => 31
)
*/Returns a single column from the first row of the query result
$user = $dbal->fetchColumn("SELECT * FROM users WHERE id = ?", [1], 1);
// DawidDeletes rows of a given table
$dbal->delete('users', ["id" => 1]);
$dbal->delete('users', ["name" => "Dawid"]);Inserts a row into the given table
$dbal->insert('users', ["name" => "John", "age" => 35]);Updates rows of a given table
$dbal->update('users', ["name" => "New John", "age" => 40], ["id" => 15]);Executes a prepared statement with the given SQL and parameters and returns PDOStatement instance
$stmt = $dbal->executeQuery("SELECT * FROM users");
while ($user = $stmt->fetchObject()) {
print_r($user);
}
/*
stdClass Object
(
[id] => 1
[name] => Dawid
[age] => 31
)
stdClass Object
(
[id] => 15
[name] => New John
[age] => 40
)
*/Executes a prepared statement with the given SQL and parameters and returns the affected rows count
$rows_affected = $dbal->updateQuery("DELETE FROM users WHERE name = ?", ["New John"]);Prepare a given SQL statement and return the PDOStatement instance
$stmt = $dbal->prepare("SELECT * FROM users WHERE name LIKE 'D%'");
$stmt->execute();
while ($user = $stmt->fetch(PDO::FETCH_NUM)) {
print_r($user);
}
/*
Array
(
[0] => 1
[1] => Dawid
[2] => 31
)
Array
(
[0] => 12
[1] => Dominik
[2] => 1
)
*/Quotes a string for use in a query
$quoted = $dbal->quote("Hello", PDO::PARAM_STR);Return ID of the last inserted row
$last_id = $dbal->lastId();Model private properties should have the same names as columns in database table.
Database Table- Plural with underscores separating words (e.g., user_details)Model Class- Singular with the first letter of each word capitalized (e.g., UserDetail)
Extend your model class with Reven\DBAL\ActiveRecord
<?php
namespace Reven\DBAL;
class User extends ActiveRecord
{
}Create database connection
ConnectionManager::createConnection($config, 'active_record');Set connection name if it's not default
User::setConnectionName('active_record');Change default database table (optional)
User::setTableName('employers');To create a new record in database (e.g. add new user) we instantiating a new object and then invoking the save() method.
// INSERT INTO users(name,email) VALUES('John','john@gmail.com')
$user = new User();
$user->setName("John");
$user->setEmail("john@gmail.com");
$user->save();These are your basic methods to find and retrieve records from your database.
// SELECT * FROM users WHERE id=1 LIMIT 1
$user = User::findById(1);
echo $user->getName();
// SELECT * FROM users
$users = User::findAll();
foreach ($users as $user) {
echo $user->getName();
}
// SELECT * FROM users WHERE email='john@gmail.com'
$users = User::findByQuery("SELECT * FROM users WHERE email=:email", [":email" => "john@gmail.com"]);
echo $users[0]->getName();To update you would just need to find a record first and then change one of attributes.
// UPDATE users SET name='Edwin' WHERE id=1
$user = User::findById(1);
$user->setName("Edwin");
$user->save();That will call SQL query to delete the record in your database.
// DELETE FROM users WHERE id=1
$user = User::findById(1);
$user->delete();Licensed under the MIT license. (http://opensource.org/licenses/MIT)