PHPackages                             tomwright/db-query-builder - PHPackages - PHPackages  [Skip to content](#main-content)[PHPackages](/)[Directory](/)[Categories](/categories)[Trending](/trending)[Leaderboard](/leaderboard)[Changelog](/changelog)[Analyze](/analyze)[Collections](/collections)[Log in](/login)[Sign up](/register)

1. [Directory](/)
2. /
3. [Database & ORM](/categories/database)
4. /
5. tomwright/db-query-builder

ActiveLibrary[Database & ORM](/categories/database)

tomwright/db-query-builder
==========================

A package to make building database queries easy.

1.0.0(8y ago)0441PHP

Since Nov 3Pushed 8y ago1 watchersCompare

[ Source](https://github.com/TomWright/QueryBuilderPHP)[ Packagist](https://packagist.org/packages/tomwright/db-query-builder)[ RSS](/packages/tomwright-db-query-builder/feed)WikiDiscussions master Synced 3d ago

READMEChangelogDependencies (1)Versions (2)Used By (0)

Query Builder PHP
=================

[](#query-builder-php)

[![Build Status](https://camo.githubusercontent.com/655c120202369df65b03e6ccee1df92f89b1e7936536d1c398d3380929302141/68747470733a2f2f7472617669732d63692e6f72672f546f6d5772696768742f51756572794275696c6465725048502e7376673f6272616e63683d6d6173746572)](https://travis-ci.org/TomWright/QueryBuilderPHP)[![Latest Stable Version](https://camo.githubusercontent.com/45deabde9da198eea91ca045351ad93cd49861269b560040d5f2f0eb7b75ec78/68747470733a2f2f706f7365722e707567782e6f72672f746f6d7772696768742f64622d71756572792d6275696c6465722f762f737461626c65)](https://packagist.org/packages/tomwright/db-query-builder)[![Total Downloads](https://camo.githubusercontent.com/4a95b0f1c41c276bfff050947eb62d2ec6e58da2ae7d550002b27532cc3426be/68747470733a2f2f706f7365722e707567782e6f72672f746f6d7772696768742f64622d71756572792d6275696c6465722f646f776e6c6f616473)](https://packagist.org/packages/tomwright/db-query-builder)[![Monthly Downloads](https://camo.githubusercontent.com/77c8fce53143741c1373045da14b8bcf431554857c372aac01c2e841537c3d20/68747470733a2f2f706f7365722e707567782e6f72672f746f6d7772696768742f64622d71756572792d6275696c6465722f642f6d6f6e74686c79)](https://packagist.org/packages/tomwright/db-query-builder)[![Daily Downloads](https://camo.githubusercontent.com/f35bcc2528a0b45a1a8bc62287e325d557631b24d34e3981171d0fedb327f29e/68747470733a2f2f706f7365722e707567782e6f72672f746f6d7772696768742f64622d71756572792d6275696c6465722f642f6461696c79)](https://packagist.org/packages/tomwright/db-query-builder)[![License](https://camo.githubusercontent.com/803324feec33e67fb9105ae539706f0f44f90fc652f7a978b3e1c0cbe87333e7/68747470733a2f2f706f7365722e707567782e6f72672f746f6d7772696768742f64622d71756572792d6275696c6465722f6c6963656e73652e737667)](https://packagist.org/packages/tomwright/db-query-builder)

Installation
------------

[](#installation)

```
composer install tomwright/db-query-builder

```

Query Builder
=============

[](#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.

This works well in large applications and search functionality.

Quick Examples
--------------

[](#quick-examples)

In all examples below:

- `$db` is an instance of [ExtendedPDO](https://github.com/TomWright/ExtendedPDO).
- `$builder` is an instance of `SqlQueryBuilder`. E.g. `$builder = new SqlQueryBuilder();`.

### SELECT

[](#select)

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

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

[](#update)

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

```
$query = $builder
    ->update()
    ->setValues('username', 'Tom')
    ->addRawValue('dt_modified', 'NOW()')
    ->setTable('users')
    ->addWhere('user_id', 5)
    ->buildQuery();

$db->dbQuery($query->getSql(), $query->getBinds());

```

Getting the SQL and Bind Parameters out of the Query Builder
------------------------------------------------------------

[](#getting-the-sql-and-bind-parameters-out-of-the-query-builder)

In order to get the SQL and bind parameters, you must have already run `buildQuery()` on the `Query` object.

```
$query->getSql();
// SELECT * FROM users WHERE username = :_where_username;

$query->getBinds();
// [':_where_username' => 'Tom']

```

Selecting the Query Type
------------------------

[](#selecting-the-query-type)

Any of the following methods are available to create your Query object.

```
$query = $builder->select(); // SELECT query
$query = new Query('DELETE'); // DELETE query
$query = new Query()->setType('UPDATE'); // UPDATE query

```

Choosing fields to SELECT
-------------------------

[](#choosing-fields-to-select)

The fields default to `['*']`.

```
$query->setFields(['something', 'something_else']);
$query->addField('another_field');

$query->getFields(); // ['something', 'something_else', 'another_field']

```

Choosing the table
------------------

[](#choosing-the-table)

```
$query->setTable('users');
$query->setTable('users u');

$query->getTable('users u');

```

Values to SET or UPDATE
-----------------------

[](#values-to-set-or-update)

Using the Query Builder here takes advantage of PDO bind parameters and makes you invulnerable to SQL Injection.

```
$query->addValue('users.username', 'Tom');

```

Raw Values
----------

[](#raw-values)

Using raw values will NOT use PDO bind parameters and so your SQL queries may be vulnerable to SQL Injection.

```
$query->addRawValue('users.dt_registered', 'NOW()');

```

ON DUPLICATE KEY UPDATE
-----------------------

[](#on-duplicate-key-update)

Sometimes you may need to use the `ON DUPLICATE KEY UPDATE` SQL syntax. This is achieved by doing the following.

```
$query->addOnDupeValue('users.username', 'Tom');

```

A full query may look something like this.

```
$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;

```

Joins
-----

[](#joins)

Create an instance of the [Join class](src/Join.php) and then add it to a Query.

```
$query
    ->setTable('users')
    ->addJoin(new Join('JOIN', 'codes', 'codes.user_id = users.user_id'));

```

You can also use aliases here.

```
$query
    ->setTable('users u')
    ->addJoin(new Join('JOIN', 'codes c', 'c.user_id = u.user_id'));

```

Where's
-------

[](#wheres)

Building WHERE statements using the Query Builder takes advantage of PDO bind parameters so your queries are protected against SQL Injection.

### Comparison Types

[](#comparison-types)

The default comparison is `=`.

```
$query->addWhere('users.username', 'Tom')

```

#### Custom Comparison

[](#custom-comparison)

You can easily override the default comparison by doing the following.

```
$query->addWhere('users.username !=', 'Tom')

```

### Raw SQL in where's

[](#raw-sql-in-wheres)

When you use raw SQL you will not benefit from PDO bind parameters and your queries may be vulnerable to SQL Injection.

```
$query->addRawWhere('users.dt_registered