SQL Syntax Reference

Complete reference for php-sqlx augmented SQL syntax.

Placeholders

Positional Placeholders

-- Question mark (standard)
SELECT * FROM users WHERE id = ? AND status = ?

-- Numbered (PostgreSQL style)
SELECT * FROM users WHERE id = $1 AND status = $2

-- Colon-numbered
SELECT * FROM users WHERE id = :1 AND status = :2

Named Placeholders

-- Dollar-sign (recommended)
SELECT * FROM users WHERE id = $id AND status = $status

-- Colon-prefix
SELECT * FROM users WHERE id = :id AND status = :status

Type-Safe Placeholders

Scalar Types

SELECT * FROM users WHERE age >= ?u
SELECT * FROM products WHERE price = ?d
SELECT * FROM users WHERE name = ?s
INSERT INTO events (data) VALUES (?j)

Nullable Types

Prefix with n for nullable:

UPDATE users SET manager_id = ?ni WHERE id = ?i

Array Types

Suffix with a for arrays:

SELECT * FROM users WHERE id IN (?ia)
SELECT * FROM products WHERE category IN (?sa)

Named Type-Safe Placeholders

SELECT * FROM users WHERE age >= $min_age:u AND name = $name:s
SELECT * FROM users WHERE id IN ($ids:ia)

Conditional Blocks

Basic Syntax

SELECT * FROM users
WHERE 1=1
{{ AND status = $status }}
{{ AND role = $role }}

Block is included only if all referenced parameters are provided and non-null.

Nested Blocks

SELECT * FROM orders
WHERE 1=1
{{ AND customer_id = $customer_id
   {{ AND status = $status }}
}}

Multiple Parameters in Block

SELECT * FROM users
WHERE 1=1
{{ AND created_at BETWEEN $start_date AND $end_date }}

All parameters must be provided for the block to be included.

IN Clause Expansion

Array Expansion

-- Input
SELECT * FROM users WHERE id IN (?)
-- With params: [[1, 2, 3]]

-- Output (PostgreSQL)
SELECT * FROM users WHERE id IN ($1, $2, $3)
-- With params: [1, 2, 3]

Empty Array Handling

-- Input with empty array
SELECT * FROM users WHERE id IN (?)
-- With params: [[]]

-- Output (with OPT_COLLAPSIBLE_IN = true)
SELECT * FROM users WHERE FALSE

For NOT IN:

-- Input with empty array
SELECT * FROM users WHERE id NOT IN (?)

-- Output
SELECT * FROM users WHERE TRUE

Query Builder WHERE Syntax

Array Format

// [column, operator, value]
[['status', '=', 'active']]
[['age', '>=', 18]]
[['name', 'LIKE', '%john%']]
[['id', 'IN', [1, 2, 3]]]
[['deleted_at', 'IS NULL']]

Supported Operators

OR Conditions

use function Sqlx\OR_;

// AND (status = 'active' OR status = 'pending')
[
    OR_([
        ['status', '=', 'active'],
        ['status', '=', 'pending']
    ])
]

// Complex: active AND (admin OR moderator)
[
    ['active', '=', true],
    OR_([
        ['role', '=', 'admin'],
        ['role', '=', 'moderator']
    ])
]

Database-Specific Output

Identifier Quoting

Parameter Markers

Boolean Values

Unicode Strings

Escaping Rules

String Escaping

Single quotes are doubled:

'O''Brien'  -- Represents O'Brien

LIKE Pattern Escaping

metaQuoteLike() escapes % and _:

$pattern = $driver->metaQuoteLike("100%");
// Returns: '100\%'

Identifier Escaping

Special characters in identifiers:

$driver->quoteIdentifier("my-table");
// PostgreSQL: "my-table"
// MySQL: `my-table`
// MSSQL: [my-table]

Examples

Dynamic Search

SELECT * FROM products
WHERE 1=1
{{ AND category = $category }}
{{ AND price >= $min_price }}
{{ AND price <= $max_price }}
{{ AND name LIKE $search }}
ORDER BY {{ $sort_column }} {{ $sort_dir }}
LIMIT $limit OFFSET $offset

Type-Safe User Input

SELECT * FROM users
WHERE age >= ?u
AND status IN (?sa)
AND balance >= ?ud

Complex Filtering

SELECT u.*, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE 1=1
{{ AND u.status = $status }}
{{ AND u.role IN ($roles) }}
{{ AND u.created_at >= $since }}
GROUP BY u.id
{{ HAVING COUNT(o.id) >= $min_orders }}
ORDER BY {{ $order_by }}
LIMIT ?u