PDO-database-class
PHP PDO Wrapper which utilizes PDO and prepared statements, (*1)
Table of Contents
Support Me
This software is developed during my free time and I will be glad if somebody will support me., (*2)
Everyone's time should be valuable, so please consider donating., (*3)
Donate with paypal, (*4)
Installation
To utilize this class, first import PDODb.php into your project, and require it.
PDODb requires PHP 5.5+ to work., (*5)
require_once ('PDODb.php');
Installation with composer
It is also possible to install library via composer, (*6)
composer require tommyknocker/pdo-database-class
Initialization
Simple initialization with utf8 charset set by default:, (*7)
$db = new PDODb('host', 'username', 'password', 'databaseName');
Advanced initialization:, (*8)
$db = new PDODb(['type' => 'mysql',
'host' => 'host',
'username' => 'username',
'password' => 'password',
'dbname'=> 'databaseName',
'port' => 3306,
'prefix' => 'my_',
'charset' => 'utf8']);
table prefix, port and database charset params are optional.
If no charset should be set charset, set it to null, (*9)
Also it is possible to reuse already connected pdo object:, (*10)
$pdo = new PDO('mysql:dbname=test;host=localhost', 'user', 'password');
$db = new PDODb($pdo);
If no table prefix were set during object creation its possible to set it later with a separate call:, (*11)
$db->setPrefix('my_');
If you need to get already created pdo object from another class or function use, (*12)
function init() {
// db staying private here
$db = new PDODb('type', 'host', 'username', 'password', 'databaseName');
}
...
function myfunс() {
// obtain db object created in init()
$db = PDODb::getInstance();
...
}
Insert Query
Simple example, (*13)
$data = ["login" => "admin",
"firstName" => "John",
"lastName" => 'Doe'
];
$id = $db->insert('users', $data);
if($id)
echo 'user was created. Id=' . $id;
Insert with functions use, (*14)
$data = [
'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, (*15)
$data = ["login" => "admin",
"firstName" => "John",
"lastName" => 'Doe',
"createdAt" => $db->now(),
"updatedAt" => $db->now(),
];
$updateColumns = ["updatedAt"];
$lastInsertId = "id";
$db->onDuplicate($updateColumns, $lastInsertId);
$id = $db->insert('users', $data);
Replace Query
Replace() method implements same API as insert();, (*16)
Update Query
$data = ['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->getRowCount() . ' records were updated';
} else {
echo 'update failed: ' . $db->getLastError();
}
update() also support limit parameter:, (*17)
$db->update('users', $data, 10);
// Gives: UPDATE users SET ... LIMIT 10
Select Query
After any select/get function calls amount or returned rows is stored in $count variable, (*18)
$users = $db->get('users'); //contains an Array of all users
$users = $db->get('users', 10); //contains an Array 10 users
or select with custom columns set. Functions also could be used, (*19)
$cols = ["id", "name", "email"];
$users = $db->get("users", null, $cols);
if ($users) {
foreach ($users as $user) {
print_r ($user);
}
}
or select just one row, (*20)
$db->where("id", 1);
$user = $db->getOne("users");
echo $user['id'];
$stats = $db->getOne("users", "SUM(id), COUNT(*) as cnt");
echo "total ".$stats['cnt']. "users found";
or select one column value or function result, (*21)
$count = $db->getValue("users", "COUNT(*)");
echo "{$count} users found";
select one column value or function result from multiple rows:, (*22)
$logins = $db->getValue("users", "login", null);
// select login from users
$logins = $db->getValue("users", "login", 5);
// select login from users limit 5
foreach ($logins as $login) {
echo $login;
}
You may use php 5.5+ generator feature with PDODb get(), rawQuery() methods just call useGenerator(true) method, (*23)
Example:, (*24)
$result = $db->useGenerator(true)->get('users'); // $result will contain Generator object
if($result->current()) {
foreach($result as $row) {
print_r($row);
}
}
Use paginate() instead of get() to fetch paginated result, (*25)
$page = 1;
// set page limit to 2 results per page. 20 by default
$db->pageLimit = 2;
$products = $db->paginate("products", $page);
echo "showing $page out of " . $db->totalPages;
Defining a return type
To select a return type use setReturnType() method., (*26)
// Array return type
$= $db->getOne("users");
echo $u['login'];
// Object return type
$u = $db->setReturnType(PDO::FETCH_OBJ)->getOne("users");
echo $u->login;
Running raw SQL queries
$users = $db->rawQuery('SELECT * FROM users WHERE id >= ?', [10]);
foreach ($users as $user) {
print_r($user);
}
$users = $db->rawQuery('SELECT * FROM users WHERE name=:name', ['name' => 'user1']);
foreach ($users as $user) {
print_r($user);
}
To avoid long if checks there are couple helper functions to work with raw query select results:, (*27)
Get 1 row of results:, (*28)
$user = $db->rawQueryOne('SELECT * FROM users WHERE id=:id', [id => 10]);
echo $user['login'];
// Object return type
$user = $db->setReturnType(PDO::FETCH_OBJ)->rawQueryOne('SELECT * FROM users WHERE id=?', [10]);
echo $user->login;
Get 1 column value as a string:, (*29)
$password = $db->rawQueryValue('SELECT password FROM users WHERE id=? LIMIT 1', [10]);
echo "Password is {$password}";
NOTE: for a rawQueryValue() to return string instead of an array 'limit 1' should be added to the end of the query.
Get 1 column value from multiple rows:, (*30)
$logins = $db->rawQueryValue('SELECT login FROM users LIMIT 10');
foreach ($logins as $login) {
echo $login;
}
More advanced examples:, (*31)
$params = [1, 'admin'];
$users = $db->rawQuery("SELECT id, firstName, lastName FROM users WHERE id = ? AND login = ?", $params);
print_r($users); // contains Array of returned rows
// will handle any SQL query
$params = [10, 1, 10, 11, 2, 10];
$query = "(
SELECT a FROM t1
WHERE a = ? AND B = ?
ORDER BY a LIMIT ?
) UNION (
SELECT a FROM t2
WHERE a = ? AND B = ?
ORDER BY a LIMIT ?
)";
$result = $db->rawQuery($query, $params);
print_r ($result); // contains array of returned rows
Where / Having Methods
where(), orWhere(), having() and orHaving() methods allows you to specify where and having conditions of the query. All conditions supported by where() are supported by having() as well., (*32)
WARNING: In order to use column to column comparisons only raw where conditions should be used as column name or functions cant be passed as a bind variable., (*33)
Regular == operator with variables:, (*34)
$db->where('id', 1);
$db->where('login', 'admin');
$results = $db->get('users');
// Gives: SELECT * FROM users WHERE id=1 AND login='admin';
$db->where ('id', 1);
$db->having ('login', 'admin');
$results = $db->get ('users');
// Gives: SELECT * FROM users WHERE id=1 HAVING login='admin';
Regular == operator with column to column comparison:, (*35)
// WRONG
$db->where('lastLogin', 'createdAt');
// CORRECT
$db->where('lastLogin = createdAt');
$results = $db->get('users');
// Gives: SELECT * FROM users WHERE lastLogin = createdAt;
$db->where('id', 50, ">=");
// or $db->where('id', ['>=' => 50]);
$results = $db->get('users');
// Gives: SELECT * FROM users WHERE id >= 50;
BETWEEN / NOT BETWEEN:, (*36)
$db->where('id', [4, 20], 'BETWEEN');
$results = $db->get('users');
// Gives: SELECT * FROM users WHERE id BETWEEN 4 AND 20
IN / NOT IN:, (*37)
$db->where('id', [1, 5, 27, -1, 'd'], 'IN');
$results = $db->get('users');
// Gives: SELECT * FROM users WHERE id IN (1, 5, 27, -1, 'd');
OR CASE, (*38)
$db->where('firstName', 'John');
$db->orWhere('firstName', 'Peter');
$results = $db->get('users');
// Gives: SELECT * FROM users WHERE firstName='John' OR firstName='peter'
$db->where('firstName', 'John');
$db->orWhere('firstName', 'Peter');
$results = $db->get('users');
// Gives: SELECT * FROM users WHERE firstName='John' OR firstName='peter'
NULL comparison:, (*39)
$db->where("lastName", NULL, 'IS NOT');
$results = $db->get("users");
// Gives: SELECT * FROM users where lastName IS NOT NULL
Also you can use raw where conditions:, (*40)
$db->where("id != companyId");
$db->where("DATE(createdAt) = DATE(lastLogin)");
$results = $db->get("users");
Or raw condition with variables:, (*41)
$db->where("(id = ? OR id = ?)", [6,2]);
$db->where("login","mike")
$res = $db->get ("users");
// Gives: SELECT * FROM users WHERE (id = 6 OR id = 2) AND login='mike';
Find the total number of rows matched. Simple pagination example:, (*42)
$offset = 10;
$count = 15;
$users = $db->withTotalCount()->get('users', [$offset, $count]);
echo "Showing {$count} from {$db->totalCount}";
Query Keywords
To add LOW PRIORITY | DELAYED | HIGH PRIORITY | IGNORE and the rest of the mysql keywords to INSERT (), REPLACE (), GET (), UPDATE (), DELETE() method or FOR UPDATE | LOCK IN SHARE MODE into SELECT ():, (*43)
$db->setQueryOption('LOW_PRIORITY')->insert($table, $param);
// GIVES: INSERT LOW_PRIORITY INTO table ...
```php
$db->setQueryOption('FOR UPDATE')->get('users');
// GIVES: SELECT * FROM USERS FOR UPDATE;, (*44)
Also you can use an array of keywords:
```php
$db->setQueryOption(['LOW_PRIORITY', 'IGNORE'])->insert($table,$param);
// GIVES: INSERT LOW_PRIORITY IGNORE INTO table ...
Same way keywords could be used in SELECT queries as well:, (*45)
$db->setQueryOption('SQL_NO_CACHE');
$db->get("users");
// GIVES: SELECT SQL_NO_CACHE * FROM USERS;
Optionally you can use method chaining to call where multiple times without referencing your object over an over:, (*46)
$results = $db
->where('id', 1)
->where('login', 'admin')
->get('users');
Delete Query
$db->where('id', 1);
if($db->delete('users')) echo 'successfully deleted';
Ordering method
$db->orderBy("id","ASC");
$db->orderBy("login","DESC");
$db->orderBy("RAND ()");
$results = $db->get('users');
// Gives: SELECT * FROM users ORDER BY id ASC,login DESC, RAND ();
Order by values example:, (*47)
$db->orderBy('userGroup', 'ASC', ['superuser', 'admin', 'users']);
$db->get('users');
// Gives: SELECT * FROM users ORDER BY FIELD (userGroup, 'superuser', 'admin', 'users') ASC;
If you are using setPrefix () functionality and need to use table names in orderBy() method make sure that table names are escaped with ``., (*48)
$db->setPrefix("t_");
$db->orderBy("users.id","ASC");
$results = $db->get('users');
// WRONG: That will give: SELECT * FROM t_users ORDER BY users.id ASC;
$db->setPrefix("t_");
$db->orderBy("`users`.id", "ASC");
$results = $db->get('users');
// CORRECT: That will give: SELECT * FROM t_users ORDER BY t_users.id ASC;
Grouping method
$db->groupBy("name");
$results = $db->get('users');
// Gives: SELECT * FROM users GROUP BY name;
Join table products with table users with LEFT JOIN by tenantID, (*49)
JOIN method
$db->join("users u", "p.tenantID=u.tenantID", "LEFT");
$db->where("u.id", 6);
$products = $db->get("products p", null, "u.name, p.productName");
print_r($products);
Properties sharing
Its is also possible to copy properties, (*50)
$db->where("agentId", 10);
$db->where("active", true);
$customers = $db->copy();
$res = $customers->get("customers", [10, 10]);
// SELECT * FROM customers where agentId = 10 and active = 1 limit 10, 10
$cnt = $db->getValue("customers", "COUNT(id)");
echo "total records found: " . $cnt;
// SELECT COUNT(id) FROM users WHERE agentId = 10 AND active = 1
Subqueries
Subquery init, (*51)
Subquery init without an alias to use in inserts/updates/where Eg. (select * from users), (*52)
$sq = $db->subQuery();
$sq->get("users");
A subquery with an alias specified to use in JOINs . Eg. (select * from users) sq, (*53)
$sq = $db->subQuery("sq");
$sq->get("users");
Subquery in selects:, (*54)
$ids = $db->subQuery ();
$ids->where("qty", 2, ">");
$ids->get("products", null, "userId");
$db->where("id", $ids, 'in');
$res = $db->get("users");
// Gives SELECT * FROM users WHERE id IN (SELECT userId FROM products WHERE qty > 2)
Subquery in inserts:, (*55)
$userIdQ = $db->subQuery ();
$userIdQ->where("id", 6);
$userIdQ->getOne("users", "name");
$data = ["productName" => "test product",
"userId" => $userIdQ,
"lastUpdated" => $db->now()
];
$id = $db->insert("products", $data);
// Gives INSERT INTO PRODUCTS (productName, userId, lastUpdated) values ("test product", (SELECT name FROM users WHERE id = 6), NOW());
Subquery in joins:, (*56)
$usersQ = $db->subQuery("u");
$usersQ->where("active", 1);
$usersQ->get("users");
$db->join($usersQ, "p.userId=u.id", "LEFT");
$products = $db->get("products p", null, "u.login, p.productName");
print_r($products);
// SELECT u.login, p.productName FROM products p LEFT JOIN (SELECT * FROM t_users WHERE active = 1) u on p.userId=u.id;
EXISTS / NOT EXISTS condition
$sub = $db->subQuery();
$sub->where("company", 'testCompany');
$sub->get ("users", null, 'userId');
$db->where (null, $sub, 'EXISTS');
$products = $db->get ("products");
// Gives SELECT * FROM products WHERE EXISTS (select userId from users where company='testCompany')
Has method
A convenient function that returns TRUE if exists at least an element that satisfy the where condition specified calling the "where" method before this one., (*57)
$db->where("user", $user);
$db->where("password", md5($password));
if($db->has("users")) {
return "You are logged";
} else {
return "Wrong user/password";
}
Helper methods
Get last executed SQL query:
Please note that function returns SQL query only for debugging purposes as its execution most likely will fail due missing quotes around char variables., (*58)
$db->get('users');
echo "Last executed query was ". $db->getLastQuery();
Check if table exists:, (*59)
if ($db->tableExists('users')) {
echo "hooray";
}
pdo::quote() wrapper:, (*60)
$escaped = $db->escape("' and 1=1");
Transaction helpers
Please keep in mind that transactions are working on innoDB tables.
Rollback transaction if insert fails:, (*61)
$db->startTransaction();
...
if (!$db->insert('myTable', $insertData)) {
//Error while saving, cancel new record
$db->rollback();
} else {
//OK
$db->commit();
}
Error helpers
After you executed a query you have options to check if there was an error. You can get the MySQL error string or the error code for the last executed query., (*62)
$db->where('login', 'admin')->update('users', ['firstName' => 'Jack']);
if ($db->getLastErrNo() === 0) {
echo 'Update succesfull';
} else {
echo 'Update failed. Error: '. $db->getLastError();
}
Wallogit.com
