Download the PHP package knifelemon/easy-query without Composer

EasyQuery

A lightweight, fluent PHP SQL query builder that generates SQL and parameters. Designed to work with any database connection (PDO, MySQLi, FlightPHP SimplePdo).

Features

• 🔗 Fluent API - Chain methods for readable query construction
• 🛡️ SQL Injection Protection - Automatic parameter binding with prepared statements
• 🔧 Raw SQL Support - Insert raw SQL expressions with raw()
• 📝 Multiple Query Types - SELECT, INSERT, UPDATE, DELETE, COUNT
• 🔀 JOIN Support - INNER, LEFT, RIGHT joins with aliases
• 🎯 Advanced Conditions - LIKE, IN, BETWEEN, comparison operators, grouped conditions
• 📊 Aggregates - COUNT, SUM, AVG, MIN, MAX with DISTINCT and subqueries
• 🧮 Batch Writes - insertBatch, updateBatch, upsertBatch, deleteBatch
• 🪄 Conditional Chaining - when() / whenNot() for readable dynamic queries
• 🌐 Database Agnostic - Returns SQL + params, use with any DB connection
• 🪶 Lightweight - Minimal footprint with zero required dependencies

Installation

Via Composer

Manual Installation

Download and include the files:

Quick Start

Understanding build() Return Value

The build() method returns an array with two keys: sql and params. This separation is fundamental to how EasyQuery keeps your database safe.

What You Get

Why Split SQL and Parameters?

EasyQuery uses prepared statements - a security feature that prevents SQL injection attacks. Instead of inserting values directly into SQL (which is dangerous), we:

1. Generate SQL with placeholders (?) - The SQL structure is defined first
2. Keep values separate - User data stays in the params array
3. Let the database combine them safely - Your database driver (PDO, MySQLi) securely binds parameters

How to Use

The most common pattern is:

Why This Matters

❌ Dangerous (Never do this):

✅ Safe (EasyQuery way):

Working with Different Frameworks

EasyQuery's separation of SQL and parameters makes it compatible with any database library:

This universal approach means you can use EasyQuery with any framework or custom database setup.

Usage Examples

SELECT Queries

Basic SELECT

SELECT with Alias

php $q = Builder::table('users') ->alias('u') ->select(['u.id', 'u.name', 'p.title', 'p.content']) ->innerJoin('posts', 'u.id = p.user_id', 'p') ->where(['u.status' => 'active']) ->orderBy('p.published_at DESC') ->build();

// Result: // sql: "SELECT u.id, u.name, p.title, p.content FROM users AS u INNER JOIN posts AS p ON u.id = p.user_id WHERE u.status = ? ORDER BY p.published_at DESC" // params: ['active']

WHERE Conditions

Simple Equality

Comparison Operators

IN Operator

NOT IN Operator

BETWEEN Operator

IS NULL and IS NOT NULL

OR Conditions

Use orWhere() to add OR grouped conditions. Conditions within the same orWhere() call are joined with OR, and each group is added to the main query with AND.

IN / NOT IN Methods

LIKE / NOT LIKE

Grouped Conditions

HAVING

Aggregate Functions

DISTINCT

Subqueries

UNION

Batch Writes

Conditional Chaining

Builder Reuse with build(true)

INSERT Queries

INSERT with ON DUPLICATE KEY UPDATE

Use onDuplicateKeyUpdate() to handle duplicate key errors gracefully (MySQL/MariaDB only):

UPDATE Queries

DELETE Queries

COUNT Queries

Raw SQL Expressions

Use raw() when you need to insert SQL expressions directly without parameter binding:

Raw SQL with Bindings

When you need parameterized values in raw expressions:

Safe Identifiers for User Input

When column names come from user input (e.g., dynamic sorting), use safeIdentifier() to prevent SQL injection:

Safe Raw Expressions with User Input

Use rawSafe() when building raw SQL with user-provided column names:

Framework Integration

FlightPHP Integration

EasyQuery works with FlightPHP's SimplePdo by generating SQL and parameters that you pass directly to SimplePdo methods.

Legacy PHP / PDO Integration

MySQLi Integration

API Reference

Static Methods

Builder::table(string $table, string $alias = ''): Builder

Set the table name for the query. Optionally set a table alias in the same call.

Builder::raw(string $value): BuilderRaw

Create a raw SQL expression that will be inserted directly without parameter binding.

