jasny / persist-sql-query
SQL Query builder and parser
Installs: 318 301
Dependents: 1
Suggesters: 0
Security: 0
Stars: 33
Watchers: 8
Forks: 8
Open Issues: 2
Requires
- php: >=7.4.0
Requires (Dev)
- jasny/php-code-quality: 2.6.*
This package is auto-updated.
Last update: 2024-10-30 00:36:14 UTC
README
Persist - SQL Query
This library is designed to be the ultimate tool for building, splitting and modifying queries.
Automatic smart quoting helps against SQL injection and problems with reserved keywords.
Supported SQL dialects
- Generic (ANSI standard)
- MySQL
- PostgreSQL (todo)
- SQLite (todo)
Installation
composer install jasny/persist-sql-query
Examples
Add WHERE
conditions and an OFFSET
to a SELECT
query
use Jasny\Persist\SQL\Query; $query = (new Query("SELECT * FROM foo LEFT JOIN bar ON foo.bar_id = bar.id WHERE active = 1 LIMIT 25")) ->where('type', 'bike') ->where('price between ? and ?', [10, 20]) ->page(3); echo $query; // SELECT * FROM foo LEFT JOIN bar ON foo.bar_id = bar.id WHERE (active = 1) AND (`type` = 'bike') AND (`price` between 10 and 20) LIMIT 25 OFFSET 50
Map fields for an INSERT INTO ... SELECT ... ON DUPLICATE KEY
query
use Jasny\Persist\SQL\Query; $columns = [ 'ref' => 'ref', 'man' => 'boy', 'woman' => 'girl', 'amount' => 'SUM(z.bucks)' ]; $build = Query::build('mysql'); $select = $build->select()->columns($columns)->from('foo')->innerJoin('z', 'foo.id = z.foo_id')->groupBy('foo.id'); $insert = $build->insert()->into('abc')->columns(array_keys($columns))->set($select)->onDuplicateKeyUpdate(); echo $insert; // INSERT INTO `abc` (`ref`, `man`, `woman`, `amount`) // SELECT `ref` AS `ref`, `boy` AS `man`, `girl` AS `woman`, SUM(`z`.`bucks`) AS `amount` FROM `foo` LEFT JOIN `z` ON `foo`.`id` = `z`.`foo_id` GROUP BY `foo`.id` // ON DUPLICATE KEY UPDATE `ref` = VALUES(`ref`), `man` = VALUES(`man`), `woman` = VALUES(`woman`), `amount` = VALUES(`amount`)