PHP code example of knifelemon / easy-query

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

    

knifelemon / easy-query example snippets

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();

$q = Builder::table('users')
    ->where(['email' => '[email protected]'])
    ->build();

// Returns:
// [
//     'sql' => 'SELECT * FROM users WHERE email = ?',
//     'params' => ['[email protected]']
// ]

// 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();

// 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!

$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

// 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']);

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

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

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

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

$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%']

$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]

$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']

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

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

// 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']

// 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%']

// IN with an array of values
$q = Builder::table('users')
    ->where(['status' => 'active'])
    ->whereIn('id', [1, 2, 3, 4, 5])
    ->build();
// sql: "SELECT * FROM users WHERE status = ? AND id IN (?, ?, ?, ?, ?)"
// params: ['active', 1, 2, 3, 4, 5]

// IN with a subquery
$q = Builder::table('users')
    ->whereIn('id', Builder::table('logs')->select('user_id')->where(['type' => 'signup']))
    ->build();
// sql: "SELECT * FROM users WHERE id IN (SELECT user_id FROM logs WHERE type = ?)"
// params: ['signup']

// NOT IN
$q = Builder::table('users')
    ->whereNotIn('status', ['banned', 'deleted'])
    ->build();
// sql: "SELECT * FROM users WHERE status NOT IN (?, ?)"
// params: ['banned', 'deleted']

// LIKE with escaped wildcards and an explicit ESCAPE clause
$q = Builder::table('products')
    ->where(['category' => 'books'])
    ->like('title', '100%')
    ->build();
// sql: "SELECT * FROM products WHERE category = ? AND title LIKE ? ESCAPE '!'"
// params: ['books', '%100!%%']

// Search only the start of the value
$q = Builder::table('products')
    ->like('title', 'PHP', 'after')
    ->build();
// sql: "SELECT * FROM products WHERE title LIKE ? ESCAPE '!'"
// params: ['PHP%']

// NOT LIKE
$q = Builder::table('products')
    ->notLike('title', 'expired')
    ->build();
// sql: "SELECT * FROM products WHERE title NOT LIKE ? ESCAPE '!'"
// params: ['%expired%']

// AND group (nested parentheses)
$q = Builder::table('orders')
    ->where(['status' => 'open'])
    ->groupStart()
        ->where(['total' => ['>=', 100]])
        ->where(['priority' => 'high'])
    ->groupEnd()
    ->build();
// sql: "SELECT * FROM orders WHERE status = ? AND (total >= ? AND priority = ?)"
// params: ['open', 100, 'high']

// OR alternation via orWhere() (conditions within the same call are joined with OR)
$q = Builder::table('orders')
    ->where(['status' => 'open'])
    ->where(['total' => ['>=', 100]])
    ->orWhere(['priority' => 'high', 'urgent' => 1])
    ->build();
// sql: "SELECT * FROM orders WHERE status = ? AND total >= ? AND (priority = ? OR urgent = ?)"
// params: ['open', 100, 'high', 1]

// OR group via orGroupStart()
$q = Builder::table('users')
    ->where(['status' => 'active'])
    ->orGroupStart()
        ->where(['role' => 'admin'])
        ->where(['plan' => 'premium'])
    ->groupEnd()
    ->build();
// sql: "SELECT * FROM users WHERE status = ? OR (role = ? AND plan = ?)"
// params: ['active', 'admin', 'premium']

// Negated group
$q = Builder::table('users')
    ->where(['status' => 'active'])
    ->notGroupStart()
        ->where(['role' => 'guest'])
        ->where(['banned' => 1])
    ->groupEnd()
    ->build();
// sql: "SELECT * FROM users WHERE status = ? AND NOT (role = ? AND banned = ?)"
// params: ['active', 'guest', 1]

$q = Builder::table('orders')
    ->select(['user_id', 'total'])
    ->groupBy('user_id')
    ->having(['total' => ['>', 1000]])
    ->build();
// sql: "SELECT user_id, total FROM orders GROUP BY user_id HAVING total > ?"
// params: [1000]

$q = Builder::table('orders')
    ->selectCount('*', 'total_orders')
    ->selectSum('amount', 'total_amount')
    ->where(['status' => 'paid'])
    ->build();