Builder::raw(string $value, array $bindings = []): BuilderRaw

Create a raw SQL expression with optional bound parameters for ? placeholders.

Builder::rawSafe(string $expression, array $identifiers, array $bindings = []): BuilderRaw

Create a raw SQL expression with safe identifier substitution. Use {placeholder} syntax for identifiers.

Builder::safeIdentifier(string $identifier): string

Validate and return a safe column/table identifier. Only allows alphanumeric, underscores, and dots.

Instance Methods

alias(string $alias): self

Set an alias for the table.

select(string|array $columns = '*'): self

Set the columns to select.

where(array $conditions): self

Add WHERE conditions. Multiple calls are combined with AND.

orWhere(array $conditions): self

Add OR WHERE conditions.

join(string $table, string $condition, string $alias = '', string $type = 'INNER'): self

Add a JOIN clause.

leftJoin(string $table, string $condition, string $alias = ''): self

Add a LEFT JOIN clause.

innerJoin(string $table, string $condition, string $alias = ''): self

Add an INNER JOIN clause.

groupBy(string $groupBy): self

Add GROUP BY clause.

orderBy(string $orderBy): self

Add ORDER BY clause.

orderBy(string $column, ?string $direction = null): self

Add ORDER BY. Without a direction, $orderBy is treated as a full sort expression (e.g. created_at DESC). With a direction, the column is validated with safeIdentifier() and the direction must be ASC or DESC, making it safe for user input.

limit(int $limit, int $offset = 0): self

Add LIMIT and optional OFFSET.

distinct(): self

Add DISTINCT to the SELECT query.

selectSubquery(Builder $query, string $alias): self

Add a subquery as a SELECT column with an alias.

selectCount(string $column, string $alias = ''): self

Add COUNT(column) to the SELECT list.

selectSum(string $column, string $alias = ''): self

Add SUM(column) to the SELECT list.

selectAvg(string $column, string $alias = ''): self

Add AVG(column) to the SELECT list.

selectMin(string $column, string $alias = ''): self

Add MIN(column) to the SELECT list.

selectMax(string $column, string $alias = ''): self

Add MAX(column) to the SELECT list.

fromSubquery(Builder $query, string $alias): self

Use a subquery as the FROM source instead of the table.

whereIn(string $column, array $values): self

Add a WHERE column IN (...) condition.

whereIn(string $column, Builder $query): self

Add a WHERE column IN (subquery) condition with correctly ordered parameters.

orWhereIn(string $column, array|Builder $values): self

Add an OR IN condition (array or subquery).

whereNotIn(string $column, array|Builder $values): self

Add a WHERE column NOT IN (...) condition (array or subquery).

orWhereNotIn(string $column, array|Builder $values): self

Add an OR NOT IN condition (array or subquery).

like(string $column, string $value, string $position = 'both'): self

Add a WHERE column LIKE ? condition. Values are bound as parameters and escaped with an explicit ESCAPE '!' clause. $position is one of 'before', 'after', 'both', 'none'.

orLike(string $column, string $value, string $position = 'both'): self

Add an OR LIKE condition.

notLike(string $column, string $value, string $position = 'both'): self

Add a WHERE column NOT LIKE ? condition.

orNotLike(string $column, string $value, string $position = 'both'): self

Add an OR NOT LIKE condition.

groupStart(): self

Open a new condition group joined with AND. Must be closed with groupEnd().

orGroupStart(): self

Open a new condition group joined with OR. Must be closed with groupEnd().

notGroupStart(): self

Open a negated condition group (NOT (...)). Must be closed with groupEnd().

groupEnd(): self

Close an open condition group.

having(array $conditions): self

Add HAVING conditions for filtered aggregate queries.

orHaving(array $conditions): self

Add OR HAVING conditions.

rightJoin(string $table, string $condition, string $alias = ''): self

Add a RIGHT JOIN clause.

union(Builder $query): self

Add a UNION to the query.

unionAll(Builder $query): self

Add a UNION ALL to the query.

insertBatch(array $rows): self

Set the query action to a multi-row INSERT. All rows must contain the same columns. Raw Builder::raw() values are inlined.

upsertBatch(array $rows, array $uniqueKeys): self

Set the query action to INSERT ... ON DUPLICATE KEY UPDATE (MySQL/MariaDB). $uniqueKeys are the columns that define a duplicate row; remaining columns are updated with VALUES(column).

