v-dem / queasy-db
Database access classes, part of QuEasy PHP framework
Requires
- php: >=5.3.0|>=7.0.0|>=8.0.0
- ext-pdo: *
- psr/log: ~1.1
- v-dem/queasy-helper: dev-master
Requires (Dev)
- php: >=7.1.0
- ext-sqlite3: *
- ext-xdebug: *
- phpunit/phpunit: ~10
Suggests
- queasy/config: Configuration provider package, supports PHP (and multifile configs in this case), INI, XML and JSON (and YAML in future) formats
- queasy/log: PSR-3 compatible logger package, supports file system log writer, console and email (using mail() function)
This package is auto-updated.
Last update: 2023-03-24 17:43:06 UTC
README
QuEasy PHP Framework - Database
Package v-dem/queasy-db
Database access classes. Some the most usual queries can be built automatically, more complex queries can be added into database and/or tables config.
Features
Requirements
- PHP version 5.3 or higher
Installation
composer require v-dem/queasy-db:master-dev
It will also install v-dem/queasy-helper
.
Usage
Notes
queasy\db\Db
class inheritsPDO
class, so anyPDO
methods can be called with it- You can use
setLogger()
method which acceptsPsr\Log\LoggerInterface
to log all queries
Initialization
Sample:
$db = new queasy\db\Db( [ 'connection' => [ 'driver' => 'mysql', 'host' => 'localhost', 'name' => 'test', 'user' => 'test_user', 'password' => 'test_password' ], 'options' => [ // Optional. Driver options 'fetchMode' => PDO::FETCH_OBJ // Default is PDO::FETCH_ASSOC ] ] );
Or
$db = new queasy\db\Db( [ 'connection' => [ 'dsn' => 'mysql:host=localhost;dbname=test', 'user' => 'test_user', 'password' => 'test_password' ] ] );
Or PDO-way:
$db = new queasy\db\Db('mysql:host=localhost;dbname=test', 'test_user', 'test_password');
- By default error mode is set to
PDO::ERRMODE_EXCEPTION
Get all records from users
table
$users = $db->users->all();
Resulting SQL:
SELECT * FROM `users`
Get a single record from users
table by id
key
$user = $db->users->id[$userId];
Resulting SQL:
SELECT * FROM `users` WHERE `id` = :id
It's possible to use select()
method to pass PDO options; select()
returns array of rows:
$users = $db->users->id->select($userId, $options);
Get multiple records
$users = $db->users->id[[$userId1, $userId2]];
Resulting SQL:
SELECT * FROM `users` WHERE `id` IN (:id_1, :id_2)
Insert a record into users
table using associative array
$db->users[] = [ 'email' => 'john.doe@example.com', 'password_hash' => sha1('myverystrongpassword') ];
Resulting SQL:
INSERT INTO `users` (`email`, `password_hash`) VALUES (:email, :password_hash)
Insert a record into users
table by fields order
$db->users[] = [ 'john.doe@example.com', sha1('myverystrongpassword') ];
Insert many records into users
table using associative array (it will generate single INSERT
statement)
$db->users[] = [ [ 'email' => 'john.doe@example.com', 'password_hash' => sha1('myverystrongpassword') ], [ 'email' => 'mary.joe@example.com', 'password_hash' => sha1('herverystrongpassword') ] ];
Resulting SQL:
INSERT INTO `users` (`email`, `password_hash`) VALUES (:email_1, :password_hash_1), (:email_2, :password_hash_2)
Insert many records into users
table by order
$db->users[] = [ [ 'john.doe@example.com', sha1('myverystrongpassword') ], [ 'mary.joe@example.com', sha1('herverystrongpassword') ] ];
Inserting many records into users
table with field names denoted separately
$db->users[] = [ [ 'email', 'password_hash' ], [ [ 'john.doe@example.com', sha1('myverystrongpassword') ], [ 'mary.joe@example.com', sha1('herverystrongpassword') ] ] ];
Also it's possible to use insert()
method (in the same way as above) when need to pass PDO options:
$db->users->insert([ 'email' => 'john.doe@example.com', 'password_hash' => sha1('myverystrongpassword') ], $options);
Get last insert id (alias of lastInsertId()
method)
$newUserId = $db->id();
Update a record in users
table by id
key
$db->users->id[$userId] = [ 'password_hash' => sha1('mynewverystrongpassword') ]
Update multiple records
$db->users->id[[$userId1, $userId2]] = [ 'is_blocked' => true ]
Delete a record in users
table by id
key
unset($db->users->id[$userId]);
Delete multiple records
unset($db->users->id[[$userId1, $userId2]]);
Get count of all records in users
table
$usersCount = count($db->users);
Using transactions
$db->trans(function(queasy\db\Db $db) use(...) { // Run queries inside a transaction });
queasy\db\Db
instance will be passed as first argument.
Using foreach
with a users
table
foreach ($db->users as $user) { // Do something }
Run custom query (returns PDOStatement
)
$result = $db->run(' SELECT * FROM `users` WHERE `name` LIKE concat(\'%\', :searchName, \'%\')', [ ':searchName' => $searchName ] );
- Possible 3rd argument is
$driverOptions
which will be passed toPDO::prepare()
Run query predefined in configuration
This feature can help keep code cleaner and place SQL code outside PHP, somewhere in config files.
$db = new queasy\db\Db( [ 'connection' => [ 'driver' => 'mysql', 'host' => 'localhost', 'name' => 'test', 'user' => 'test_user', 'password' => 'test_password' ], 'queries' => [ 'getActiveUserByName' => [ 'sql' => ' SELECT * FROM `user_roles` WHERE `name` = :name AND `is_active` = 1', 'returns' => Db::RETURN_ONE ] ] ] ); $user = $db->getActiveUserByName([ 'name' => 'John Doe' ]);
- Possible values for
returns
option areDb::RETURN_STATEMENT
(default, returnsPDOStatement
instance),Db::RETURN_ONE
,Db::RETURN_ALL
,Db::RETURN_VALUE
Also it is possible to group predefined queries by tables:
$db = new queasy\db\Db( [ 'connection' => [ 'driver' => 'mysql', 'host' => 'localhost', 'name' => 'test', 'user' => 'test_user', 'password' => 'test_password' ], 'tables' => [ 'users' => [ 'getActiveByName' => [ 'sql' => ' SELECT * FROM `user_roles` WHERE `name` = :name AND `is_active` = 1', 'returns' => Db::RETURN_ONE ] ] ] ] ); $user = $db->users->getActiveByName([ 'name' => 'John Doe' ]);
Using v-dem/queasy-db
together with v-dem/queasy-config
and v-dem/queasy-log
config.php:
return [ 'db' => [ 'connection' => [ 'driver' => 'mysql', 'host' => 'localhost', 'name' => 'test', 'user' => 'test_user', 'password' => 'test_password' ], 'tables' => [ 'users' => [ 'getActiveByName' => [ 'sql' => ' SELECT * FROM `users` WHERE `name` = :name AND `is_active` = 1', 'returns' => Db::RETURN_ONE ] ] ] ], 'logger' => [ [ 'class' => queasy\log\ConsoleLogger::class, 'minLevel' => Psr\Log\LogLevel::DEBUG ] ] ];
$config = new queasy\config\Config('config.php'); // Can be also INI, JSON or XML $logger = new queasy\log\Logger($config->logger); $db = new queasy\db\Db($config->db); $db->setLogger($logger); $user = $db->users->getActiveByName([ 'name' => 'John Doe' ]);
- All queries will be logged with
Psr\Log\LogLevel::DEBUG
level. Also it's possible to use any other logger class compatible with PSR-3.