jasny/dbquery-mysql

Query builder and parser for MySQL

, _(*1)

Persist - SQL Query

This library is designed to be the ultimate tool for building, splitting and modifying queries., _(*2)

Automatic smart quoting helps against SQL injection and problems with reserved keywords., _(*3)

Supported SQL dialects, _(*4)

• 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, _(*5)

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, _(*6)

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`)