updateBatch(array $rows, string $whereColumn): self

Set the query action to a multi-row UPDATE using CASE WHEN blocks. The $whereColumn value identifies each row and is also used in the final WHERE ... IN (...) clause.

deleteBatch(string $whereColumn, array $values): self

Set the query action to DELETE WHERE column IN (...).

when($condition, callable $callback): self

Conditionally apply query modifications. If $condition is truthy, $callback($this) is invoked. Always returns $this and leaves the builder usable.

whenNot($condition, callable $callback): self

Inverse of when(). The callback runs when the condition is falsy.

count(string $column = '*'): self

Set the query action to COUNT.

insert(array $data): self

Set the query action to INSERT with data.

update(array $data): self

Set the query action to UPDATE with data.

delete(): self

Set the query action to DELETE.

clearWhere(): self

Clear WHERE conditions and parameters (allows query builder reuse).

clearSelect(): self

Clear SELECT columns (reset to default '*').

clearJoin(): self

Clear all JOIN clauses.

clearGroupBy(): self

Clear GROUP BY clause.

clearOrderBy(): self

Clear ORDER BY clause.

clearLimit(): self

Clear LIMIT and OFFSET.

clearAll(): self

Clear all query conditions (reset builder to initial state).

build(bool $reset = false): array

Build and return the query as ['sql' => string, 'params' => array]. Pass true to reset the builder afterwards (opt-in; the builder is not reset by default). The reset clears the conditions and returns the action to SELECT, so the builder can be reused for any query type; the table and alias are preserved. Note this differs from clearAll(), which is called during the reset but deliberately preserves the action (existing behavior); build(true) resets it as an extra step.

get(): array

Alias for build().

buildSQL(): string

Build and return only the SQL string (for SELECT queries).

getSQL(): string

Alias for buildSQL().

getParams(): array

Get the parameter array for binding.

Advanced Examples

Complex JOIN with Multiple Conditions

Dynamic Query Building

Query Builder Reuse

The query builder can be reused by clearing specific conditions or resetting entirely. This is useful when you need to execute similar queries with different parameters.

Clear Methods Usage

Practical Example: Pagination with Reuse

Batch Insert Helper

Security

This library uses prepared statements with parameter binding to protect against SQL injection attacks. Parameters are never directly concatenated into SQL strings.

Safe Parameter Binding

Safe Column Names from User Input

When column names come from user input (e.g., sorting, aggregation), use these safety methods:

Allowed Identifier Characters

safeIdentifier() and rawSafe() only allow:

• Letters (a-z, A-Z)
• Numbers (0-9)
• Underscores (_)
• Dots (.) for table.column notation

Any other characters will throw an InvalidArgumentException.

Debugging with Tracy

EasyQuery provides automatic Tracy Debugger integration with a beautiful custom panel. No setup required! Just install Tracy and use EasyQuery - the debug panel will automatically appear.

Automatic Setup

How It Works

• Auto-initialization: First Builder instantiation automatically initializes Tracy logging
• Zero configuration: Just have Tracy installed and it works
• Automatic logging: Every build() call is logged to the custom Tracy panel

Tracy Panel Features

The custom Tracy panel shows:

• Summary Cards: Total queries, breakdown by type (SELECT, INSERT, UPDATE, DELETE, COUNT)
• Query List: Each query with:
  • Action type badge (color-coded)
  • Generated SQL (syntax highlighted)
  • Parameters array
  • Expandable details (table, where, joins, order, limit, etc.)
  • Timestamp

Install Tracy:

If Tracy is not installed, EasyQuery works normally without any debug output.

Testing

Requirements

• PHP >= 7.4
• PDO or MySQLi extension (for database connectivity)

Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

1. Fork the repository
2. Create your feature branch (git checkout -b feature/amazing-feature)
3. Commit your changes (git commit -m 'Add some amazing feature')
4. Push to the branch (git push origin feature/amazing-feature)
5. Open a Pull Request

License

This project is licensed under the MIT License - see the LICENSE file for details.

Credits

Created and maintained by KnifeLemon

Changelog

See CHANGELOG.md for version history.

Support

If you encounter any issues or have questions, please open an issue on GitHub.

Resources

• FlightPHP Framework
• FlightPHP GitHub
• FlightPHP SimplePdo Documentation