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
🌐 Database Agnostic - Returns SQL + params, use with any DB connection
🪶 Lightweight - Minimal footprint with zero required dependencies

Installation

Via Composer

composer require knifelemon/easy-query

Manual Installation

Download and include the files:

require_once 'src/Builder.php';
require_once 'src/BuilderRaw.php';

Quick Start

use KnifeLemon\EasyQuery\Builder;

// Simple SELECT query
$q = Builder::table('users')
    ->select(['id', 'name', 'email'])
    ->where(['status' => 'active'])
    ->orderBy('id DESC')
    ->limit(10)
    ->build();

// Execute with PDO
$stmt = $pdo->prepare($q['sql']);
$stmt->execute($q['params']);
$users = $stmt->fetchAll();

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

$q = Builder::table('users')
    ->where(['email' => 'user@example.com'])
    ->build();

// Returns:
// [
//     'sql' => 'SELECT * FROM users WHERE email = ?',
//     'params' => ['user@example.com']
// ]

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:

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

How to Use

The most common pattern is:

// 1. Build your query
$q = Builder::table('users')
    ->where(['status' => 'active'])
    ->limit(10)
    ->build();

// 2. Prepare the SQL statement
$stmt = $pdo->prepare($q['sql']);

// 3. Execute with parameters
$stmt->execute($q['params']);

// 4. Get results
$users = $stmt->fetchAll();

Why This Matters

❌ Dangerous (Never do this):

// Direct concatenation = SQL injection vulnerability!
$email = $_POST['email'];
$sql = "SELECT * FROM users WHERE email = '$email'";
// If $email is: ' OR '1'='1
// SQL becomes: SELECT * FROM users WHERE email = '' OR '1'='1'
// This returns ALL users!

✅ Safe (EasyQuery way):

$email = $_POST['email'];
$q = Builder::table('users')
    ->where(['email' => $email])
    ->build();
// SQL: SELECT * FROM users WHERE email = ?
// Params: ['user input']
// The database treats the input as data, not code

Working with Different Frameworks

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

// PDO
$stmt = $pdo->prepare($q['sql']);
$stmt->execute($q['params']);

// MySQLi
$stmt = $mysqli->prepare($q['sql']);
$stmt->execute($q['params']);

// FlightPHP SimplePdo
$users = Flight::db()->fetchAll($q['sql'], $q['params']);

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

Usage Examples

SELECT Queries

Basic SELECT

$q = Builder::table('users')
    ->select(['id', 'name', 'email'])
    ->where(['status' => 'active'])
    ->build();

// Result: 
// sql: "SELECT id, name, email FROM users WHERE status = ?"
// params: ['active']

SELECT with Alias

// Method 1: Set alias in table() method (v1.0.2.2+)
$q = Builder::table('users', 'u')
    ->select(['u.id', 'u.name'])
    ->where(['u.status' => 'active'])
    ->orderBy('u.created_at DESC')
    ->limit(10)
    ->build();

// Method 2: Set alias using alias() method
$q = Builder::table('users')
    ->alias('u')
    ->select(['u.id', 'u.name'])
    ->where(['u.status' => 'active'])
    ->orderBy('u.created_at DESC')
    ->limit(10)
    ->build();

// Result:
// sql: "SELECT u.id, u.name FROM users AS u WHERE u.status = ? ORDER BY u.created_at DESC LIMIT 10"
// params: ['active']

#### SELECT with JOIN

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

