Easy to use query builder for PHP5.3+/PDO

Installs: 1 340

Dependents: 1

Suggesters: 0

Stars: 11

Watchers: 5

Forks: 2

Open Issues: 0


SensioLabsInsight Scrutinizer Code Quality Build Status Latest Stable Version License

Simple mysql query builder to build select, insert, update and delete queries with conditional parts. This library was initially not intended to build prepared statements, but this is also possible. The main motive for this library is an environment where a lot of things are automated.

Here a few things to keep in mind:

  • The charset is up to you. No special binding to UTF8, although UTF8 is the default.
  • The order of method-calls of each statement-builder is irrelevant. The resulting query will always render the right order.
  • No animals were harmed due to the production of this library.

Some examples


$pdo = new PDO('mysql:host=;dbname=test;charset=utf8', 'root', '', [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
$mysql = new MySQL($pdo);
$mysql->getAliasRegistry()->add('t', 'testdb.test__');


$subSelect = function ($id) use ($mysql) {
    return $mysql->select()
    ->from('t', 'table')
    ->where('t.foreign_id=?', $id);

$select = $mysql->select()
->field('COUNT(*)', 'customer_count')
->from('t1', 't#test1')
->joinInner('t2', 't#test2', 't2.test_id = t1.id AND t2.field1 = ?', 123)
->joinLeft('t3', 't#test3', 't3.test_id = t1.id')
->joinRight('t4', 't#test4', 't4.test_id = t1.id')
->joinRight('t5', $subSelect(10), 't5.test_id = t1.id')
->orderBy('t1.field2', 'DESC')
if($contition === true) {
    $select->where('t1.somefield = ?', $someValue);
$rows = $select->fetchRows();
foreach($rows as $row) {
  • The order of method-calls doesn't matter.


You can insert key-value-arrays with addAll, updateAll, addOrUpdateAll. As the second parameter you can provide an array to specify the only fields to consider.

$id = $mysql->insert()
->addOrUpdateAll($data, ['field1', 'field2', 'field3'])
->add('created_by', $userId)
->addOrUpdate('updated_by', $userId)
  • insert() alwasy returns an id, no matter if a dataset was actually inserted or updated.
  • You can mass-insert by using insert()->...->insertRows(array $rows).

There is also an option to build an INSERT INTO ... SELECT ... FROM ... ON DUPLICATE KEY UPDATE ...:

$id = $mysql->insert()
    ->field('a.myfield1', 'field1')
    ->field('a.myfield2', 'field2')
    ->from('a', 'mytable')
    ->where('field=?', 1)


->table('t1', 'test1')
->joinLeft('t2', 'test2', 't1.id = t2.test_id')
->where("t1.field1 = ? OR t2.field2 > ?", 1, 10)
->where("field IN (?)", [1, 2, 3, 4, 5, 6])


You can use joins in delete-statements. But only the rows of tables specified in from will be modified (deleted).

->from('t1', 'test1')
->joinLeft('t2', 'test2', 't1.id=t2.test_id')
->where('t1.field1=? AND t2.field2 > ?', 1, 10)

True nested transactions

$mysql = new \Kir\MySQL\Databases\MySQL($pdo);


$test = function () use ($mysql) {
    $name = $mysql->select()
    ->from('t', 'test')
    ->where('t.id=?', 1)
    printf("Current name is %s\n", $name);

$setName = function ($name) use ($mysql) {
    ->add('id', 1)
    ->addOrUpdate('name', $name)


$mysql->transaction(function () use ($mysql, $setName, $test) {

    // $mysql->transaction or...
    $mysql->dryRun(function () use ($mysql, $setName, $test) {

Current name is Peter
Current name is Paul
Current name is Bert
Current name is Paul