Switch branches/tags
Nothing to show
Find file
27ae5a9 Nov 3, 2011
@dmitrymin @DmitryKoterov
351 lines (251 sloc) 9.35 KB
DbSimple: Simplest but powerful interface to work with various relational databases.
(C) Dmitry Koterov,
There are a lot of PHP libraries which bring us unified database access
interfaces: PEAR DB, ADOdb and PDO. Code written using these libraries is
known to be very verbalize and is excessive overloaded by useless details.
DbSimple introduces the interface much more simple and handy than above
(and many other popular) abstraction libraries.
* Supports PHP 4 and 5, DBMS: MySQL, PostgreSQL � InterBase/FireBird.
* Simple and laconic interface (see samples below).
* Conditional macro-blocks in SQL body ({}-blocks), which allow to
dynamically generate even very complex queries without detriment to
code readability.
* Caching of query results (if necessary).
* Supports various placeholder (query arguments) types: list-based,
associative, identifier-based etc.
* Supports operation "select + count total number of resulting rows"
(for data displayed page-by-page).
* Functions for direct fetching: all result rows, one row, one column,
one cell, associative array, multi-dimensional associative array,
linked tree etc.
* Very handy interface to watch and debug query errors.
* Supports enhanced query logging (including query results and caller
line number).
* Supports "native" database placeholders and automatic optimization
"single prepare, multiple execute".
* Object-based BLOB access interface (if necessary).
* Library code is quite compact: one file is the base class, one file -
specific database driver.
* License LGPL (open-source).
* Library should not wrap differences in SQL language between different DBMS.
* Interface must be extremely laconic and handy for practical usage.
* "Query execution" and "result fetching" must be joined together into
single operation.
* If a query is built "piece by piece" (dynamically by PHP code), it must be
done without detriment to readability.
* Optimization "single prepare, multiple execute" must be performed transparently
and automatically.
* PEAR DB, ADOdb: these libraries do not simplify DBMS access, they
simply provide us single (and heavy overloaded) access interface; debug
functions are too poor.
* PDO: requires PHP 5; not quite handy work with placeholders and query
results provided.
* Standart PHP functions for DBMS access: poor code readability, large
debugging discomfort, amenability to SQL Injections.
mixed connect(string $dsn)
Static function to connect ANY database using DSN syntax.
mixed select(string $query [,$arg1...])
Executes the query and returns the result as 2D-array.
hash selectRow(string $query [,$arg1...])
Fetches the result of single-row query.
array selectCol(string $query [,$arg1...])
Fetches one column.
scalar selectCell(string $query [,$arg1...])
Fetches one query result cell.
mixed selectPage(int &$total, string $query [,$arg1...)
Fetches 2D-array with total number of found rows calculation.
mixed query(string $query [,$arg1...])
Executes non-SELECT query; for auto-increment fields and INSERT
queries - returns last inserted ID.
mixed transaction([mixed $parameters])
Starts the new transaction.
mixed commit() / mixed rollback()
Commits/rollbacks the current transaction.
In addition, to modify the format of result representation you may use
reserved column aliases (ARRAY_KEY* etc.) and attributed SQL comments
(e.g. for turning on caching). See usage synopsis below.
Listing 1: Connect to DBMS
require_once "DbSimple/Generic.php";
$DB = DbSimple_Generic::connect("pgsql://login:password@host/database");
Listing 2: Fetch all resulting rows
$rows = $DB->select('SELECT * FROM ?_user LIMIT 10');
foreach ($rows as $numRow => $row) {
echo $row['user_name'];
Listing 3: Fetch one page
// Variable $totalNumberOfUsers will hold total number of found rows.
$rows = $DB->selectPage(
'SELECT * FROM ?_user LIMIT ?d, ?d',
$pageOffset, $numUsersOnPage
Listing 4: Macro-substitutions in SQL queries
$rows = $DB->select('
FROM goods
category_id = ?
{ AND activated_at > ? }
(empty($_POST['activated_at'])? DBSIMPLE_SKIP : $_POST['activated_at']),
Listing 5: Macro-substitutions in SQL queries #2
$rows = $DB->select('
goods g
{ JOIN category c ON = g.category_id AND 1 = ? }
1 = 1
{ AND = ? }
(empty($_POST['cat_name'])? DBSIMPLE_SKIP : 1),
(empty($_POST['cat_name'])? DBSIMPLE_SKIP : $_POST['cat_name']),
Listing 6: Macro-substitutions in SQL queries #3
$rows = $DB->select('
{ OR user_id IN(?a) }
// If empty, resulted to 1=0 which means false.
Listing 7: Query result caching by time
$row = $DB->select('
-- CACHE: 10h 20m 30s
SELECT * FROM table WHERE id=123
// Define caching function.
function myCacher($key, $value)
// If $value !== null then we must store it to the cache with key $key.
// If $value === null then we must return the value stored in the cache with key $key.
Listing 8: Query result caching with dependence on table modification
// Here forum.modified and topic.modified are TIMESTAMPs.
$row = $DB->select('
-- CACHE: 10h 20m 30s, forum.modified, topic.modified
FROM forum JOIN topic ON
WHERE id=123
Listing 9: Fetching of associative array
$rows = $DB->select('SELECT user_id AS ARRAY_KEY, ?_user.* FROM ?_user');
foreach ($rows as $userId => $userData) {
echo $userData['user_name'];
Listing 10: List-based placeholder
$ids = array(1, 101, 303);
$DB->select('SELECT name FROM tbl WHERE id IN(?a)', $ids);
// SELECT name FROM tbl WHERE id IN(1, 101, 303)
Listing 11: Associative placeholder
$row = array(
'id' => 10,
'date' => "2006-03-02"
$DB->query('UPDATE tbl SET ?a', $row);
// MySQL: UPDATE tbl SET `id`='10', `date`='2006-03-02'
Listing 12: Identifier-based placeholder
$DB->select('SELECT ?# FROM tbl', 'date');
// MySQL: SELECT `date` FROM tbl
// FireBird: SELECT "date" FROM tbl
Listing 13: Identifier-list-based placeholder
$user = array('user_id' => 101, 'user_name' => 'Rabbit', 'user_age' => 30);
$newUserId = $DB->query(
'INSERT INTO user(?#) VALUES(?a)',
Listing 14: {Prefix-based placeholder}
$DB->select('SELECT * FROM ?_user');
// SELECT * FROM phpbb_users
Listing 15: One row fetching
$row = $DB->selectRow('SELECT * FROM ?_user WHERE user_id=?', $uid);
Listing 16: One cell fetching
$userName = $DB->selectCell(
'SELECT user_name FROM ?_user WHERE user_id=?',
Listing 17: One column fetching
$cities = $DB->selectCol('SELECT city_name FROM ?_cities');
$citiesById = $DB->selectCol(
'SELECT city_id AS ARRAY_KEY, city_name FROM ?_cities'
Listing 18: Multi-dimensional associative array fetching
$messagesByTopics = $DB->select('
message_topic_id AS ARRAY_KEY_1,
message_id AS ARRAY_KEY_2,
message_subject, message_text
// $messagesByForumsAndTopics[topicId][messageId] = messageData
Listing 19: Linked tree fetching
$forest = $DB->select('
person_id AS ARRAY_KEY,
person_father_id AS PARENT_KEY,
FROM ?_person
Listing 20: prepare ... execute optimization
foreach ($array as $item) {
// DbSimple underatands that it should execure "prepare" only once!
$DB->query('INSERT INTO tbl(field) VALUES(?)', $item);
Listing 21: Error handling
// File connect.php
$DB = DbSimple_Generic::connect('mysql://test:test@localhost1/non-existed-db');
function databaseErrorHandler($message, $info)
if (!error_reporting()) return;
echo "SQL Error: $message<br><pre>"; print_r($info); echo "</pre>";
// As a result we will get:
SQL Error: Unknown MySQL Server Host 'localhost1' (11001) at .../connect.php line 17
[code] => 2005
[message] => Unknown MySQL Server Host 'localhost1' (11001)
[query] => mysql_connect()
[context] => .../connect.php line 17
Listing 22: Temporary error disabling
// Please note the "@" operator usage!
// Also an unique index must be created for id field.
if (!@$DB->query('INSERT INTO tbl(id, field) VALUES(1, ?)', $field)) {
// Here goes the reaction on the query error.
// You may fetch error context using $DB->error property.
$DB->query('UPDATE tbl SET field=? WHERE id=1', $field);
Listing 23: Query logging
$rows = $DB->select('SELECT * FROM U_GET_PARAM_LIST');
function myLogger($db, $sql)
$caller = $db->findLibraryCaller();
$tip = "at ".@$caller['file'].' line '.@$caller['line'];
// Print the query (of course it's better to use Debug_HackerConsole).
echo "<xmp title=\"$tip\">"; print_r($sql); echo "</xmp>";
// Will be printed something like:
--- 13 ms = 4+3+6; returned 30 row(s);