Db class -- Simple PDO wrapper with prepared statements
Initialization
Select Query
Insert Query
Insert Multiple Query
Update Query
Delete Query
Create database
Truncate table
Drop database
Drop table
This software is developed during my free time and I will be glad if somebody will support me.
Everyone's time should be valuable, so please consider donating.
- Import Db.class.php into your project, and require it:
require_once 'Db.class.php';or you can use autoload:
spl_autoload_extensions('.class.php'); spl_autoload_register();- Import the namespace to the file where the class is used:
use lib\Db;Simple initialization with utf8 charset set by default:
// simple way (you need to change params in the __constructor to yours at first): $db = new Db();Advanced initialization:
// Advanced initialization, whith params: $db = new Db('driver', 'host', 'username', 'password', 'databaseName', 'charset');You can add a prefix of your database:
$db->setPrefix ('my_');dbObject.php is an object mapping library built on top of Db to provide model representation functionality. See dbObject manual for more information
Simple example
$data = Array ("login" => "admin", "firstName" => "John", "lastName" => 'Doe' ); $id = $db->insert ('users', $data); if($id) echo 'user was created. Id=' . $id;Insert with functions use
$data = Array ( 'login' => 'admin', 'active' => true, 'firstName' => 'John', 'lastName' => 'Doe', 'password' => $db->func('SHA1(?)',Array ("secretpassword+salt")), // password = SHA1('secretpassword+salt') 'createdAt' => $db->now(), // createdAt = NOW() 'expires' => $db->now('+1Y') // expires = NOW() + interval 1 year // Supported intervals [s]econd, [m]inute, [h]hour, [d]day, [M]onth, [Y]ear ); $id = $db->insert ('users', $data); if ($id) echo 'user was created. Id=' . $id; else echo 'insert failed: ' . $db->getLastError();Insert with on duplicate key update
$data = Array ("login" => "admin", "firstName" => "John", "lastName" => 'Doe', "createdAt" => $db->now(), "updatedAt" => $db->now(), ); $updateColumns = Array ("updatedAt"); $lastInsertId = "id"; $db->onDuplicate($updateColumns, $lastInsertId); $id = $db->insert ('users', $data);Insert multiple datasets at once
$data = Array( Array ("login" => "admin", "firstName" => "John", "lastName" => 'Doe' ), Array ("login" => "other", "firstName" => "Another", "lastName" => 'User', "password" => "very_cool_hash" ) ); $ids = $db->insertMulti('users', $data); if(!$ids) { echo 'insert failed: ' . $db->getLastError(); } else { echo 'new users inserted with following id\'s: ' . implode(', ', $ids); }If all datasets only have the same keys, it can be simplified
$data = Array( Array ("admin", "John", "Doe"), Array ("other", "Another", "User") ); $keys = Array("login", "firstName", "lastName"); $ids = $db->insertMulti('users', $data, $keys); if(!$ids) { echo 'insert failed: ' . $db->getLastError(); } else { echo 'new users inserted with following id\'s: ' . implode(', ', $ids); }$data = Array ( 'firstName' => 'Bobby', 'lastName' => 'Tables', 'editCount' => $db->inc(2), // editCount = editCount + 2; 'active' => $db->not() // active = !active; ); $db->where ('id', 1); if ($db->update ('users', $data)) echo $db->count . ' records were updated'; else echo 'update failed: ' . $db->getLastError();update() also support limit parameter:
$db->update ('users', $data, 10); // Gives: UPDATE users SET ... LIMIT 10select title and content columns
$selectCustomFields = $db->select(['article', ['title, content']], null, '3', '0', ['id' => 'ASC']);select all from the table
$selectAll = $db->select('article');or select just one row
$selectAll = $db->select('article', ['id' => 1]);$db->where('id', 1); if($db->delete('users')) echo 'successfully deleted';$db->where('id', 1); if($db->delete('users')) echo 'successfully deleted';$db->where('id', 1); if($db->delete('users')) echo 'successfully deleted';$db->truncateTable('article');example of use:
if(truncateTable('article')) { echo 'Table article successfully cleared'; }$db->dropDatabase('articles');example of use:
if(dropDatabase('articles')) { echo 'Table articles successfully deleted'; }$db->dropTable('article');example of use:
if(dropTable('article')) { echo 'Table article successfully deleted'; }