// sql: "SELECT COUNT(*) AS total_orders, SUM(amount) AS total_amount FROM orders WHERE status = ?"
// params: ['paid']

$q = Builder::table('orders')
    ->select(['user_id'])
    ->selectAvg('amount')
    ->selectMin('amount', 'min_amount')
    ->selectMax('amount', 'max_amount')
    ->groupBy('user_id')
    ->build();
// sql: "SELECT user_id, AVG(amount), MIN(amount) AS min_amount, MAX(amount) AS max_amount FROM orders GROUP BY user_id"
// params: []

$q = Builder::table('users')
    ->distinct()
    ->select(['country'])
    ->build();
// sql: "SELECT DISTINCT country FROM users"
// params: []

// Subquery in the SELECT list
$q = Builder::table('users')
    ->select(['id', 'name'])
    ->selectSubquery(
        Builder::table('orders')->selectCount('*')->where(['user_id' => 42]),
        'order_count'
    )
    ->build();
// sql: "SELECT id, name, (SELECT COUNT(*) FROM orders WHERE user_id = ?) AS order_count FROM users"
// params: [42]

// Subquery as the FROM source
$q = Builder::table('users')
    ->fromSubquery(
        Builder::table('users')->select(['id', 'email'])->where(['status' => 'active']),
        'u'
    )
    ->where(['u.age' => ['>=', 18]])
    ->build();
// sql: "SELECT * FROM (SELECT id, email FROM users WHERE status = ?) AS u WHERE u.age >= ?"
// params: ['active', 18]

$q = Builder::table('active_users')
    ->select(['id', 'name'])
    ->union(Builder::table('vip_users')->select(['id', 'name']))
    ->build();
// sql: "SELECT id, name FROM active_users UNION SELECT id, name FROM vip_users"
// params: []

// UNION ALL keeps duplicate rows
$q = Builder::table('jan_orders')
    ->unionAll(Builder::table('feb_orders'))
    ->build();
// sql: "SELECT * FROM jan_orders UNION ALL SELECT * FROM feb_orders"
// params: []

// Multi-row INSERT
$q = Builder::table('users')
    ->insertBatch([
        ['name' => 'Alice', 'email' => '[email protected]'],
        ['name' => 'Bob', 'email' => '[email protected]'],
    ])
    ->build();
// sql: "INSERT INTO users (name, email) VALUES (?, ?), (?, ?)"
// params: ['Alice', '[email protected]', 'Bob', '[email protected]']

// Upsert with ON DUPLICATE KEY UPDATE
$q = Builder::table('user_stats')
    ->upsertBatch([
        ['user_id' => 1, 'views' => 10],
        ['user_id' => 2, 'views' => 5],
    ], ['user_id'])
    ->build();
// sql: "INSERT INTO user_stats (user_id, views) VALUES (?, ?), (?, ?) ON DUPLICATE KEY UPDATE views = VALUES(views)"
// params: [1, 10, 2, 5]

// Multi-row UPDATE via CASE WHEN
$q = Builder::table('users')
    ->updateBatch([
        ['id' => 1, 'status' => 'active'],
        ['id' => 2, 'status' => 'disabled'],
    ], 'id')
    ->build();
// sql: "UPDATE users SET status = CASE WHEN id = ? THEN ? WHEN id = ? THEN ? END WHERE id IN (?, ?)"
// params: [1, 'active', 2, 'disabled', 1, 2]

// Batch DELETE
$q = Builder::table('users')
    ->deleteBatch('id', [1, 2, 3])
    ->build();
// sql: "DELETE FROM users WHERE id IN (?, ?, ?)"
// params: [1, 2, 3]

$q = Builder::table('products')
    ->when(!empty($categoryId), function ($query) use ($categoryId) {
        $query->where(['category_id' => $categoryId]);
    })
    ->when(!empty($searchTerm), function ($query) use ($searchTerm) {
        $query->like('name', $searchTerm);
    })
    ->build();

// Reset a builder in one call (replaces manual clear*() calls)
$q = Builder::table('users')
    ->where(['status' => 'active'])
    ->build(true);
// $q is the query result; the builder is reset and ready for the next statement

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

// Result:
// sql: "INSERT INTO users SET name = ?, email = ?, status = ?"
// params: ['John Doe', '[email protected]', 'active']

