PHPackages                             phputil/sql - 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. phputil/sql

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

phputil/sql
===========

🔥 A portable, highly readable SQL query builder for PHP

v0.24.1(6mo ago)010MITPHPPHP >=8.1CI passing

Since Oct 2Pushed 6mo agoCompare

[ Source](https://github.com/thiagodp/phputil-sql)[ Packagist](https://packagist.org/packages/phputil/sql)[ RSS](/packages/phputil-sql/feed)WikiDiscussions main Synced 1mo ago

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

[![Packagist Version](https://camo.githubusercontent.com/ef3f991e68f629f63bf57e4141c9f6594f90c8c79f9d811b114fc5d42532838c/68747470733a2f2f696d672e736869656c64732e696f2f7061636b61676973742f762f7068707574696c2f73716c3f7374796c653d666f722d7468652d626164676526636f6c6f723d677265656e)](https://github.com/thiagodp/sql/commits/main/)[![GitHub License](https://camo.githubusercontent.com/73aa02d2e1aa6a514384df2f2e3bf6f446987653d661448a1204748faafbbd87/68747470733a2f2f696d672e736869656c64732e696f2f6769746875622f6c6963656e73652f74686961676f64702f7068707574696c2d73716c3f7374796c653d666f722d7468652d626164676526636f6c6f723d677265656e)](https://github.com/thiagodp/phputil-sql/blob/main/LICENSE)[![Build](https://github.com/thiagodp/phputil-sql/actions/workflows/ci.yml/badge.svg?style=for-the-badge&color=green)](https://github.com/thiagodp/phputil-sql/actions)

phputil/sql
===========

[](#phputilsql)

> 🔥 A portable, highly readable SQL query builder for PHP

⚠️ **Work-In-Progress!** ⚠️

Features:

- 🎯 **Cross-database SQL support with the same API**: MySQL, PostgreSQL, SQLite, Oracle, and SQLServer.
- 🚀 No database or external dependencies - not even [PDO](https://www.php.net/manual/en/book.pdo.php).
- 🔥 Fluent interface, strongly typed, SQL-like syntax.
- 🛟 Automatically quote columns and table names (e.g. backticks in MySQL).
- 🪢 Support to complex queries.
- 🛠️ Include utility functions for [aggregation](#aggregate-functions), [string](#string-functions), [date and time](#date-and-time-functions), [null](#null-handling-function), and [math](#math-functions).

See the [Roadmap](#roadmap)

Use cases
---------

[](#use-cases)

This library is particularly useful for:

- Creating queries that can be used with different relational databases without the need of (bloated) ORM frameworks.
    - Migration is usually achieved by changing a single line of code in your codebase.
- Writing readable, typo-free SQL statements.
- Building complex WHERE clauses (e.g. for filtering content) without the need of concatenating strings.
- Migrating data from different relational databases.

Install
-------

[](#install)

> Requires PHP 8.1+

```
composer require phputil/sql
```

**Note**: Get updates via `composer require phputil/sql` since [Composer is not able to get updates automatically](https://getcomposer.org/doc/articles/versions.md#caret-version-range-) via `composer update` for packages in version `0.x` (pre-1.0).

Basic Usage
-----------

[](#basic-usage)

### Queries

[](#queries)

ℹ️ Use the function `select()` for creating a query and the method `end()` for finishing it.

```
require_once 'vendor/autoload.php';
use phputil\sql\{SQLType};
use function phputil\sql\{select};

// end() returns a Select object
$sql = select()->from( 'example' )->end();

// ... which is convertible to string
echo $sql; // SELECT * FROM example

// ... and it's convertible to specific databases via toString()
echo $sql->toString( SQLType::MYSQL );      // SELECT * FROM `example`
echo $sql->toString( SQLType::SQLSERVER );  // SELECT * FROM [example]

// Converting a query to a database-specific string
echo select()->from( 'example' )->end()->toString( SQLType::POSTGRESQL ); // SELECT * FROM "example"
echo select()->from( 'example' )->endAsString( SQLType::POSTGRESQL ); // SELECT * FROM "example"

// 👉 Setting the default SQL/database type globally with SQL::useXXX methods.
SQL::useSQLite();

echo $sql; // SELECT * FROM `example`

// But you can still convert it into another database
echo $sql->toString( SQLType::ORACLE ); // SELECT * FROM "example"
```

🆒 Okay, let's build a more complex query.

```
require_once 'vendor/autoload.php';
use phputil\sql\{SQL, SQLType};
use function phputil\sql\{select, col};

SQL::useMySQL();

// Say, all products with price between 100 and 999.999, quantity above 0,
// ordered by SKU and with a paginated result

$sql = select( 'p.sku', 'p.description', 'p.quantity', 'u.name AS unit', 'p.price' )
    ->from( 'product p' )
    ->leftJoin( 'unit u' )
        ->on( col( 'u.id' )->equalTo( col( 'p.unit_id' ) ) )
    ->where(
        col( 'p.price' )->between( 100.00, 999.99 )
        ->and( col( 'p.quantity' )->greaterThan( 0 ) )
    )
    ->orderBy( 'p.sku' )
    ->limit( 10 ) // limit to 10 rows
    ->offset( 20 ) // skip the first 20 rows (e.g., 3rd page in 10-row pagination)
    ->end();

echo $sql, PHP_EOL;

// It generates:
//
// SELECT `p`.`sku`, `p`.`description`, `p`.`quantity`, `u`.`name` AS `unit`, `p`.`price`
// FROM `product` `p`
// LEFT JOIN `unit` `u`
//   ON `u`.`id` = `p`.`unit_id`
// WHERE `p`.`price` BETWEEN 100 AND 999.99 AND `p`.`quantity` > 0
// ORDER BY `p`.`sku` ASC
// LIMIT 10
// OFFSET 20

echo $sql->toString( SQLType::ORACLE );

// Now it generates:
//
// SELECT "p"."sku", "p"."description", "p"."quantity", "u"."name" AS "unit", "p"."price"
// FROM "product" "p"
// LEFT JOIN "unit" "u"
//  ON "u"."id" = "p"."unit_id"
// WHERE "p"."price" BETWEEN 100 AND 999.99 AND "p"."quantity" > 0
// ORDER BY "p"."sku" ASC
// OFFSET 20 ROWS
// FETCH NEXT 10 ROWS ONLY
```

🤔 Right, but what about SQL Injection?

🆗 Just use parameters - with [`param()`](#param) - for any input values.

👉 Your database must be able to handle parameters in SQL commands. Example with PDO:

```
// Getting an optional filter from the URL: /products?sku=123456
$sku = htmlspecialchars( $_GET[ 'sku' ] ?? '' );

// Example with named parameters using PDO
$sql = select( 'sku', 'description', 'price' )->from( 'product' );

if ( ! empty( $sku ) ) {
    $sql = $sql->where(
        col( 'sku' )->equal( param( 'sku' ) ) // 👈 Named parameter
    );
}

$pdo = new PDO( 'sqlite:example.db' );
$pdoStatement = $pdo->prepare( $sql );
$pdoStatement->execute( [ 'sku' => $sku ] ); // 👈 Value only here
// ...
```

➡️ See more examples in the [API section](#api) or in the folder [examples](./examples/).

Data manipulation
-----------------

[](#data-manipulation)

ℹ️ Data manipulation functions do not require to use the `end()` method (it is optional).

1️⃣ Use `deleteFrom()` for creating a `DELETE` command. Example:

```
$command = deleteFrom( 'user' )->where( col( 'id' )->equalTo( param() ) )->end();
// DELETE FROM `user` WHERE `id` = ?

```

2️⃣ Use `insertInto()` for creating an `INSERT` command. Examples:

```
// Insert with field names and named parameters
$command = insertInto( 'user', [ 'name', 'username', 'password' ] )
    ->values(
        [ param( 'name' ), param( 'username' ), param( 'password' ) ]
    )->end();
// INSERT INTO `user` (`name`, `username`, `password`)
// VALUES (:name, :username, :password)

// Insert with values only
$command = insertInto( 'user' )->values(
    [ 1, 'Alice Foe', 'alice', 'aL1C3_passW0rD' ],
    [ 2, 'Bob Doe', 'bob', 'just_b0b' ],
)->end();
// INSERT INTO `user`
// VALUES
// (1, 'Alice Foe', 'alice', 'aL1C3_passW0rD'),
// (2, 'Bob Doe', 'bob', 'just_b0b')

// Insert from select
$command = insertInto( 'user', [ 'name', 'username', 'password' ],
    select( 'name', 'nickname', 'ssn' )->from( 'customer' )->end()
)->end();
// INSERT INTO `user` (`name`, `username`, `password`)
// SELECT `name`, `nickname`, `ssn` FROM `customer`
```

3️⃣ Use `update()` for creating an `UPDATE` command. Examples:

```
// Update with anonymous parameter and function
$command = update( 'user' )
    ->set( [ 'password' => param(), 'last_update' => now() ] )
    ->where( col( 'id' )->equalTo( 123 ) )
    ->endAsString( SQLType::MYSQL );
// UPDATE `user`
// SET `password` = ?, `last_update` = NOW()
// WHERE `id` = 123

$command = update( 'example' )
    ->set( [ 'a' => 10, 'b' => 'b + 1', 'c' => 'c + c * 50/100', 'd' => "'Hello'", 'e' => val( 'World' ) ] )
    ->where( col( 'id' )->equalTo( 1 ) )
    ->endAsString( SQLType::MYSQL );
// UPDATE `example`
// SET `a` = 10, `b` = `b` + 1, `c` = `c` + `c` * 50/100, `d` = 'Hello', `e` = 'World'
// WHERE `id` = 1
```

API
---

[](#api)

⚠️ **Note**: Most examples of generated queries are in MySQL. ⚠️

Index:

- [Types](#types)
    - [`SQL`](#sql), [`SQLType`](#sqltype)
- [Basic functions](#basic-functions)
    - [`select`](#select), [`selectDistinct`](#selectdistinct)
    - [`insertInto`](#insertinto), [`update`](#update), [`deleteFrom`](#deletefrom)
    - [`col`](#col), [`val`](#val), [`param`](#param), [`wrap`](#wrap), [`not`](#not)
- [Logic utilities](#logic-utilities)
    - [`andAll`](#andall), [`orAll`](#orall)
- [Ordering utilities](#ordering-utilities)
    - [`asc`](#asc), [`desc`](#desc)
- [Date and time functions](#date-and-time-functions)
    - [`now`](#now), [`date`](#date), [`time`](#time), [`extract`](#extract), [`diffInDays`](#diffindays), [`addDays`](#adddays), [`subDays`](#subdays), [`dateAdd`](#dateadd), [`dateSub`](#datesub)
- [String functions](#string-functions)
    - [`upper`](#upper), [`lower`](#lower), [`substring`](#substring), [`concat`](#concat), [`length`](#length), [`bytes`](#bytes)
- [Null handling function](#null-handling-function)
    - [`ifNull`](#ifnull)
- [Math functions](#math-functions)
    - [`abs`](#abs), [`round`](#round), [`ceil`](#ceil), [`floor`](#floor), [`power`](#power), [`sqrt`](#sqrt), [`sin`](#sin), [`cos`](#cos), [`tan`](#tan)

### Types

[](#types)

#### `SQLType`

[](#sqltype)

`SQLType` is an enum type with these values: `NONE`, `MYSQL`, `POSTGRESQL`, `SQLITE`, `ORACLE`, and `SQLSERVER`.

Example:

```
use phputil\sql\{SQLType};
use function phputil\sql\{select};

echo select()->from( 'example' )->endAsString( SQLType::NONE );
// SELECT * FROM example
```

#### `SQL`

[](#sql)

`SQL` is a class with static attributes that keeps the default SQL type for queries.

```
use phputil\sql\{SQL};

echo SQL::$type; // Get the current database type - by default, it is SQLType::NONE

// The following methods change SQL::$type
SQL::useNone(); // No specific SQL type - that is, change to SQLType::NONE
SQL::useMySQL(); // Change to SQLType::MYSQL
SQL::usePostgreSQL(); // Change to SQLType::POSTGRESQL
SQL::useSQLite(); // Change to SQLType::SQLITE
SQL::useOracle(); // Change to SQLType::ORACLE
SQL::useSQLServer(); // Change to SQLType::SQLSERVER
```

### Basic functions

[](#basic-functions)

```
// 👉 Make sure to declare their usage. Example:
use function phputil\sql\{select, col, val, param, wrap};
```

#### `select`

[](#select)

Create a selection. Examples:

```
$sql = select()->from( 'user' )->end();
// SELECT * FROM `user`

$sql = select( 'name', 'email' )
    ->from( 'user' )
    ->where( col( 'id' )->equalTo( 123 ) )
    ->end();
// SELECT `name`, `email` FROM `user` WHERE `id` = 123
```

👉 `from()` returns a `From` object with the following methods:

- `join( string $table ): Join`
- `innerJoin( string $table ): Join`
- `leftJoin( string $table ): Join`
- `rightJoin( string $table ): Join`
- `fullJoin( string $table ): Join`
- `crossJoin( string $table ): From`
- `naturalJoin( string $table ): From`
- `where( Condition $condition ): From`
- `whereExists( Select $select ): From`
- `groupBy( string ...$columns ): From`
- `having( Condition $condition ): From`
- `orderBy( string ...$columns ): From`
- `union( Select $select ): From`
- `unionDistinct( Select $select ): From`

Example with `having`:

```
echo select( count( 'id' ), 'country' )
    ->from( 'customer' )
    ->groupBy( 'country' )
    ->having( val( count( 'id' ) )->greaterThan( 5 ) )
    ->orderBy( desc( count( 'id' ) ) )
    ->endAsString( SQLType::MYSQL );

// SELECT COUNT(`id`), `country`
// FROM `customer`
// GROUP BY `country`
// HAVING COUNT(`id`) > 5
// ORDER BY COUNT(`id`) DESC
```

#### `selectDistinct`

[](#selectdistinct)

Create a distinct selection. It can receive one or more columns. Examples:

```
$sql = selectDistinct( 'name' )
    ->from( 'customer' )
    ->where( col( 'name' )->like( 'John%' ) )
    ->end();
// SELECT DISTINCT `name` FROM `customer` WHERE `name` LIKE 'John%'
```

#### `insertInto`

[](#insertinto)

`insertInto( string $table, string[] $fields = [], ?Select $select = null )` creates an `INSERT` command.

```
// With no fields declared, but they are: id, name, email
$command = insertInto( 'contact' )
    ->values(
        [ 1, 'John Doe', 'john@doe.com' ],
        [ 2, 'Suzan Foe', 'suzan@foe.com' ],
    )->end();
// INSERT INTO `contact`
// VALUES
// (1, 'John Doe', 'john@doe.com'),
// (2, 'Suzan Foe', 'suzan@foe.com')

// With fields declared, considering an auto-incremental id
$command = insertInto( 'contact', [ 'name', 'email' ] )
    ->values(
        [ 'John Doe', 'john@doe.com' ],
        [ 'Suzan Foe', 'suzan@foe.com' ],
    )->end();
// INSERT INTO `contact` (`name`, `email`)
// VALUES
// ('John Doe', 'john@doe.com'),
// ('Suzan Foe', 'suzan@foe.com')

// With anonymous parameters
$command = insertInto( 'contact', [ 'name', 'email' ] )
    ->values(
        [ param(), param() ]
    )->end();
// INSERT INTO `contact` (`name`, `email`) VALUES (?, ?)

// With named parameters
$command = insertInto( 'contact', [ 'name', 'email' ] )
    ->values(
        [ param( 'name' ), param( 'email' ) ]
    )->end();
// INSERT INTO `contact` (`name`, `email`) VALUES (:name, :email)

// From selection
$command = insertInto( 'contact', [ 'name', 'email' ],
        select( 'name', 'email' )->from( 'customer' )
            ->where( col( 'email' )->endWith( '@acme.com' ) )
            ->end()
    )->end();
// INSERT INTO `contact` (`name`, `email`)
// SELECT `name`, `email` FROM `customer`
// WHERE `email` LIKE '%@acme.com'
```

#### `update`

[](#update)

`update` creates an `UPDATE` command. Example:

```
$command = update( 'user' )
    ->set(
        [ 'password' => val( '123456' ), 'last_update' => now() ]
    )->where(
        col( 'id' )->equalTo( 123 )
    )->end();
// UPDATE `user`
// SET `password` = '123456', `last_update` = NOW()
// WHERE `id` = 123
```

#### `deleteFrom`

[](#deletefrom)

`deleteFrom` creates a `DELETE` command. Example:

```
// With anonymous parameter
$command = deleteFrom( 'user' )
    ->where( col( 'id' )->equalTo( param() ) )
    ->end();
// DELETE FROM `user` WHERE `id` = ?

// With named parameter
$command = deleteFrom( 'user' )
    ->where( col( 'id' )->equalTo( param( 'id' ) ) )
    ->end();
// DELETE FROM `user` WHERE `id` = :id
```

#### `col`

[](#col)

`col` makes a column comparison and makes sure that the column is quoted appropriately. Examples:

```
$sql = select( 'total' )->from( 'sale' )->where( col( 'id' )->equalTo( 123 ) )->end();
// SELECT `total` FROM `sale` WHERE `id` = 123

$sql = select( 'id' )->from( 'product' )->where( col( 'qty' )->lessThan( col( 'min_qty' ) ) )->end();
// SELECT `id` FROM `product` WHERE `qty` < `min_qty`

$sql = select( 'name' )->from( 'product' )->where( col( 'special' )->isTrue() )->end();
// SELECT `name` FROM `product` WHERE `special` IS TRUE

$sql = select( 'id' )->from( 'sale' )->where( col( 'customer_id' )->in( [ 1234, 4567, 7890 ] ) )->end();
// SELECT `id` FROM `sale` WHERE `customer_id` IN (1234, 4567, 7890)

// Sub-select
$sql = select( 'id' )->from( 'sale' )->where( col( 'customer_id' )->in(
    select( 'id' )->from( 'customer' )->where( col( 'salary' )->greaterThan( 100_000 ) )
) )->end();
// SELECT `id` FROM `sale` WHERE `customer_id` IN (SELECT `id` FROM `customer` WHERE `salary` > 100000)
```

`col` returns the following comparison methods:

- `equalTo( $x )` for `=`
- `notEqualTo( $x )` or `differentFrom( $x )` for ``
- `lessThan( $x )` for `=`
- `like( $value )` for `LIKE`
- `startWith( $value )` for `LIKE` with `%` at the beginning of the value
- `endWith( $value )` for `LIKE` with `%` at the end of the value
- `contain( $value )` for `LIKE` with `%` around the value
- `between( $min, $max )` for `BETWEEN` with a minimum and a maximum value
- `in( $selectionOrArray )` for a sub select statement or an array of values
- `isNull()` for `IS NULL`
- `isNotNull()` for `IS NOT NULL`
- `isTrue()` for `IS TRUE`
- `isFalse()` for `IS FALSE`

ℹ️ **Notes**:

- Methods `startWith`, `endWith`, and `contain` produce a `LIKE` expression that adds `%` to the receive value. However, when an anonymous (`?`) or a named (`:name`) parameter is received by them, **they will not add `%`**, and you must add `%` manually to the parameter values.
- In Oracle databases, the methods `isTrue()` and `isFalse()` are supported from Oracle version `23ai`. In older versions, you can use `equalTo(1)` and `equalTo(0)` respectively, for the same results.

👉 `col` can also be used for creating aliases, with the `as` method. For instance, these three examples are equivalent:

```
$sql = select( col( 'long_name' )->as( 'l' ) );
$sql = select( col( 'long_name AS l' ) );
$sql = select( 'long_name AS l' );
```

#### `val`

[](#val)

`val( $value )` allows a value to be in the left side of a comparison. Example:

```
$sql = select( 'total' )->from( 'sale' )->where( val( 123 )->equalTo( col( 'id' ) ) )->end();
// SELECT `total` FROM `sale` WHERE 123 = `id`
```

ℹ️ **Note**: `val` returns the same comparison operators as [`col`](#col).

`val` can also be used in a select statement for defining values or functions. Example:

```
$sql = select( val( 1 ) );
// SELECT 1
```

#### `param`

[](#param)

`param` establishes an anonymous or named parameter. Examples:

```
// Calling param() without an argument makes an anonymous parameter
$sql = select( 'total' )->from( 'sale' )->where( col( 'id' )->equalTo( param() ) )->end();
// SELECT `total` FROM `sale` WHERE `id` = ?

// Calling param() with an argument makes a named parameter
$sql = select( 'total' )->from( 'sale' )->where( col( 'id' )->equalTo( param( 'id' ) ) )->end();
// SELECT `total` FROM `sale` WHERE `id` = :id
```

#### `wrap`

[](#wrap)

`wrap` adds parenthesis around a condition. Example:

```
$sql = select( 'id' )->from( 'sale' )
    ->where(
        col( 'total' )->greaterThanOrEqualTo( 100 )
        ->and( wrap(
            col( 'customer_id' )->equalTo( 1234 )
            ->or( col( 'customer_id' )->equalTo( 4567 ) )
        ) )
    )->end();
// SELECT `id` FROM `sale`
// WHERE `total` >= 100 AND (`customer_id` = 1234 OR `customer_id` = 4567)
```

#### `not`

[](#not)

`not` negates a condition. Example:

```
$sql = select( 'name' )->from( 'customer' )
    ->where(
        not( col( 'name' )->like( '% % %' ) )
    )->end();
// SELECT `name` FROM `customer`
// WHERE NOT(`name` LIKE '% % %')
```

### Logic utilities

[](#logic-utilities)

These are especially useful for creating a WHERE condition that unites a bunch of other conditions with the same logic operator.

#### `andAll`

[](#andall)

`andAll()` concatenates all the received conditions with the AND operator. Example:

```
$condition = andAll(
    col( 'description' )->startWith( 'Mouse' ),
    col( 'price' )->lessThanOrEqualTo( 300.00 )
);

$sql = select()->from( 'product' )->where( $condition )->end();
// SELECT * FROM `product`
// WHERE `description` LIKE 'Mouse%' AND `price` startWith( 'Mouse' ),
    col( 'sku' )->contain( 'MZ' )
);

$sql = select()->from( 'product' )->where( $condition )->end();
// SELECT * FROM `product`
// WHERE `description` LIKE 'Mouse%' OR `sku` LIKE '%MZ%'
```

ℹ️ *Tip*: You can use the spread operator (`...`) for passing an array of conditions to `orAll()`. Just make sure that your array is not empty, before doing that.

### Ordering utilities

[](#ordering-utilities)

#### `asc`

[](#asc)

`asc()` indicates an ascending sort order. Its usage is **optional**. Example:

```
$sql = select()->from( 'example' )->orderBy( 'a', asc( 'b' ) )->end();
// SELECT * FROM `example` ORDER BY `a` ASC, `b` ASC
```

#### `desc`

[](#desc)

`desc()` makes an descending sort. Example:

```
$sql = select()->from( 'example' )->orderBy( 'a', desc( 'b' ) )->end();
// SELECT * FROM `example` ORDER BY `a` ASC, `b` DESC
```

### Aggregate functions

[](#aggregate-functions)

Aggregate functions can receive an alias as a second argument or use the method `as` to define an alias. For instance, these two commands are equivalent:

```
// Alias using the method as()
$sql = select(
        'date',
        sum( 'price * quantity' )->as( 'subtotal' ), // 👈
    )->from( 'sale' )
    ->groupBy( 'date' )
    ->end();

// Alias as the second argument
$sql = select(
        'date',
        sum( 'price * quantity', 'subtotal' ), // 👈
    )->from( 'sale' )
    ->groupBy( 'date' )
    ->end();
```

#### `count`

[](#count)

```
$sql = select( count( 'id' ) )->from( 'sale' )->end();
```

#### `countDistinct`

[](#countdistinct)

```
$sql = select( countDistinct( 'phone_number' ) )->from( 'contact' )->end();
```

#### `sum`

[](#sum)

```
$sql = select( sum( 'total' ) )->from( 'order' )->end();
```

#### `sumDistinct`

[](#sumdistinct)

```
$sql = select( sumDistinct( 'commission' ) )->from( 'sale' )->end();
```

#### `avg`

[](#avg)

```
$sql = select( avg( 'price' ) )->from( 'product' )->end();
```

#### `avgDistinct`

[](#avgdistinct)

```
$sql = select( avgDistinct( 'receive_qty' ) )->from( 'purchase' )->end();
```

#### `min`

[](#min)

```
$sql = select( min( 'price' ) )->from( 'product' )->end();
```

#### `max`

[](#max)

```
$sql = select( max( 'price' ) )->from( 'product' )->end();
```

### Date and Time functions

[](#date-and-time-functions)

#### `now`

[](#now)

`now()` returns the current date and time, in most databases. Example:

```
$sql = select( now() );
// MySQL        : SELECT NOW()
// PostgreSQL   : SELECT NOW()
// SQLite       : SELECT DATETIME('now')
// Oracle       : SELECT SYSDATE
// SQLServer    : SELECT CURRENT_TIMESTAMP
```

#### `date`

[](#date)

`date()` returns the current date. Example:

```
$sql = select( date() );
// MySQL        : SELECT CURRENT_DATE
// PostgreSQL   : SELECT CURRENT_DATE
// SQLite       : SELECT CURRENT_DATE
// Oracle       : SELECT SYSDATE
// SQLServer    : SELECT GETDATE()
```

#### `time`

[](#time)

`time()` returns the current time, in most databases. Example:

```
$sql = select( time() );
// MySQL        : SELECT CURRENT_TIME
// PostgreSQL   : SELECT CURRENT_TIME
// SQLite       : SELECT CURRENT_TIME
// Oracle       : SELECT CURRENT_TIMESTAMP
// SQLServer    : SELECT CURRENT_TIMESTAMP
```

#### `extract`

[](#extract)

`extract()` can extract a piece of a column or a date/time/timestamp value. Examples:

```
use phputil\sql\{SQLType, Extract};
use function phputil\sql\{select, extract};

$sql = select( extract( Extract::DAY, 'col1' ) )
    ->from( 'example' )->endAsString( SQLType::MYSQL );
// SELECT EXTRACT(DAY FROM `col1`) FROM `example`

$sql = select( extract( Extract::DAY, val( '2025-12-31' ) ) )
    ->toString( SQLType::MYSQL );
// SELECT EXTRACT(DAY FROM '2025-12-31')
```

This is the `Extract` enum:

```
enum Extract {
    case YEAR;
    case MONTH;
    case DAY;

    case HOUR;
    case MINUTE;
    case SECOND;
    case MICROSECOND;

    case QUARTER;
    case WEEK;
    case WEEK_DAY;
}
```

#### `diffInDays`

[](#diffindays)

`diffInDays` returns the difference in days from two dates/timestamps.

```
echo select( diffInDays( val( '31-12-2024' ), now() ) )
    ->toString( SQLType:MYSQL );
// SELECT DATEDIFF('31-12-2024', NOW())

echo select( diffInDays( 'birthdate', now() ) )->from( 'example' )
    ->toString( SQLType:MYSQL );
// SELECT DATEDIFF(`birthdate`, NOW()) FROM `example`
```

#### `addDays`

[](#adddays)

Documentation soon

#### `subDays`

[](#subdays)

Documentation soon

#### `dateAdd`

[](#dateadd)

Documentation soon

#### `dateSub`

[](#datesub)

Documentation soon

### String functions

[](#string-functions)

All string functions can have an alias, using `as()`. Example:

```
$sql = select(
    concat( 'first_name', 'last_name' )->as( 'full_name' )
)->from( 'customer' )->end();

// SELECT CONCAT(`first_name`, `last_name`) AS `full_name` FROM `customer`
```

#### `upper`

[](#upper)

`upper( $textOrColumn )` converts a text or column to uppercase. Example:

```
$sql = select( upper( 'name' ) )->from( 'customer' )->end();
//  SELECT UPPER(`name`) FROM `customer`
```

#### `lower`

[](#lower)

`lower( $textOrColumn )` converts a text or column to lowercase. Example:

```
$sql = select( lower( 'name' ) )->from( 'customer' )->end();
// SELECT LOWER(`name`) FROM `customer`
```

#### `substring`

[](#substring)

`substring( $textOrColumn, int $startingIndex, ?int $length = null )` gets a piece of a text or column.

- `$startingIndex` starts at 1;
- `$length` is optional.

Examples:

```
$sql = select( substring( 'name', 1, 5 ) ) )->from( 'customer' )->end();
// SELECT SUBSTRING(`name`, 1, 5) FROM `customer`

$sql = select( substring( val( 'Hello, world' ), 8 ) ) );
// SELECT SUBSTRING('Hello, world', 8)
```

#### `concat`

[](#concat)

`concat( $textOrColumn1, $textOrColumn2, ...$textOrColumnN )` concatenates strings or fields. Example:

```
$sql = select(
    concat( 'first_name', 'last_name' )->as( 'name' )
)->from( 'customer' )->end();

// SELECT CONCAT(`first_name`, `last_name`) AS `name` FROM `customer`
```

#### `length`

[](#length)

`length( $textOrColumn )` returns the length of a text or a (string) column. Example:

```
$sql = select( length( 'name' ) )->from( 'customer' )->end();
// SELECT CHAR_LENGTH(`name`) FROM `customer`
```

#### `bytes`

[](#bytes)

`bytes( $textOrColumn )` returns the size in bytes of a text or a (string) column. Example:

```
$sql = select( bytes( 'name' ) )->from( 'customer' )->end();
// SELECT LENGTH(`name`) FROM `customer`
```

### Null handling function

[](#null-handling-function)

#### `ifNull`

[](#ifnull)

`ifNull( $valueOrColumm, $valueOrColumnIfNull )` creates a fallback value for a column when it is null. Examples:

```
$sql = select( 'name', ifNull( 'nickname', val( 'anonymous' ) ) )
    ->from( 'user' )->end();
// SELECT `name`, COALESCE(`nickname`, 'anonymous') FROM `user`

$sql = select( 'name', ifNull( 'nickname', 'name' ) )
    ->from( 'user' )->end();
// SELECT `name`, COALESCE(`nickname`, `name`) FROM `user`
```

### Math functions

[](#math-functions)

Math functions can receive a field, a value or an expression.

All of them have a method `as()` to define an alias. Example:

```
$sql = select(
    power( 'price', 2 )->as( 'New price' )
)->from( 'product' )->end();

// SELECT POWER(`price`, 2) AS `New price` FROM `product`
```

#### `abs`

[](#abs)

```
$sql = select( abs( 'price' ) )->from( 'product' )->end();
```

#### `round`

[](#round)

```
$sql = select( round( 'price * amount' ) )->from( 'product' )->end();
```

#### `ceil`

[](#ceil)

```
$sql = select( ceil( 'price' ) )->from( 'product' )->end();
```

#### `floor`

[](#floor)

```
$sql = select( floor( 'price' ) )->from( 'product' )->end();
```

#### `power`

[](#power)

```
$sql = select( power( 'price', 2 ) )->from( 'product' )->end();
```

#### `sqrt`

[](#sqrt)

```
$sql = select( sqrt( 'amount' ) )->from( 'product' )->end();
```

#### `sin`

[](#sin)

```
$sql = select( sin( 2 ) );
```

#### `cos`

[](#cos)

```
$sql = select( cos( 2 ) );
```

#### `tan`

[](#tan)

```
$sql = select( tan( 2 ) );
```

Roadmap
-------

[](#roadmap)

- Select statement
    - Complex where clauses
    - Joins
    - Sub-queries
    - Limit and Offset
    - Aggregate functions
    - Distinct for selections and aggregation functions
    - Null handling function
    - Common date and time functions
    - Common string functions
    - Common mathematical functions
    - Automatic value conversions:
        - Add apostrophes to string values.
        - DateTime values as database strings.
        - Boolean and NULL values.
        - Array values inside `in` expressions.
    - Aggregate functions in order by clauses
    - Aggregate functions in having clauses - by using [val()](#val)
- Options for SQL generation
    - Add argument for avoiding escaping names
- Delete statement
    - WHERE clause
- Insert statement
    - with SELECT clause
- Update statement

👉 Contribute by opening an [Issue](https://github.com/thiagodp/phputil-sql/issues) or making a [Pull Request](https://github.com/thiagodp/phputil-sql/pulls).

License
-------

[](#license)

[MIT](LICENSE) ©️ [Thiago Delgado Pinto](https://github.com/thiagodp)

###  Health Score

33

—

LowBetter than 75% of packages

Maintenance67

Regular maintenance activity

Popularity5

Limited adoption so far

Community6

Small or concentrated contributor base

Maturity45

Maturing project, gaining track record

 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 ~1 days

Total

28

Last Release

202d ago

### Community

Maintainers

![](https://www.gravatar.com/avatar/154324d722a6ee9c252a0338329781084a97af2d0ea9faaf39176df5a689a2ec?d=identicon)[thiagodp](/maintainers/thiagodp)

---

Top Contributors

[![thiagodp](https://avatars.githubusercontent.com/u/2997844?v=4)](https://github.com/thiagodp "thiagodp (61 commits)")

---

Tags

builderfluent-interfacephpquerysql

###  Code Quality

Static AnalysisPHPStan

Type Coverage Yes

### Embed Badge

![Health badge](/badges/phputil-sql/health.svg)

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

###  Alternatives

[doctrine/orm

Object-Relational-Mapper for PHP

10.2k285.3M6.2k](/packages/doctrine-orm)[jdorn/sql-formatter

a PHP SQL highlighting library

3.9k115.1M102](/packages/jdorn-sql-formatter)[illuminate/database

The Illuminate Database package.

2.8k52.4M9.4k](/packages/illuminate-database)[ramsey/uuid-doctrine

Use ramsey/uuid as a Doctrine field type.

90440.3M211](/packages/ramsey-uuid-doctrine)[reliese/laravel

Reliese Components for Laravel Framework code generation.

1.7k3.4M16](/packages/reliese-laravel)[wildside/userstamps

Laravel Userstamps provides an Eloquent trait which automatically maintains `created\_by` and `updated\_by` columns on your model, populated by the currently authenticated user in your application.

7511.7M13](/packages/wildside-userstamps)

PHPackages © 2026

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