A fluent, zero-dependency SQL query builder for PHP. It builds MySQL-style SQL (backtick-quoted
identifiers) as plain strings plus a bindings array, ready to hand to PDO::prepare() /
PDOStatement::execute(). The core guarantee: values you pass in are never interpolated into
the SQL string — they only ever appear, in order, in the returned bindings array.
Once published to Packagist:
composer require kasapdev/php-query-builder
Or just require the file directly:
require_once 'src/QueryBuilder.php';
use Kasapdev\QueryBuilder\QueryBuilder; // SELECT ... [$sql, $bindings] = QueryBuilder::table('users') ->select(['users.id', 'users.name', 'orders.total']) ->join('orders', 'users.id', '=', 'orders.user_id') ->where('users.active', '=', 1) ->andWhere('orders.total', '>', 100) ->orWhere('users.role', '=', 'admin') ->orderBy('orders.total', 'DESC') ->limit(10) ->offset(0) ->toSql(); // $sql: "SELECT `users`.`id`, `users`.`name`, `orders`.`total` FROM `users` // INNER JOIN `orders` ON `users`.`id` = `orders`.`user_id` // WHERE `users`.`active` = ? AND `orders`.`total` > ? OR `users`.`role` = ? // ORDER BY `orders`.`total` DESC LIMIT ? OFFSET ?" // $bindings: [1, 100, "admin", 10, 0] $pdo = new PDO('mysql:host=localhost;dbname=app', $user, $pass); $stmt = $pdo->prepare($sql); $stmt->execute($bindings); $rows = $stmt->fetchAll(PDO::FETCH_ASSOC); // INSERT / UPDATE / DELETE are terminal helpers on the same class: $qb = QueryBuilder::table('users'); [$sql, $bindings] = $qb->insert('users', ['name' => 'Ada', 'email' => '[email protected]']); $pdo->prepare($sql)->execute($bindings); // upsert() = INSERT, or UPDATE the existing row if a unique/primary key collides: [$sql, $bindings] = $qb->upsert('users', ['id' => 7, 'name' => 'Ada', 'hits' => 1]); $pdo->prepare($sql)->execute($bindings); [$sql, $bindings] = $qb->update('users', ['name' => 'Grace'], ['id' => 7]); $pdo->prepare($sql)->execute($bindings); [$sql, $bindings] = $qb->delete('users', ['id' => 7]); $pdo->prepare($sql)->execute($bindings);
Conditions can be built up incrementally too — where()/andWhere()/orWhere() all just append to the
same query, so you can branch on application logic before compiling:
$query = QueryBuilder::table('users')->select(['id', 'name', 'email']); if ($onlyActive) { $query->where('active', '=', 1); } if ($minAge !== null) { $query->andWhere('age', '>=', $minAge); } [$sql, $bindings] = $query->orderBy('name')->limit(50)->toSql(); $stmt = $pdo->prepare($sql); $stmt->execute($bindings); $users = $stmt->fetchAll(PDO::FETCH_ASSOC);
$evil = "'; DROP TABLE users; --"; [$sql, $bindings] = QueryBuilder::table('users')->where('name', '=', $evil)->toSql(); // $sql: "SELECT * FROM `users` WHERE `name` = ?" <-- no injected SQL, ever // $bindings: ["'; DROP TABLE users; --"] <-- the raw value, safely parameterized
This is enforced structurally: the builder never does string concatenation of a value into the
SQL text. Every value-producing code path (where, having, insert, update, delete)
appends a ? to the SQL and pushes the raw value onto the bindings array instead.
->where('id', 5) // shorthand for where('id', '=', 5) ->where('id', '=', 5) ->where('age', '>=', 18) ->where('id', 'IN', [1, 2, 3]) // expands to IN (?, ?, ?) ->where('id', 'NOT IN', [1, 2]) ->where('deleted_at', '=', null) // compiles to IS NULL ->where('deleted_at', '!=', null) // compiles to IS NOT NULL ->where('id', 'IN', $subquery) // subquery, expands to IN (SELECT ...) — see "Subqueries" below ->andWhere(...) // explicit AND ->orWhere(...) // explicit OR
// If a row with a colliding unique/primary key already exists, refresh every // given column on it instead of failing the INSERT: [$sql, $bindings] = QueryBuilder::table('users')->upsert('users', [ 'id' => 7, 'email' => '[email protected]', 'login_count' => 1, ]); // INSERT INTO `users` (`id`, `email`, `login_count`) VALUES (?, ?, ?) // ON DUPLICATE KEY UPDATE `id` = VALUES(`id`), `email` = VALUES(`email`), `login_count` = VALUES(`login_count`) // Pass $updateColumns to only refresh specific columns on conflict (e.g. leave // login_count alone and just bump the email): [$sql, $bindings] = QueryBuilder::table('users')->upsert( 'users', ['id' => 7, 'email' => '[email protected]', 'login_count' => 1], ['email'] ); // ... ON DUPLICATE KEY UPDATE `email` = VALUES(`email`)
This compiles MySQL/MariaDB's INSERT ... ON DUPLICATE KEY UPDATE. Which row counts as a
"duplicate" is decided by the table's own unique/primary key constraints, not by anything passed
to upsert() — there's no separate $uniqueBy argument because MySQL doesn't need one.
Pass a nested QueryBuilder instance as a where() / andWhere() / orWhere() value and it
compiles to a parenthesized (SELECT ...), with the subquery's own bindings merged into the
parent's bindings array at exactly the position its placeholders appear in the SQL — the same
left-to-right ordering guarantee the library already gives you for a single query, now proven
recursively for nested ones:
$bigSpenders = QueryBuilder::table('orders') ->select(['user_id']) ->where('total', '>', 100); [$sql, $bindings] = QueryBuilder::table('users') ->where('status', '=', 'active') ->andWhere('id', 'IN', $bigSpenders) ->toSql(); // $sql: "SELECT * FROM `users` WHERE `status` = ? AND `id` IN (SELECT `user_id` FROM `orders` WHERE `total` > ?)" // $bindings: ["active", 100] $stmt = $pdo->prepare($sql); $stmt->execute($bindings);
Values inside the subquery are still never interpolated into the SQL string — they flow through
compileConditions() the same way top-level values do, so the "always parameterize" guarantee
holds no matter how deeply queries are nested. A subquery also works as a plain scalar comparison
value (where('id', '=', $subquery) compiles to `id` = (SELECT ...)) since it falls out of
the same "value is a QueryBuilder" check — but IN (subquery) is the primary use case.
QueryBuilder::table(string $table): self— start a queryselect(array $columns): selfwhere(string $column, mixed $operator, mixed $value = null): selfandWhere(...)/orWhere(...)— same signature aswhere()join(string $table, string $first, string $operator, string $second, string $type = 'INNER'): selfleftJoin(...)/rightJoin(...)— same signature asjoin()minus$typeorderBy(string $column, string $direction = 'ASC'): selfgroupBy(string ...$columns): selfhaving(string $column, mixed $operator, mixed $value = null): self/orHaving(...)limit(int $limit): selfoffset(int $offset): selftoSql(): array→[string $sql, array $bindings]insert(string $table, array $data): array→[string $sql, array $bindings]upsert(string $table, array $data, array $updateColumns = []): array→[string $sql, array $bindings]—INSERT ... ON DUPLICATE KEY UPDATE;$updateColumnsdefaults to every column in$dataupdate(string $table, array $data, array $where): array→[string $sql, array $bindings]delete(string $table, array $where): array→[string $sql, array $bindings]
All identifiers (table names, column names) are quoted with backticks. Dotted identifiers
(table.column) are quoted per-segment. Expressions that aren't simple identifiers (containing
spaces, parentheses, etc.) are passed through unmodified so you can supply raw expressions like
COUNT(*) AS total.
php tests/run.php
The test suite includes an explicit SQL-injection regression test (a value containing
'; DROP TABLE users; --' is asserted to appear only in the bindings array, never in the SQL
string) plus end-to-end execution of generated SQL against a real in-memory SQLite database.
MIT