morris / dop
An immutable API on top of PDO to compose and execute SQL statements
0.4.0
2020-05-09 15:14 UTC
Requires
- php: >=5.3.0
Requires (Dev)
- friendsofphp/php-cs-fixer: ^2.14
- phpunit/phpunit: ^8
README
DOP is an immutable API on top of PDO to compose and execute SQL statements.
- Extended parameters (
::param
and??
) allow binding to arbitrary values like arrays,null
and SQL fragments. - Provides helpers for writing common queries, e.g. selects, inserts, updates, deletes.
- Tested with SQLite, PostgreSQL, and MySQL.
Installation
DOP requires PHP >= 5.3.0 and PDO. Install via composer:
composer require morris/dop
Usage
// Connect to a database $pdo = new PDO('sqlite:blog.sqlite3'); $dop = new Dop\Connection($pdo); // Find posts by author IDs using DOP parametrization $authorIds = [1, 2, 3]; $orderByTitle = $dop('ORDER BY title ASC'); $posts = $dop( 'SELECT * FROM post WHERE author_id IN (??) ??', [$authorIds, $orderByTitle] )->fetchAll(); // Find published posts using DOP helpers for common queries $posts = $dop->query('post')->where('is_published = ?', [1])->fetchAll(); // Get categorizations of posts using DOP's map function $categorizations = $dop( 'SELECT * FROM categorization WHERE post_id IN (??)', [$dop->map($posts, 'id')] )->fetchAll(); // Find posts with more than 3 categorizations using a sub-query as a parameter $catCount = $dop('SELECT COUNT(*) FROM categorization WHERE post_id = post.id'); $posts = $dop( 'SELECT * FROM post WHERE (::catCount) >= 3', ['catCount' => $catCount] )->fetchAll();
Internally, ??
and ::named
parameters are resolved before statement preparation.
Note that due to the current implementation using regular expressions,
you should never use quoted strings directly. Always use bound parameters.
Reference
See API.md for a complete API reference.
Contributors
Thanks!