Useful wrapper for PDO using >=php7.1
Database\DB class realizes a singleton/multiton desing pattern.
CREATE TABLE IF NOT EXISTS `users` (
`id` int(10) unsigned NOT NULL auto_increment,
`role` varchar(50) NOT NULL,
`name` varchar(50) NOT NULL,
`email` varchar(50) NOT NULL,
`visits` int(10) unsigned NOT NULL DEFAULT 0,
`created` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
);
INSERT INTO `users` (`id`, `role`, `name`, `email`) VALUES
(1, 'admin', 'John Smith', 'js@gmail.com'),
(2, 'user', 'Barbara Johnson', 'bj@gmail.com'),
(3, 'manager', 'Mary Lee', 'mee@gmail.com'),
(4, 'user', 'Lucia Woods', 'uw@gmail.com'),
(5, 'user', 'Brandon Alister', 'ba@gmail.com');
use Database\DB;
use Database\DBExpression;
$db = DB::getInstanse(); // default db
$db2 = DB::getInstanse(DB::CONFIG_OTHER); // another db
// simple select
$rows = $db->selectAll('users', ['role' => 'admin']);
// select with NULL option
$rows = $db->selectAll('users', [
'role' => 'admin',
'created' => 'IS NOT NULL' // or 'created' => 'IS NULL'
]);
$row = $db->selectRow('users', ['email' => 'admin@gmail.com']);
$row = $db->selectRowById('users', 3);
$emails = $db->selectColumn('users', 'email');
$email = $db->selectCell('users', 'email', ['id' => 3]);
$count = $db->selectCount('users');
// simple insert
$id = $db->insert('users', ['role' => 'user', 'name' => 'new name', 'email' => 'new@gmail.com']);
// insert with mysql expression
$id = $db->insert('users', [
'role' => 'user',
'name' => 'new name',
'email' => 'new@gmail.com',
'created' => new DBExpression('NULL') // or 'created' => null
]);
// simple update
$affected = $db->update('users', ['role' => 'manager'], ['id' => 2]);
// update with mysql expression
$affected = $db->update('users', [
'role' => 'manager',
'created' => new DBExpression('NOW()')
], ['id' => 2]);
$affected = $db->updateCounters('users', ['visits' => 1], ['id' => 3]);
$isExist = $db->exists('users', ['role' => 'admin']);
$db->delete('users', ['visits' => 0]);
DB::closeConections();