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'; }