A package to make building database queries easy.
composer install tomwright/db-query-builder
The Query Builder should be used to generate SQL queries in an object orientated fashion, allowing multiple functions or objects to modify queries in any way they desire., (*2)
This works well in large applications and search functionality., (*3)
In all examples below:
- $db
is an instance of ExtendedPDO.
- $builder
is an instance of SqlQueryBuilder
. E.g. $builder = new SqlQueryBuilder();
., (*4)
SELECT uea.email FROM users u JOIN user_email_addresses uea ON uesa.user_id = u.user_id WHERE uea.email_confirmed = 1 AND ( uea.dt_deleted IS NULL OR uea.dt_deleted > NOW() ) ORDER BY uea.dt_created ASC
In order to get the above query, you would do something like this:, (*5)
$query = $builder ->select() ->setFields(['uea.email']) ->setTable('users u') ->addJoin(new Join('JOIN', 'user_email_addresses uea', 'uea.user_id = u.user_id')) ->addWhere('uea.email_confirmed', true) ->addRawWhere('( uea.dt_deleted IS NULL OR uea.dt_deleted > NOW() )') ->addOrderBy('uea.dt_created ASC') ->buildQuery(); $db->queryAll($query->getSql(), $query->getBinds());
UPDATE users SET username = 'Tom', dt_modified = NOW() WHERE user_id = 5;
In order to get the above query, you would do something like this:, (*6)
$query = $builder ->update() ->setValues('username', 'Tom') ->addRawValue('dt_modified', 'NOW()') ->setTable('users') ->addWhere('user_id', 5) ->buildQuery(); $db->dbQuery($query->getSql(), $query->getBinds());
In order to get the SQL and bind parameters, you must have already run buildQuery()
on the Query
object., (*7)
$query->getSql(); // SELECT * FROM users WHERE username = :_where_username; $query->getBinds(); // [':_where_username' => 'Tom']
Any of the following methods are available to create your Query object., (*8)
$query = $builder->select(); // SELECT query $query = new Query('DELETE'); // DELETE query $query = new Query()->setType('UPDATE'); // UPDATE query
The fields default to ['*']
., (*9)
$query->setFields(['something', 'something_else']); $query->addField('another_field'); $query->getFields(); // ['something', 'something_else', 'another_field']
$query->setTable('users'); $query->setTable('users u'); $query->getTable('users u');
Using the Query Builder here takes advantage of PDO bind parameters and makes you invulnerable to SQL Injection., (*10)
$query->addValue('users.username', 'Tom');
Using raw values will NOT use PDO bind parameters and so your SQL queries may be vulnerable to SQL Injection., (*11)
$query->addRawValue('users.dt_registered', 'NOW()');
Sometimes you may need to use the ON DUPLICATE KEY UPDATE
SQL syntax. This is achieved by doing the following., (*12)
$query->addOnDupeValue('users.username', 'Tom');
A full query may look something like this., (*13)
$q = new Query('INSERT'); $q->setTable('users'); $q->addValue('username', 'Tod'); $q->addValue('password', 'abcdef'); $q->addOnDupeValue('password', 'abcdef'); $q->buildQuery(); $q->getSql(); // INSERT INTO users SET username = :_update_bind_username, password = :_update_bind_password ON DUPLICATE KEY UPDATE password = :_dupe_update_bind_password;
Create an instance of the Join class and then add it to a Query., (*14)
$query ->setTable('users') ->addJoin(new Join('JOIN', 'codes', 'codes.user_id = users.user_id'));
You can also use aliases here., (*15)
$query ->setTable('users u') ->addJoin(new Join('JOIN', 'codes c', 'c.user_id = u.user_id'));
Building WHERE statements using the Query Builder takes advantage of PDO bind parameters so your queries are protected against SQL Injection., (*16)
The default comparison is =
., (*17)
$query->addWhere('users.username', 'Tom')
You can easily override the default comparison by doing the following., (*18)
$query->addWhere('users.username !=', 'Tom')
When you use raw SQL you will not benefit from PDO bind parameters and your queries may be vulnerable to SQL Injection., (*19)
$query->addRawWhere('users.dt_registered <= NOW()');
To save having lots of raw SQL when LIKE
is concerned, you can use the Like
object in your where clauses., (*20)
SELECT * FROM users WHERE (username LIKE '%Tom%' OR username LIKE '%Jim%');
becomes..., (*21)
$like = new Like('contains', ['Tom', 'Jim']); $q = new Query('SELECT'); $q->setTable('users'); $q->addWhere('username', $like);
The different like types you can use are contains
, starts_with
and ends_with
., (*22)
$query->addOrderBy('users.username ASC');
$query->addGroupBy('users.user_id');
$query->setLimit(5);
$query->setOffset(5);
The Query Builder has a handy method to slightly simplify pagination. If you were on the 2nd page, and displayed 5 records per page it would look like the following., (*23)
$query->setPage(2, 5);
In the background this simply sets a limit of 5 and an offset of 5., (*24)
If you are looking to use sub-queries but still want to use the query builder then you have come to the right place., (*25)
Using sub-queries in the following manner will still use PDO prepared statements so as your queries are always safe from SQL injection., (*26)
Simply build your sub-query and pass that into the where
method of another query using %SQL%
as a replacement placeholder for the SQL., (*27)
Desired SQL query:, (*28)
SELECT * FROM users WHERE user_id IN ( SELECT user_id FROM deleted_users WHERE deleted_users.deleted = 1 AND username != 'Jim'; ) AND username != 'Tom';
PHP Code:, (*29)
$subQ = new Query('SELECT'); $subQ->setTable('deleted_users'); $subQ->setFields(['user_id']); $subQ->addWhere('deleted_users.deleted', true); $subQ->addWhere('username !=', 'Jim'); $q = new Query('SELECT'); $q->setTable('users'); $q->addWhere('user_id IN (%SQL%)', $subQ); $q->addWhere('username !=', 'Tom'); $q->buildQuery();