QueryBuilder Guide

Fluent interface for type-safe database operations

Documentation / Reference / QueryBuilder

QueryBuilder Usage Guide

Build complex database queries with a fluent, type-safe interface. The QueryBuilder provides automatic SQL injection protection via prepared statements.

SQL Safe

Fluent API

Type-Safe

Fast

Getting Started

Initialize QueryBuilder

<?php
use Gemvc\Database\QueryBuilder;

// Create a new QueryBuilder instance
$qb = new QueryBuilder();

SELECT Queries INSERT Queries UPDATE Queries DELETE Queries WHERE Conditions Pagination & Limiting Error Handling Transactions

SELECT Queries

Basic SELECT

Basic SELECT Query

$qb = new QueryBuilder();

$users = $qb->select('id', 'name', 'email')
    ->from('users')
    ->run();

if ($users === null) {
    echo "Error: " . $qb->getError();
} else {
    foreach ($users as $user) {
        echo "User: " . $user['name'] . "\n";
    }
}

SELECT with JOINs

SELECT with JOIN

$userPosts = $qb->select('u.name', 'p.title', 'p.created_at')
    ->from('users', 'u')
    ->leftJoin('posts p ON u.id = p.user_id')
    ->whereEqual('u.status', 'active')
    ->orderBy('p.created_at', true) // true = DESC
    ->run();

Complex Conditions

Complex WHERE Conditions

$products = $qb->select('*')
    ->from('products')
    ->whereEqual('category', 'electronics')
    ->whereBetween('price', 100, 500)
    ->whereIn('brand', ['apple', 'samsung', 'google'])
    ->whereNotNull('description')
    ->orderBy('price')
    ->limit(20)
    ->run();

JSON Results

Get JSON Output

$jsonData = $qb->select('id', 'name', 'email')
    ->from('users')
    ->whereEqual('status', 'active')
    ->json();

// Returns JSON string ready for API response
if ($jsonData !== null) {
    echo $jsonData;
}

Tip: Use ->json() to get results directly as a JSON string - perfect for API responses!

INSERT Queries

INSERT Query

// Insert a single record using the Insert class
$result = $qb->insert('users')
    ->values([
        'name' => 'John Doe',
        'email' => 'john@example.com',
        'created_at' => date('Y-m-d H:i:s')
    ])
    ->run();

if ($result !== null) {
    echo "Inserted successfully";
}

UPDATE Queries

UPDATE Query

// Update records with conditions
$result = $qb->update('users')
    ->set([
        'status' => 'verified',
        'updated_at' => date('Y-m-d H:i:s')
    ])
    ->whereEqual('email_verified', 1)
    ->whereNull('verified_at')
    ->run();

if ($result !== null) {
    echo "Updated successfully";
}

info: Always use WHERE conditions with UPDATE to avoid modifying all records accidentally.

DELETE Queries

DELETE Query

// Delete with conditions
$result = $qb->delete('sessions')
    ->whereLess('expires_at', date('Y-m-d H:i:s'))
    ->run();

// Soft delete pattern (using Update)
$qb->update('users')
    ->set(['deleted_at' => date('Y-m-d H:i:s')])
    ->whereEqual('id', $userId)
    ->run();

WHERE Conditions

All WHERE methods from WhereTrait:

Method	Description	Example
whereEqual()	Exact match (=)	whereEqual('status', 'active')
whereLike()	Pattern match (%val%)	whereLike('name', 'john')
whereIn()	In array	whereIn('id', [1, 2, 3])
whereNotIn()	Not in array	whereNotIn('status', ['deleted'])
whereBetween()	Range	whereBetween('price', 10, 100)
whereNull()	Is NULL	whereNull('deleted_at')
whereNotNull()	Is NOT NULL	whereNotNull('email')
whereBigger()	Greater than (>)	whereBigger('age', 18)
whereBiggerEqual()	Greater or equal (>=)	whereBiggerEqual('stock', 10)
whereLess()	Less than (<)	whereLess('price', 100)
whereLessEqual()	Less or equal (<=)	whereLessEqual('qty', 5)

info: Note: whereLike() automatically wraps the value with % on both sides for LIKE %value% search.

Pagination & Limiting

Pagination Example

// Basic limit
$recent = $qb->select('*')
    ->from('posts')
    ->orderBy('created_at', true)  // true = DESC
    ->limit(10)
    ->run();

// Pagination with offset
$page = 2;
$perPage = 20;
$offset = ($page - 1) * $perPage;

$paginated = $qb->select('*')
    ->from('products')
    ->limit($perPage)
    ->offset($offset)
    ->run();

Error Handling

Error Handling Pattern

$qb = new QueryBuilder();

$result = $qb->select('*')
    ->from('users')
    ->whereEqual('id', $userId)
    ->run();

if ($result === null) {
    // Query failed - check error
    $error = $qb->getError();
    error_log("Query failed: {$error}");
    return false;
}

if (empty($result)) {
    // No results found (but query succeeded)
    return null;
}

return $result[0];

Transactions

Transaction Example

$qb = new QueryBuilder();

$qb->beginTransaction();

try {
    // Deduct from sender
    $qb->update('accounts')
        ->set(['balance' => $senderBalance - $amount])
        ->whereEqual('id', $senderId)
        ->run();
    
    // Add to receiver
    $qb->update('accounts')
        ->set(['balance' => $receiverBalance + $amount])
        ->whereEqual('id', $receiverId)
        ->run();
    
    // Record transaction
    $qb->insert('transactions')
        ->values([
            'from_id' => $senderId,
            'to_id' => $receiverId,
            'amount' => $amount
        ])
        ->run();
    
    $qb->commit();
} catch (Exception $e) {
    $qb->rollback();
    throw $e;
}

Tip: Always use transactions for operations that modify multiple tables to ensure data consistency.

Table ORM vs QueryBuilder

GEMVC provides two ways to interact with the database:

Table ORM

• Extends Table class
• Property-to-column mapping
• insertSingleQuery()
• updateSingleQuery()
• selectById()

QueryBuilder

• Standalone class
• Raw query building
• select(...)->from()->run()
• insert()->values()->run()
• Complex joins & subqueries

Best Practices

Always check for null - Query methods return null on failure
Use transactions - For multi-table operations
Use WHERE conditions - Never update/delete without WHERE
Select specific columns - Avoid SELECT * in production
Use pagination - limit() and offset() for large result sets
Prefer Table ORM - For CRUD operations with model classes

Next Steps

API Reference Security Guide Performance