dendude/awesome-php-pdo-wrapper

Simple PDO wrapper

dev-master 2018-08-05 19:08 UTC

MIT

README

Useful wrapper for PDO using >=php7.1

Database\DB class realizes a singleton/multiton desing pattern.

Table example

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', '[e-mail]'),
(2, 'user', 'Barbara Johnson', '[e-mail]'),
(3, 'manager', 'Mary Lee', '[e-mail]'),
(4, 'user', 'Lucia Woods', '[e-mail]'),
(5, 'user', 'Brandon Alister', '[e-mail]');

Get instance

use Database\DB;
use Database\DBExpression;

$db = DB::getInstanse(); // default db
$db2 = DB::getInstanse(DB::CONFIG_OTHER); // another db 

Select

// 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'
]);

Select row

$row = $db->selectRow('users', ['email' => '[e-mail]']);

Select row by id

$row = $db->selectRowById('users', 3);

Select column

$emails = $db->selectColumn('users', 'email');

Select cell

$email = $db->selectCell('users', 'email', ['id' => 3]);

Select count

$count = $db->selectCount('users');

Insert data

// simple insert
$id = $db->insert('users', ['role' => 'user', 'name' => 'new name', 'email' => '[e-mail]']);

// insert with mysql expression
$id = $db->insert('users', [
    'role' => 'user', 
    'name' => 'new name', 
    'email' => '[e-mail]', 
    'created' => new DBExpression('NULL') // or 'created' => null
]);

Update data

// 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]);

Update counter fields

$affected = $db->updateCounters('users', ['visits' => 1], ['id' => 3]);

Check existing record

$isExist = $db->exists('users', ['role' => 'admin']);

Delete rows

$db->delete('users', ['visits' => 0]);

Close connections of the all instances

DB::closeConections();