Query builder and parser for MySQL

Installs: 309

Dependents: 0

Stars: 19

Watchers: 6

Forks: 8

Open Issues: 1

Language: PHP

v2.0.0 2014-08-31 03:14 UTC


Build Status

This library is designed to be the ultimate tool for building, splitting and modifying MySQL queries.

Automatic smart quoting helps against SQL injection and problems with reserved keywords.

The query builder can be used standalone, in conjunction with Jasny's DB layer or in almost any framework.


Jasny's MySQL query builder is registred at packagist as jasny/dbquery-mysql and can be easily installed using composer.

composer install jasny/dbquery-mysql

Alternatively you can simply download the .zip and copy the file from the 'src' folder.


An example to simple to be using a query builder

    use Jasny\DB\MySQL\Query;

    $query = Query::select()->columns('id, name')->from('foo')->where('active = 1');
    $result = $mysqli->query($query); // SELECT `id`, `name` FROM `foo` WHERE `active` = 1

Dynamicly apply paging and filtering on a query

    use Jasny\DB\MySQL\Query;

    $query = new Query("SELECT * FROM foo LEFT JOIN bar ON foo.bar_id = bar.id WHERE active = 1 LIMIT 25");
    if (isset($_GET['page'])) $query->page(3);

    $filter = isset($_POST['filter']) ? $_POST['filter'] : array(); // array('type' => 'bike', 'price between ? and ?' => array(10, 20))
    foreach ($filter as $field => $value) {
        $query->where($field, $value);

    $result = $mysqli->query($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\DB\MySQL\Query;

    $columns = array(
        'ref' => 'ref',
        'man' => 'boy',
        'woman' => 'girl',
        'amount' => 'SUM(z.bucks)'

    $select = Query::select()->columns($columns)->from('foo')->innerJoin('z', 'foo.id = z.foo_id')->groupBy('foo.id');
    $insert = Query::insert()->into('abc')->columns(array_keys($columns))->set($select)->onDuplicateKeyUpdate();

    $mysql->query($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`)

API documentation (generated)