// Basic usage - update specific columns on duplicate
$q = Builder::table('users')
    ->insert([
        'email' => '[email protected]',
        '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: ['[email protected]', 'John Doe', 100, 'John Doe Updated', 200]

// Increment values on duplicate using raw SQL
$q = Builder::table('users')
    ->insert([
        'email' => '[email protected]',
        '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 points = points + 100, login_count = login_count + 1, updated_at = NOW()"
// params: ['[email protected]', 'John Doe', 100]

// Use VALUES() to reference the inserted value
$q = Builder::table('user_stats')
    ->insert([
        'user_id' => 123,
        'views' => 10,
        'clicks' => 5
    ])
    ->onDuplicateKeyUpdate([
        'views' => Builder::raw('views + VALUES(views)'),
        'clicks' => Builder::raw('clicks + VALUES(clicks)'),
        'updated_at' => Builder::raw('NOW()')
    ])
    ->build();

// Result:
// sql: "INSERT INTO user_stats SET user_id = ?, views = ?, clicks = ? ON DUPLICATE KEY UPDATE views = views + VALUES(views), clicks = clicks + VALUES(clicks), updated_at = NOW()"
// params: [123, 10, 5]

$q = Builder::table('users')
    ->update(['status' => 'inactive', 'updated_at' => date('Y-m-d H:i:s')])
    ->where(['id' => 123])
    ->build();

// Result:
// sql: "UPDATE users SET status = ?, updated_at = ? WHERE id = ?"
// params: ['inactive', '2026-01-15 10:30:00', 123]

$q = Builder::table('users')
    ->delete()
    ->where(['id' => 123])
    ->build();

// Result:
// sql: "DELETE FROM users WHERE id = ?"
// params: [123]

$q = Builder::table('users')
    ->count()
    ->where(['status' => 'active'])
    ->build();

// Result:
// sql: "SELECT COUNT(*) AS cnt FROM users WHERE status = ?"
// params: ['active']

use KnifeLemon\EasyQuery\Builder;

// Update with SQL functions
$q = Builder::table('users')
    ->update([
        'points' => Builder::raw('GREATEST(0, points - 100)'),
        'updated_at' => Builder::raw('NOW()')
    ])
    ->where(['id' => 123])
    ->build();

// Result:
// sql: "UPDATE users SET points = GREATEST(0, points - 100), updated_at = NOW() WHERE id = ?"
// params: [123]

// WHERE with raw SQL
$q = Builder::table('products')
    ->where([
        'price' => ['>', Builder::raw('(SELECT AVG(price) FROM products)')]
    ])
    ->build();

// Result:
// sql: "SELECT * FROM products WHERE price > (SELECT AVG(price) FROM products)"
// params: []

// Raw expression with bound parameters
$q = Builder::table('orders')
    ->update([
        'total' => Builder::raw('COALESCE(subtotal, ?) + ?', [0, 10])
    ])
    ->where(['id' => 1])
    ->build();

// Result:
// sql: "UPDATE orders SET total = COALESCE(subtotal, ?) + ? WHERE id = ?"
// params: [0, 10, 1]

// Validate user-provided column name
$sortColumn = $_GET['sort'];  // e.g., 'created_at'
$safeColumn = Builder::safeIdentifier($sortColumn);

$q = Builder::table('users')
    ->orderBy($safeColumn . ' DESC')
    ->build();

// If user tries: "name; DROP TABLE users--"
// Throws InvalidArgumentException: Invalid identifier

// User selects which column to aggregate
$userColumn = $_GET['aggregate_column'];  // e.g., 'total_amount'

$q = Builder::table('orders')
    ->select([
        Builder::rawSafe('COALESCE(SUM({col}), ?)', ['col' => $userColumn], [0])->value . ' AS total'
    ])
    ->build();

// Result (with safe column):
// sql: "SELECT COALESCE(SUM(total_amount), ?) AS total FROM orders"
// params: [0]

// If user tries SQL injection, throws InvalidArgumentException

// Multiple safe identifiers
use KnifeLemon\EasyQuery\BuilderRaw;

$raw = BuilderRaw::withIdentifiers(
    '{table}.{col1} + {table}.{col2}',
    ['table' => 'orders', 'col1' => 'price', 'col2' => 'tax']
);
// Result: "orders.price + orders.tax"

use KnifeLemon\EasyQuery\Builder;

// Register SimplePdo with FlightPHP
Flight::register('db', \flight\database\SimplePdo::class, [
    'mysql:host=localhost;dbname=myapp;charset=utf8mb4',
    'username',
    'password',
    [
        PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES \'utf8mb4\'',
        PDO::ATTR_EMULATE_PREPARES => false,
        PDO::ATTR_STRINGIFY_FETCHES => false,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
    ]
]);

// In your FlightPHP route
Flight::route('GET /users', function() {
    $q = Builder::table('users')
        ->select(['id', 'name', 'email'])
        ->where(['status' => 'active'])
        ->orderBy('created_at DESC')
        ->limit(20)
        ->build();
    
    // SimplePdo returns Collection objects
    $users = Flight::db()->fetchAll($q['sql'], $q['params']);
    
    // Collection objects have getData() method that returns array
    $usersArray = array_map(fn($user) => $user->getData(), $users);
    
    Flight::json(['users' => $usersArray]);
});

// Using fetchField for COUNT queries (returns single value)
Flight::route('GET /users/count', function() {
    $q = Builder::table('users')
        ->count()
        ->where(['status' => 'active'])
        ->build();
    
    $count = Flight::db()->fetchField($q['sql'], $q['params']);
    
    Flight::json(['count' => (int)$count]);
});

// INSERT with FlightPHP
Flight::route('POST /users', function() {
    $data = Flight::request()->data;
    
    $q = Builder::table('users')
        ->insert([
            'name' => $data->name,
            'email' => $data->email,
            'created_at' => Builder::raw('NOW()')
        ])
        ->build();
    
    Flight::db()->runQuery($q['sql'], $q['params']);
    $userId = Flight::db()->lastInsertId();
    
    Flight::json(['success' => true, 'id' => $userId]);
});

use KnifeLemon\EasyQuery\Builder;

// PDO connection
$pdo = new PDO('mysql:host=localhost;dbname=mydb', 'user', 'pass');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

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

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

// INSERT with PDO
$q = Builder::table('users')
    ->insert([
        'name' => 'Jane Doe',
        'email' => '[email protected]'
    ])
    ->build();

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

// UPDATE with PDO
$q = Builder::table('users')
    ->update(['status' => 'inactive'])
    ->where(['id' => $userId])
    ->build();

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

use KnifeLemon\EasyQuery\Builder;

$mysqli = new mysqli('localhost', 'user', 'pass', 'mydb');

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

// Prepare statement
$stmt = $mysqli->prepare($q['sql']);

// Bind parameters dynamically
$types = str_repeat('s', count($q['params'])); // 's' for string, adjust as needed
$stmt->bind_param($types, ...$q['params']);

$stmt->execute();
$result = $stmt->get_result();
$users = $result->fetch_all(MYSQLI_ASSOC);

$q = Builder::table('orders')
    ->alias('o')
    ->select([
        'o.id',
        'o.total',
        'u.name AS customer_name',
        'p.title AS product_title'
    ])
    ->innerJoin('users', 'o.user_id = u.id', 'u')
    ->leftJoin('order_items', 'o.id = oi.order_id', 'oi')
    ->leftJoin('products', 'oi.product_id = p.id', 'p')
    ->where([
        'o.status' => 'completed',
        'o.total' => ['>=', 100],
        'o.created_at' => ['>=', '2024-01-01']
    ])
    ->groupBy('o.id')
    ->orderBy('o.created_at DESC')
    ->limit(50)
    ->build();

$query = Builder::table('products')->alias('p');

// Conditionally add conditions
if (!empty($categoryId)) {
    $query->where(['p.category_id' => $categoryId]);
}

if (!empty($minPrice)) {
    $query->where(['p.price' => ['>=', $minPrice]]);
}

if (!empty($searchTerm)) {
    $query->where(['p.name' => ['LIKE', "%{$searchTerm}%"]]);
}

// Add sorting
$query->orderBy('p.created_at DESC')->limit(20);

$result = $query->build();

// Create a base query
$baseQuery = Builder::table('users')
    ->select(['id', 'name', 'email'])
    ->where(['status' => 'active'])
    ->orderBy('created_at DESC');

// First query: Active users in the last 30 days
$q1 = $baseQuery
    ->where(['created_at' => ['>=', date('Y-m-d', strtotime('-30 days'))]])
    ->limit(10)
    ->build();

$recentUsers = executeQuery($q1);

// Clear WHERE to reuse the builder
$baseQuery->clearWhere();

// Second query: All active premium users
$q2 = $baseQuery
    ->where(['status' => 'active', 'plan' => 'premium'])
    ->limit(20)
    ->build();

$premiumUsers = executeQuery($q2);

// Clear specific parts
$baseQuery
    ->clearSelect()
    ->clearOrderBy()
    ->clearLimit();

// Third query: Count active users
$q3 = $baseQuery
    ->count()
    ->where(['status' => 'active'])
    ->build();

$activeCount = executeQuery($q3);

// Clear only WHERE conditions
$query->clearWhere();

// Clear only SELECT columns
$query->clearSelect();

// Clear only JOINs
$query->clearJoin();

// Clear only ORDER BY
$query->clearOrderBy();

// Clear only GROUP BY
$query->clearGroupBy();

// Clear only LIMIT and OFFSET
$query->clearLimit();

// Clear everything and start fresh
$query->clearAll();

// Base query for user list
$usersQuery = Builder::table('users')
    ->select(['id', 'name', 'email', 'created_at'])
    ->where(['status' => 'active'])
    ->orderBy('created_at DESC');

// Get total count
$countQuery = clone $usersQuery;
$countResult = $countQuery
    ->clearSelect()
    ->count()
    ->build();

$totalUsers = executeQuery($countResult)[0]['cnt'];

// Get paginated results
$page = 1;
$perPage = 20;
$offset = ($page - 1) * $perPage;

$listResult = $usersQuery
    ->limit($perPage, $offset)
    ->build();

$users = executeQuery($listResult);

// Next page - reuse the same query
$usersQuery->clearLimit();
$page = 2;
$offset = ($page - 1) * $perPage;

$nextPageResult = $usersQuery
    ->limit($perPage, $offset)
    ->build();

$nextPageUsers = executeQuery($nextPageResult);

function batchInsert($pdo, $table, array $rows) {
    $pdo->beginTransaction();
    try {
        foreach ($rows as $row) {
            $q = Builder::table($table)->insert($row)->build();
            $stmt = $pdo->prepare($q['sql']);
            $stmt->execute($q['params']);
        }
        $pdo->commit();
        return true;
    } catch (Exception $e) {
        $pdo->rollBack();
        throw $e;
    }
}

// Usage
$users = [
    ['name' => 'Alice', 'email' => '[email protected]'],
    ['name' => 'Bob', 'email' => '[email protected]'],
    ['name' => 'Charlie', 'email' => '[email protected]']
];

batchInsert($pdo, 'users', $users);

// ✅ SAFE - Using parameter binding
$q = Builder::table('users')
    ->where(['email' => $_POST['email']])
    ->build();

// ✅ SAFE - Using raw() with SQL functions
$q = Builder::table('users')
    ->update(['updated_at' => Builder::raw('NOW()')])
    ->build();

// ❌ DANGEROUS - Never do this!
$q = Builder::table('users')
    ->where(['email' => Builder::raw("'{$_POST['email']}'")])  // SQL injection risk!
    ->build();

// ✅ SAFE - Validate column name
$sortColumn = Builder::safeIdentifier($_GET['sort']);
$q = Builder::table('users')->orderBy($sortColumn . ' DESC')->build();

// ✅ SAFE - Safe raw expression with user column
$q = Builder::table('orders')
    ->select([Builder::rawSafe('SUM({col})', ['col' => $_GET['column']])->value])
    ->build();

// ❌ DANGEROUS - Never concatenate user input in raw()
$q = Builder::table('orders')
    ->select([Builder::raw("SUM({$_GET['column']})")])
    ->build();

use Tracy\Debugger;
use KnifeLemon\EasyQuery\Builder;

// Enable Tracy (development only)
Debugger::enable();

// That's it! Just use EasyQuery normally
$q = Builder::table('users')
    ->select(['id', 'name', 'email'])
    ->where(['status' => 'active'])
    ->orderBy('created_at DESC')
    ->limit(10)
    ->build();

// All queries are automatically logged to Tracy panel
// No manual initialization needed!