PHPackages                             phossa/phossa-query - 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. phossa/phossa-query

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

phossa/phossa-query
===================

A SQL query builder library for PHP

1.0.4(9y ago)1391[3 PRs](https://github.com/phossa/phossa-query/pulls)MITPHPPHP >=5.4.0

Since May 8Pushed 9y ago2 watchersCompare

[ Source](https://github.com/phossa/phossa-query)[ Packagist](https://packagist.org/packages/phossa/phossa-query)[ Docs](https://github.com/phossa/phossa-query)[ RSS](/packages/phossa-phossa-query/feed)WikiDiscussions master Synced 2mo ago

READMEChangelog (5)Dependencies (1)Versions (9)Used By (0)

phossa-query
============

[](#phossa-query)

[![Build Status](https://camo.githubusercontent.com/705bd1a0ef9d6210539e03d7ab33c7712ba553b9221c03abd6318cf32b6bf182/68747470733a2f2f7472617669732d63692e6f72672f70686f7373612f70686f7373612d71756572792e7376673f6272616e63683d6d6173746572)](https://travis-ci.org/phossa/phossa-query)[![HHVM](https://camo.githubusercontent.com/311f64200d849d92a693609049a438722b03e26c05b38cc8f457a157137960e6/68747470733a2f2f696d672e736869656c64732e696f2f6868766d2f70686f7373612f70686f7373612d71756572792e7376673f7374796c653d666c6174)](http://hhvm.h4cc.de/package/phossa/phossa-query)[![Latest Stable Version](https://camo.githubusercontent.com/7b196b67a1f9f86317bb36aabb618057dd588dd76d19df1712cea40e8d5285a9/68747470733a2f2f696d672e736869656c64732e696f2f7061636b61676973742f767072652f70686f7373612f70686f7373612d71756572792e7376673f7374796c653d666c6174)](https://packagist.org/packages/phossa/phossa-query)[![License](https://camo.githubusercontent.com/5f6e3a923eb3e35737db1b5bbafa2bc022a2ff031d663dded38e8c28e0ddc211/68747470733a2f2f706f7365722e707567782e6f72672f70686f7373612f70686f7373612d71756572792f6c6963656e7365)](http://mit-license.org/)

**phossa-query** is a SQL query builder library with concise syntax for PHP. It supports Mysql, SQLite, Postgres, Sql server, Oracle etc.

It requires PHP 5.4 and supports PHP 7.0+, HHVM. It is compliant with [PSR-1](http://www.php-fig.org/psr/psr-1/ "PSR-1: Basic Coding Standard"), [PSR-2](http://www.php-fig.org/psr/psr-2/ "PSR-2: Coding Style Guide"), [PSR-4](http://www.php-fig.org/psr/psr-4/ "PSR-4: Autoloader").

Features
--------

[](#features)

- Support [SELECT](#select), [INSERT](#insert), [UPDATE](#update), [REPLACE](#replace), [DELETE](#delete), [CREATE TABLE](#create).
- Complex sql building with [expr()](#expr), [raw()](#raw), [before()](#before), [after()](#before) etc.
- Statement with positioned or named [parameters](#param).
- Beautiful output with different [settings](#settings).
- Ongoing support for different dialects like [`Mysql`](#mysql), `Sqlite` and more.

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

[](#installation)

Install via the [`composer`](https://getcomposer.org/) utility.

```
composer require "phossa/phossa-query=1.*"

```

or add the following lines to your `composer.json`

```
{
    "require": {
        "phossa/phossa-query": "^1.0.0"
    }
}
```

Usage
-----

[](#usage)

- Getting started

    Start with a query builder first, then the query.

    ```
    use Phossa\Query\Builder;
    use Phossa\Query\Dialect\Mysql;

    // a builder with mysql dialect,default 'users' table
    $users = new Builder(new Mysql(), 'users');

    // SELECT * FROM `users` LIMIT 10
    $sql = $users->select()->limit(10)->getStatement();

    // INSERT INTO `users` (`usr_name`) VALUES ('phossa')
    $sql = $users->insert(['usr_name' => 'phossa'])->getStatement();

    // A new builder (cloned) with default table 'sales'
    $sales = $users->table(['sales' => 's']);
    $query = $sales->select()->where('user_id', 12);

    // SELECT * FROM `sales` AS `s` WHERE `user_id` = ?
    $sql = $query->getStatement(['positionedParam' => true]);

    // value bindings: [12]
    $val = $query->getBindings();
    ```
- `SELECT`

    - Columns

        Columns can be specified in the `select()` or in `col()` (or with its alias `field()`).

        Column with optional alias name,

        ```
        // SELECT `user_name` AS `n` FROM `users`
        $query = $users->select('user_name', 'n');
        ```

        Multiple columns,

        ```
        // SELECT `id`, `user_name` AS `n` FROM `users`
        $query = $users->select()->col(['id', 'user_name' => 'n']);

        // same as above
        $query = $users->select()->col('id')->field('user_name', 'n');
        ```

        Raw mode,

        ```
        // SELECT COUNT(user_id) AS `cnt` FROM `users`
        $query = $users->select()->colRaw(['COUNT(user_id)' => 'cnt']);
        ```

        Common functions like `count()`, `min()`, `max()`, `avg()`, `sum()` and `sumDistinct()` can be used in the columns.

        ```
        // SELECT COUNT(`user_id`) AS `cnt`, MAX(`user_id`) AS `max_id` FROM `users`
        $query = $users->select()->count('user_id', 'cnt')->max('user_id', 'max_id');
        ```

        Generic functions by using `func($template, $colName, $colAlias)`,

        ```
        // SELECT CONCAT(`user_name`, "XXX") AS `new_name` FROM `users`
        $query = $users->select()->func('CONCAT(%s, "XXX")', 'user_name', 'new_name');
        ```
    - Distinct

        `DISTINCT` can be specified with `distinct()`

        ```
        // SELECT DISTINCT `user_alias` FROM `users`
        $query = $users->select('user_alias')->distinct();
        ```
    - From

        `FROM` can used with builder object or select object.

        Use `select(false)` to ignore default table from the builder,

        ```
        // SELECT * FROM `sales` AS `s`
        $query = $users->select(false)->from('sales', 's');
        ```

    ```

      Builder tables are carried over,

      ```php
      // SELECT * FROM `users`, `sales`
      $query = $users->select()->from('sales');

    ```

    Multiple tables (with aliases) supported,

    ```
    // SELECT * FROM `users` AS `u`, `accounts` AS `a`
    $query = $users->select()->from(['users' => 'u', 'accounts' => 'a']);
    ```

    Subqueries can be used in `from()`,

    ```
    // builder without default table[s]
    $builder = $users->table(false);

    // SELECT * FROM (SELECT `user_id` FROM `oldusers`) AS `u`
    $query = $builder->select()->from(
        $builder->select('user_id')->from('oldusers'), 'u'
    );
    ```

    - Group by

        Single `GROUP BY`,

        ```
        // SELECT `group_id`, COUNT(*) AS `cnt` FROM `users` GROUP BY `group_id`
        $query = $users->select()->col('group_id')->count('*', 'cnt')->groupBy('group_id');
        ```

        Multiple `groupBy()` and raw mode can be used,

        ```
        // SELECT `group_id`, `age`, COUNT(*) AS `cnt` FROM `users` GROUP BY `group_id`, age ASC
        $query = $users->select()->col('group_id')->col('age')->count('*', 'cnt')
            ->groupBy('group_id')->groupByRaw('age ASC');
        ```
    - Join

        Join with another table with same column name

        ```
        // SELECT * FROM `users` INNER JOIN `accounts` ON `users`.`id` = `accounts`.`id`
        $query = $users->select()->join('accounts', 'id');
        ```

        Specify alias for the join table,

        ```
        // SELECT * FROM `users` INNER JOIN `accounts` AS `a` ON `users`.`id` = `a`.`id`
        $query = $users->select()->join('accounts a', 'id');
        ```

        Join table with different column name,

        ```
        // SELECT * FROM `users` INNER JOIN `accounts` AS `a` ON `users`.`id` = `a`.`user_id`
        $query = $users->select()->join('accounts a', 'id', 'user_id');
        ```

        Join with operator specified,

        ```
        // SELECT * FROM `users` INNER JOIN `accounts` AS `a` ON `users`.`id`  `a`.`user_id`
        $query = $users->select()->join('accounts a', 'id', '', 'user_id');
        ```

        Multiple joins,

        ```
        // SELECT * FROM `users`
        // INNER JOIN `sales` AS `s` ON `users`.`uid` = `s`.`uid`
        // INNER JOIN `order` AS `o` ON `users`.`uid` = `o`.`o_uid`
        $query = $users->select()
                    ->join('sales s', 'uid', '=', 'uid')
                    ->join('order o', 'uid', 'o_uid')
                    ->getStatement();
        ```

        Subqueries in join,

        ```
        // SELECT * FROM `users` INNER JOIN (SELECT `uid` FROM `oldusers`) AS `x`
        // ON `users`.`uid` = `x`.`uid`
        $query = $users->select()->join(
            $builder->select('uid')->from('oldusers')->alias('x'),
            'uid'
        );
        ```

        Other joins `outerJoin()`, `leftJoin()`, `leftOuterJoin()`, `rightJoin()`, `rightOuterJoin()`, `fullOuterJoin()`, `crossJoin()` are supported. If want to use your own join, `realJoin()` is handy.

        ```
        // SELECT * FROM `users` OUTER JOIN `accounts` AS `a` ON `users`.`id` = `a`.`id`
        $query = $users->select()->outerJoin('accounts a', 'id');

        // SELECT * FROM `users` NATURAL JOIN `accounts` AS `a` ON `users`.`id` = `a`.`id`
        $query = $users->select()->realJoin('NATURAL', 'accounts a', 'id');
        ```
    - Limit

        `LIMIT` and `OFFSET` are supported,

        ```
        // SELECT * FROM `users` LIMIT 30 OFFSET 10
        $query = $users->select()->limit(30, 10);

        // SELECT * FROM `users` LIMIT 20 OFFSET 15
        $query = $users->select()->limit(20)->offset(15);
        ```

        Or use `page($pageNum, $pageLength)`,

        ```
        // SELECT * FROM `users` LIMIT 30 OFFSET 60
        $query = $users->select()->page(3, 30);
        ```
    - Order by

        Order by ASC or DESC

        ```
        // SELECT * FROM `users` ORDER BY `age` ASC, `score` DESC
        $query = $users->select()->orderByAsc('age')->orderByDesc('score');
        ```

        Or raw mode

        ```
        // SELECT * FROM `users` ORDER BY age ASC, score DESC
        $query = $users->select()->orderByRaw('age ASC, score DESC');
        ```
    - Where

        Simple wheres,

        ```
        // SELECT * FROM `users` WHERE age > 18
        $query = $users->select()->where('age > 18');

        // SELECT * FROM `users` WHERE `age` = 18
        $query = $users->select()->where('age', 18);

        // SELECT * FROM `users` WHERE `age` < 18
        $query = $users->select()->where('age', '', 18], 'gender' => 'male']);

        // same as above
        $query = $users->select()->where('age', '>', 18)->where('gender','male');
        ```

        Complex where,

        ```
        // SELECT * FROM `users` WHERE (`id` = 1 OR (`id` < 20 OR `id` > 100))
        // OR `name` = 'Tester'
        $query = $users->select()->where(
                    $builder->expr()->where('id', 1)->orWhere(
                        $builder->expr()->where('id', '', 100)
                    )
                 )->orWhere('name', 'Tester');
        ```

        Raw mode,

        ```
        // SELECT * FROM `users` WHERE age = 18 OR score > 90
        $query = $users->select()->whereRaw('age = 18')->orWhereRaw('score > 90');
        ```

        with `NOT`,

        ```
        // SELECT * FROM `users` WHERE NOT `age` = 18 OR NOT `score` > 90
        $query = $users->select()->whereNot('age', 18)->orWhereNot('score', '>', 90);
        ```

        Where `IN` and `BETWEEN`

        ```
        // SELECT * FROM `users` WHERE `age` IN (10,12,15,18,20) OR `score` NOT BETWEEN 90 AND 100
        $query = $users->select()->whereIn('age', [10,12,15,18,20])
                ->orWhereNotBetween('score', 90, 100);
        ```

        Where `IS NULL` and `IS NOT NULL`

        ```
        // SELECT * FROM `users` WHERE `age` IS NULL OR `score` IS NOT NULL
        $query = $users->select()->whereNull('age')->orWhereNotNull('score');
        ```

        Exists,

        ```
        $qry1  = $users->select('user_id')->where('age', '>', 60);
        $sales = $users->table('sales');

        // SELECT * FROM `sales` WHERE EXISTS (SELECT `user_id` FROM `users`
        // WHERE `age` > 60)
        $sql = $sales->select()->whereExists($qry1)->getStatement();
        ```
    - Having

        Similar to `WHERE` clause,

        ```
        // SELECT * FROM `users` HAVING `age` = 10 OR `level` > 20
        $query = $users->select()->having('age', 10)->orHaving('level', '>', 20);
        ```
    - Union

        ```
        // SELECT * FROM `users` UNION SELECT * FROM `oldusers1`
        // UNION ALL SELECT `user_id` FROM `oldusers2`
        $sql = $users->select()
                ->union()
                    ->select()->from('oldusers1')
                ->unionAll()
                    ->select('user_id')->from('oldusers2')
                    ->getStatement()
        ```
- `INSERT`

    Single insert statement,

    ```
    // INSERT INTO `users` (`uid`, `uname`) VALUES (2, 'phossa')
    $sql = $users->insert()->set('uid', 2)->set('uname', 'phossa')
        ->getStatement();

    // same as above, with array notation
    $sql = $users->insert()->set(['uid' => 2, 'uname' => 'phossa'])
        ->getStatement();
    ```

    Multiple data rows,

    ```
    // INSERT INTO `users` (`uid`, `uname`) VALUES (2, 'phossa'), (3, 'test')
    $query = $users->insert()
              ->set(['uid' => 2, 'uname' => 'phossa'])
              ->set(['uid' => 3, 'uname' => 'test']);
    ```

    Insert with `DEFAULT` values

    ```
    // INSERT INTO `users` (`uid`, `uname`, `phone`)
    // VALUES (2, 'phossa', DEFAULT), (3, 'test', '1234')
    $query = $users->insert([
        ['uid' => 2, 'uname' => 'phossa'],
        ['uid' => 3, 'uname' => 'test', 'phone' => '1234']
    ]);
    ```

    Insert `NULL` instead of default values,

    ```
    // INSERT INTO `users` (`uid`, `uname`, `phone`)
    // VALUES (2, 'phossa', NULL), (3, 'test', '1234')
    $sql = $query->getStatement(['useNullAsDefault' => true]);
    ```

    Insert with `SELECT` subquery,

    ```
    // INSERT INTO `users` (`uid`, `uname`)
    // SELECT `user_id`, `user_name` FROM `oldusers`
    $query = $users->insert()->set(['uid', 'uname'])
                 ->select(['user_id', 'user_name'])
                 ->from('oldusers');
    ```
- `UPDATE`

    Common update statement,

    ```
    // UPDATE `users` SET `user_name` = 'phossa' WHERE `user_id` = 3
    $query = $users->update(['user_name' => 'phossa'])->where('user_id', 3);

    // UPDATE `users` SET `user_name` = 'phossa', `user_addr` = 'xxx'
    // WHERE `user_id` = 3
    $query = $users->update()->set('user_name','phossa')
        ->set('user_addr', 'xxx')->where('user_id', 3);
    ```

    With `Mysql` extensions,

    ```
    // UPDATE IGNORE `users` SET `user_id` = user_id + 10 ORDER BY `user_id` ASC LIMIT 10
    $query = $users->update()->addHint('IGNORE')->set('user_id', $builder->raw('user_id + 10'))
        ->orderByASC('user_id')->limit(10);
    ```
- `REPLACE`

    Mysql version of replace,

    ```
    // REPLACE LOW_PRIORITY INTO `users` (`user_id`, `user_name`) VALUES (3, 'phossa')
    $query = $users->replace(['user_id' => 3, 'user_name' => 'phossa'])
        ->addHint('low_priority');
    ```

    Sqlite version of replace,

    ```
    // INSERT INTO `users` (`user_id`, `user_name`) VALUES (3, 'phossa')
    // ON CONFLICT REPLACE
    $query = $users->replace(['user_id' => 3, 'user_name' => 'phossa']);
    ```
- `DELETE`

    Single table deletion,

    ```
    // DELETE FROM `users` WHERE `user_id` > 10 ORDER BY `user_id` ASC LIMIT 10
    $query = $users->delete()->where('user_id', '>', 10)
        ->orderByAsc('user_id')->limit(10);
    ```

    Multiple tables deletion

    ```
    // DELETE `users`.* FROM `users` AS `u` INNER JOIN `accounts` AS `a`
    // ON `u`.`user_id` = `a`.`user_id` WHERE `a`.`total_amount` < 0
    $query = $users->delete('users')->from('users', 'u')
        ->join('accounts a', 'user_id')->where('a.total_amount', '' , 60)->orWhere(
            $builder->expr()->where('age', '>', 55)->where('gender', 'female')
        )
    );
    ```

    Join with complex `ON`,

    ```
    $builder = $users->table(false);

    // SELECT * FROM `users` INNER JOIN `sales`
    // (ON `users`.`uid` = `sales`.`s_uid` OR `users`.`uid` = `sales`.`puid`)
    $sql = $users->select()->join('sales',
        $builder->expr()->on('users.uid', 'sales.s_uid')->orOn('users.uid', 'sales.puid')
    )->getStatement();
    ```
- `raw()`

    Raw string bypass the quoting and escaping,

    ```
    // SELECT id FROM "students" WHERE "time" = NOW()
    $query = $builder->select()->field($builder->raw("id"))
        ->from("students")->where("time", $builder->raw('NOW()'));
    ```

    Raw string with parameters,

    ```
    // SELECT * FROM "students" WHERE "age" IN RANGE(1, 1.2)
    $query = $builder->select()->from("students")->where("age", "IN",
        $builder->raw('RANGE(?, ?)', 1, 1.2));
    ```
- `before()` and `after()`

    Sometimes, non-standard SQL wanted and no methods found. `before()` and `after()` will come to rescue.

    ```
    // INSERT IGNORE INTO "users" ("id", "name") VALUES (3, 'phossa')
    // ON DUPLICATE KEY UPDATE id=id+10
    $query = $users->insert()->set('id', 3)->set('name', 'phossa')
        ->before('INTO', 'IGNORE')
        ->after('VALUES', 'ON DUPLICATE KEY UPDATE id=id+?', 10);
    ```
- Parameters

    *phossa-query* can return statement for driver to prepare and use the `getBindings()` to get the values to bind.

    ```
    $query = $users->select()->where("user_id", 10);

    // SELECT * FROM "users" WHERE "user_id" = ?
    $sql = $query->getPositionedStatement();

    // values to bind: [10]
    $val = $query->getBindings();
    ```

    Or named parameters,

    ```
    $query = $users->select()->where("user_name", ':name');

    // SELECT * FROM "users" WHERE "user_name" = :name
    $sql = $query->getNamedStatement();
    ```
- Settings

    Settings can be applied to `$builder` at instantiation,

    ```
    $users = new Builder(new Mysql(), 'users', ['autoQuote' => false]);
    ```

    Or applied when output with `getStatement()`,

    ```
    $sql = $users->select()->getStatement(['autoQuote' => false]);
    ```

    List of settings,

    - `autoQuote`: boolean. Quote db identifier or not.
    - `positionedParam`: boolean. Output with positioned parameter or not.
    - `namedParam`: boolean. Output with named parameter or not.
    - `seperator`: string, default to ' '. Seperator between clauses.
    - `indent`: string, default to ''. Indent prefix for clauses.
    - `escapeFunction`: callabel, default to `null`. Function used to quote and escape values.
    - `useNullAsDefault`: boolean.

Dialects
--------

[](#dialects)

- Mysql

Dependencies
------------

[](#dependencies)

- PHP &gt;= 5.4.0
- phossa/phossa-shared 1.\*

License
-------

[](#license)

[MIT License](http://mit-license.org/)

###  Health Score

28

—

LowBetter than 54% of packages

Maintenance20

Infrequent updates — may be unmaintained

Popularity10

Limited adoption so far

Community9

Small or concentrated contributor base

Maturity63

Established project with proven stability

 Bus Factor1

Top contributor holds 100% of commits — single point of failure

How is this calculated?**Maintenance (25%)** — Last commit recency, latest release date, and issue-to-star ratio. Uses a 2-year decay window.

**Popularity (30%)** — Total and monthly downloads, GitHub stars, and forks. Logarithmic scaling prevents top-heavy scores.

**Community (15%)** — Contributors, dependents, forks, watchers, and maintainers. Measures real ecosystem engagement.

**Maturity (30%)** — Project age, version count, PHP version support, and release stability.

###  Release Activity

Cadence

Every ~3 days

Total

5

Last Release

3640d ago

### Community

Maintainers

![](https://www.gravatar.com/avatar/f8140b30658c77b72e1596e6cbc4cf1cd9b308f636a8adf125d09af74ee52124?d=identicon)[phossa](/maintainers/phossa)

---

Top Contributors

[![phossa](https://avatars.githubusercontent.com/u/8499165?v=4)](https://github.com/phossa "phossa (46 commits)")

---

Tags

sqlqueryquery builderphossa

### Embed Badge

![Health badge](/badges/phossa-phossa-query/health.svg)

```
[![Health](https://phpackages.com/badges/phossa-phossa-query/health.svg)](https://phpackages.com/packages/phossa-phossa-query)
```

###  Alternatives

[nilportugues/sql-query-builder

An elegant lightweight and efficient SQL QueryInterface BuilderInterface supporting bindings and complicated query generation.

425239.4k6](/packages/nilportugues-sql-query-builder)[opis/database

A database abstraction layer over PDO, that provides a powerful and intuitive query builder, bundled with an easy to use schema builder

10184.2k3](/packages/opis-database)[jasny/persist-sql-query

SQL Query builder and parser

33486.0k4](/packages/jasny-persist-sql-query)[bentools/where

PHP7.1 Fluent, immutable SQL query builder. Connectionless, framework-agnostic, no dependency.

125.2k2](/packages/bentools-where)

PHPackages © 2026

[Directory](/)[Categories](/categories)[Trending](/trending)[Changelog](/changelog)[Analyze](/analyze)