```php
$q = Builder::table('users')
    ->where(['id' => 123, 'status' => 'active'])
    ->build();
// WHERE id = ? AND status = ?

Comparison Operators

$q = Builder::table('users')
    ->where([
        'age' => ['>=', 18],
        'score' => ['<', 100],
        'name' => ['LIKE', '%john%']
    ])
    ->build();

// Result:
// sql: "SELECT * FROM users WHERE age >= ? AND score < ? AND name LIKE ?"
// params: [18, 100, '%john%']

IN Operator

$q = Builder::table('users')
    ->where([
        'id' => ['IN', [1, 2, 3, 4, 5]]
    ])
    ->build();

// Result:
// sql: "SELECT * FROM users WHERE id IN (?, ?, ?, ?, ?)"
// params: [1, 2, 3, 4, 5]

NOT IN Operator

$q = Builder::table('users')
    ->where([
        'status' => ['NOT IN', ['banned', 'deleted', 'suspended']]
    ])
    ->build();

// Result:
// sql: "SELECT * FROM users WHERE status NOT IN (?, ?, ?)"
// params: ['banned', 'deleted', 'suspended']

BETWEEN Operator

$q = Builder::table('products')
    ->where([
        'price' => ['BETWEEN', [100, 500]]
    ])
    ->build();

// Result:
// sql: "SELECT * FROM products WHERE price BETWEEN ? AND ?"
// params: [100, 500]

IS NULL and IS NOT NULL

// IS NULL - check for NULL values
$q = Builder::table('users')
    ->where(['deleted_at' => ['IS', null]])
    ->build();

// Result:
// sql: "SELECT * FROM users WHERE deleted_at IS NULL"
// params: []

// IS NOT NULL - check for non-NULL values
$q = Builder::table('users')
    ->where(['email' => ['IS NOT', null]])
    ->build();

// Result:
// sql: "SELECT * FROM users WHERE email IS NOT NULL"
// params: []

// Mixed with other conditions
$q = Builder::table('users')
    ->where([
        'kakao_sender_key' => ['IS NOT', null],
        'is_delete' => 'N',
        'status' => 'active'
    ])
    ->build();

// Result:
// sql: "SELECT * FROM users WHERE kakao_sender_key IS NOT NULL AND is_delete = ? AND status = ?"
// params: ['N', 'active']

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.

// Simple OR condition
$q = Builder::table('users')
    ->where(['status' => 'active'])
    ->orWhere(['role' => 'admin'])
    ->build();
// WHERE status = ? AND (role = ?)
// params: ['active', 'admin']

// Multiple conditions in OR group
$q = Builder::table('users')
    ->where(['status' => 'active'])
    ->orWhere([
        'role' => 'admin',
        'role' => 'moderator',
        'permissions' => ['LIKE', '%manage%']
    ])
    ->build();
// WHERE status = ? AND (role = ? OR role = ? OR permissions LIKE ?)
// params: ['active', 'admin', 'moderator', '%manage%']

INSERT Queries

$q = Builder::table('users')
    ->insert([
        'name' => 'John Doe',
        'email' => 'john@example.com',
        'status' => 'active'
    ])
    ->build();

// Result:
// sql: "INSERT INTO users SET name = ?, email = ?, status = ?"
// params: ['John Doe', 'john@example.com', 'active']

INSERT with ON DUPLICATE KEY UPDATE

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

// Basic usage - update specific columns on duplicate
$q = Builder::table('users')
    ->insert([
        'email' => 'user@example.com',
        'name' => 'John Doe',
        'points' => 100
    ])
    ->onDuplicateKeyUpdate([
        'name' => 'John Doe Updated',
        'points' => 200
    ])
    ->build();

// Result:
// sql: "INSERT INTO users SET email = ?, name = ?, points = ? ON DUPLICATE KEY UPDATE name = ?, points = ?"
// params: ['user@example.com', 'John Doe', 100, 'John Doe Updated', 200]

// Increment values on duplicate using raw SQL
$q = Builder::table('users')
    ->insert([
        'email' => 'user@example.com',
        'name' => 'John Doe',
        'points' => 100
    ])
    ->onDuplicateKeyUpdate([
        'points' => Builder::raw('points + 100'),
        'login_count' => Builder::raw('login_count + 1'),
        'updated_at' => Builder::raw('NOW()')
    ])
    ->build();

// Result:
// sql: "INSERT INTO users SET email = ?, name = ?, points = ? ON DUPLICATE KEY UPDATE

EasyQueryBuilder

Install / Use

README