1. php
  2. /database
  3. /query-builder

Query Builder in PHP

Introduction to Query Builder

A Query Builder is a programming pattern that allows you to construct SQL queries programmatically using an object-oriented interface. Instead of writing raw SQL strings, you use methods to build queries dynamically, making your code more maintainable, readable, and less prone to SQL injection attacks.

Benefits of Query Builder

  1. Dynamic Query Construction: Build queries based on conditions
  2. SQL Injection Prevention: Automatic parameter binding
  3. Database Agnostic: Abstract away database-specific syntax
  4. Readable Code: Method chaining creates fluent interfaces
  5. Reusable Components: Share query logic across applications
  6. IDE Support: Better autocomplete and type safety

Basic Query Builder Implementation

Simple Query Builder Class

<?php
class QueryBuilder
{
    private $pdo;
    private $table;
    private $select = ['*'];
    private $where = [];
    private $joins = [];
    private $orderBy = [];
    private $groupBy = [];
    private $having = [];
    private $limit;
    private $offset;
    private $bindings = [];
    
    public function __construct(PDO $pdo)
    {
        $this->pdo = $pdo;
    }
    
    public function table(string $table): self
    {
        $this->table = $table;
        return $this;
    }
    
    public function select(array $columns = ['*']): self
    {
        $this->select = $columns;
        return $this;
    }
    
    public function where(string $column, string $operator, $value): self
    {
        $this->where[] = [
            'column' => $column,
            'operator' => $operator,
            'value' => $value,
            'boolean' => 'AND'
        ];
        return $this;
    }
    
    public function orWhere(string $column, string $operator, $value): self
    {
        $this->where[] = [
            'column' => $column,
            'operator' => $operator,
            'value' => $value,
            'boolean' => 'OR'
        ];
        return $this;
    }
    
    public function join(string $table, string $first, string $operator, string $second): self
    {
        $this->joins[] = [
            'type' => 'INNER',
            'table' => $table,
            'first' => $first,
            'operator' => $operator,
            'second' => $second
        ];
        return $this;
    }
    
    public function leftJoin(string $table, string $first, string $operator, string $second): self
    {
        $this->joins[] = [
            'type' => 'LEFT',
            'table' => $table,
            'first' => $first,
            'operator' => $operator,
            'second' => $second
        ];
        return $this;
    }
    
    public function orderBy(string $column, string $direction = 'ASC'): self
    {
        $this->orderBy[] = ['column' => $column, 'direction' => $direction];
        return $this;
    }
    
    public function groupBy(array $columns): self
    {
        $this->groupBy = array_merge($this->groupBy, $columns);
        return $this;
    }
    
    public function having(string $column, string $operator, $value): self
    {
        $this->having[] = [
            'column' => $column,
            'operator' => $operator,
            'value' => $value
        ];
        return $this;
    }
    
    public function limit(int $limit): self
    {
        $this->limit = $limit;
        return $this;
    }
    
    public function offset(int $offset): self
    {
        $this->offset = $offset;
        return $this;
    }
    
    public function toSql(): string
    {
        $sql = 'SELECT ' . implode(', ', $this->select);
        $sql .= ' FROM ' . $this->table;
        
        // Add JOINs
        foreach ($this->joins as $join) {
            $sql .= " {$join['type']} JOIN {$join['table']} ON {$join['first']} {$join['operator']} {$join['second']}";
        }
        
        // Add WHERE clauses
        if (!empty($this->where)) {
            $sql .= ' WHERE ';
            $whereClause = [];
            foreach ($this->where as $index => $condition) {
                $placeholder = ':where_' . $index;
                $this->bindings[$placeholder] = $condition['value'];
                
                $clause = "{$condition['column']} {$condition['operator']} {$placeholder}";
                
                if ($index > 0) {
                    $clause = "{$condition['boolean']} {$clause}";
                }
                
                $whereClause[] = $clause;
            }
            $sql .= implode(' ', $whereClause);
        }
        
        // Add GROUP BY
        if (!empty($this->groupBy)) {
            $sql .= ' GROUP BY ' . implode(', ', $this->groupBy);
        }
        
        // Add HAVING
        if (!empty($this->having)) {
            $sql .= ' HAVING ';
            $havingClause = [];
            foreach ($this->having as $index => $condition) {
                $placeholder = ':having_' . $index;
                $this->bindings[$placeholder] = $condition['value'];
                $havingClause[] = "{$condition['column']} {$condition['operator']} {$placeholder}";
            }
            $sql .= implode(' AND ', $havingClause);
        }
        
        // Add ORDER BY
        if (!empty($this->orderBy)) {
            $orderClause = [];
            foreach ($this->orderBy as $order) {
                $orderClause[] = "{$order['column']} {$order['direction']}";
            }
            $sql .= ' ORDER BY ' . implode(', ', $orderClause);
        }
        
        // Add LIMIT and OFFSET
        if ($this->limit) {
            $sql .= ' LIMIT ' . $this->limit;
        }
        
        if ($this->offset) {
            $sql .= ' OFFSET ' . $this->offset;
        }
        
        return $sql;
    }
    
    public function getBindings(): array
    {
        return $this->bindings;
    }
    
    public function get(): array
    {
        $stmt = $this->pdo->prepare($this->toSql());
        $stmt->execute($this->getBindings());
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    
    public function first(): ?array
    {
        $this->limit(1);
        $results = $this->get();
        return $results[0] ?? null;
    }
    
    public function count(): int
    {
        $originalSelect = $this->select;
        $this->select(['COUNT(*) as count']);
        
        $result = $this->first();
        $this->select = $originalSelect;
        
        return (int) ($result['count'] ?? 0);
    }
    
    public function exists(): bool
    {
        return $this->count() > 0;
    }
}
?>

Advanced Query Builder Features

Insert Query Builder

<?php
class InsertQueryBuilder
{
    private $pdo;
    private $table;
    private $data = [];
    private $onDuplicate = [];
    
    public function __construct(PDO $pdo)
    {
        $this->pdo = $pdo;
    }
    
    public function into(string $table): self
    {
        $this->table = $table;
        return $this;
    }
    
    public function values(array $data): self
    {
        $this->data = $data;
        return $this;
    }
    
    public function onDuplicateKeyUpdate(array $columns): self
    {
        $this->onDuplicate = $columns;
        return $this;
    }
    
    public function toSql(): string
    {
        $columns = implode(', ', array_keys($this->data));
        $placeholders = ':' . implode(', :', array_keys($this->data));
        
        $sql = "INSERT INTO {$this->table} ({$columns}) VALUES ({$placeholders})";
        
        if (!empty($this->onDuplicate)) {
            $updateClauses = [];
            foreach ($this->onDuplicate as $column) {
                $updateClauses[] = "{$column} = VALUES({$column})";
            }
            $sql .= ' ON DUPLICATE KEY UPDATE ' . implode(', ', $updateClauses);
        }
        
        return $sql;
    }
    
    public function execute(): bool
    {
        $stmt = $this->pdo->prepare($this->toSql());
        return $stmt->execute($this->data);
    }
    
    public function getLastInsertId(): string
    {
        return $this->pdo->lastInsertId();
    }
}
?>

Update Query Builder

<?php
class UpdateQueryBuilder
{
    private $pdo;
    private $table;
    private $set = [];
    private $where = [];
    private $bindings = [];
    
    public function __construct(PDO $pdo)
    {
        $this->pdo = $pdo;
    }
    
    public function table(string $table): self
    {
        $this->table = $table;
        return $this;
    }
    
    public function set(array $data): self
    {
        $this->set = array_merge($this->set, $data);
        return $this;
    }
    
    public function where(string $column, string $operator, $value): self
    {
        $this->where[] = [
            'column' => $column,
            'operator' => $operator,
            'value' => $value
        ];
        return $this;
    }
    
    public function toSql(): string
    {
        $setClauses = [];
        foreach ($this->set as $column => $value) {
            $placeholder = ':set_' . $column;
            $this->bindings[$placeholder] = $value;
            $setClauses[] = "{$column} = {$placeholder}";
        }
        
        $sql = "UPDATE {$this->table} SET " . implode(', ', $setClauses);
        
        if (!empty($this->where)) {
            $whereClauses = [];
            foreach ($this->where as $index => $condition) {
                $placeholder = ':where_' . $index;
                $this->bindings[$placeholder] = $condition['value'];
                $whereClauses[] = "{$condition['column']} {$condition['operator']} {$placeholder}";
            }
            $sql .= ' WHERE ' . implode(' AND ', $whereClauses);
        }
        
        return $sql;
    }
    
    public function execute(): int
    {
        $stmt = $this->pdo->prepare($this->toSql());
        $stmt->execute($this->bindings);
        return $stmt->rowCount();
    }
}
?>

Delete Query Builder

<?php
class DeleteQueryBuilder
{
    private $pdo;
    private $table;
    private $where = [];
    private $bindings = [];
    
    public function __construct(PDO $pdo)
    {
        $this->pdo = $pdo;
    }
    
    public function from(string $table): self
    {
        $this->table = $table;
        return $this;
    }
    
    public function where(string $column, string $operator, $value): self
    {
        $this->where[] = [
            'column' => $column,
            'operator' => $operator,
            'value' => $value
        ];
        return $this;
    }
    
    public function toSql(): string
    {
        $sql = "DELETE FROM {$this->table}";
        
        if (!empty($this->where)) {
            $whereClauses = [];
            foreach ($this->where as $index => $condition) {
                $placeholder = ':where_' . $index;
                $this->bindings[$placeholder] = $condition['value'];
                $whereClauses[] = "{$condition['column']} {$condition['operator']} {$placeholder}";
            }
            $sql .= ' WHERE ' . implode(' AND ', $whereClauses);
        }
        
        return $sql;
    }
    
    public function execute(): int
    {
        $stmt = $this->pdo->prepare($this->toSql());
        $stmt->execute($this->bindings);
        return $stmt->rowCount();
    }
}
?>

Unified Database Manager

<?php
class Database
{
    private $pdo;
    
    public function __construct(PDO $pdo)
    {
        $this->pdo = $pdo;
    }
    
    public function table(string $table): QueryBuilder
    {
        return (new QueryBuilder($this->pdo))->table($table);
    }
    
    public function select(array $columns = ['*']): QueryBuilder
    {
        return (new QueryBuilder($this->pdo))->select($columns);
    }
    
    public function insert(): InsertQueryBuilder
    {
        return new InsertQueryBuilder($this->pdo);
    }
    
    public function update(): UpdateQueryBuilder
    {
        return new UpdateQueryBuilder($this->pdo);
    }
    
    public function delete(): DeleteQueryBuilder
    {
        return new DeleteQueryBuilder($this->pdo);
    }
    
    public function raw(string $sql, array $bindings = []): array
    {
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($bindings);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    
    public function transaction(callable $callback)
    {
        $this->pdo->beginTransaction();
        
        try {
            $result = $callback($this);
            $this->pdo->commit();
            return $result;
        } catch (Exception $e) {
            $this->pdo->rollBack();
            throw $e;
        }
    }
}
?>

Practical Usage Examples

Basic Queries

<?php
// Create database instance
$pdo = new PDO('mysql:host=localhost;dbname=myapp', $username, $password);
$db = new Database($pdo);

// Simple SELECT
$users = $db->table('users')
    ->select(['id', 'name', 'email'])
    ->where('status', '=', 'active')
    ->orderBy('created_at', 'DESC')
    ->limit(10)
    ->get();

// Single record
$user = $db->table('users')
    ->where('id', '=', 123)
    ->first();

// Count records
$userCount = $db->table('users')
    ->where('status', '=', 'active')
    ->count();

// Check existence
$hasActiveUsers = $db->table('users')
    ->where('status', '=', 'active')
    ->exists();
?>

Complex Queries with Joins

<?php
// Users with their profiles and posts count
$users = $db->table('users')
    ->select([
        'users.id',
        'users.name',
        'users.email',
        'profiles.bio',
        'COUNT(posts.id) as posts_count'
    ])
    ->leftJoin('profiles', 'users.id', '=', 'profiles.user_id')
    ->leftJoin('posts', 'users.id', '=', 'posts.user_id')
    ->where('users.status', '=', 'active')
    ->groupBy(['users.id', 'users.name', 'users.email', 'profiles.bio'])
    ->having('posts_count', '>', 0)
    ->orderBy('posts_count', 'DESC')
    ->get();

// Dynamic search with multiple conditions
function searchUsers($db, $filters)
{
    $query = $db->table('users')->select(['*']);
    
    if (!empty($filters['name'])) {
        $query->where('name', 'LIKE', '%' . $filters['name'] . '%');
    }
    
    if (!empty($filters['email'])) {
        $query->where('email', 'LIKE', '%' . $filters['email'] . '%');
    }
    
    if (!empty($filters['status'])) {
        $query->where('status', '=', $filters['status']);
    }
    
    if (!empty($filters['min_age'])) {
        $query->where('age', '>=', $filters['min_age']);
    }
    
    if (!empty($filters['max_age'])) {
        $query->where('age', '<=', $filters['max_age']);
    }
    
    return $query->orderBy('created_at', 'DESC')->get();
}
?>

Insert, Update, Delete Operations

<?php
// Insert new user
$userId = $db->insert()
    ->into('users')
    ->values([
        'name' => 'John Doe',
        'email' => '[email protected]',
        'password' => password_hash('secret', PASSWORD_DEFAULT),
        'created_at' => date('Y-m-d H:i:s')
    ])
    ->execute();

$lastId = $db->insert()->getLastInsertId();

// Insert with conflict resolution
$db->insert()
    ->into('user_settings')
    ->values([
        'user_id' => 123,
        'setting_key' => 'theme',
        'setting_value' => 'dark'
    ])
    ->onDuplicateKeyUpdate(['setting_value'])
    ->execute();

// Update user
$affectedRows = $db->update()
    ->table('users')
    ->set([
        'name' => 'Jane Doe',
        'email' => '[email protected]',
        'updated_at' => date('Y-m-d H:i:s')
    ])
    ->where('id', '=', 123)
    ->execute();

// Delete inactive users
$deletedRows = $db->delete()
    ->from('users')
    ->where('status', '=', 'inactive')
    ->where('last_login', '<', date('Y-m-d H:i:s', strtotime('-1 year')))
    ->execute();
?>

Advanced Features

Subqueries

<?php
class SubqueryBuilder extends QueryBuilder
{
    public function whereIn(string $column, QueryBuilder $subquery): self
    {
        $this->where[] = [
            'column' => $column,
            'operator' => 'IN',
            'value' => '(' . $subquery->toSql() . ')',
            'boolean' => 'AND',
            'raw' => true
        ];
        
        // Merge bindings from subquery
        $this->bindings = array_merge($this->bindings, $subquery->getBindings());
        
        return $this;
    }
    
    public function whereExists(QueryBuilder $subquery): self
    {
        $this->where[] = [
            'column' => 'EXISTS',
            'operator' => '',
            'value' => '(' . $subquery->toSql() . ')',
            'boolean' => 'AND',
            'raw' => true
        ];
        
        $this->bindings = array_merge($this->bindings, $subquery->getBindings());
        
        return $this;
    }
}

// Usage: Find users who have posted in the last month
$recentPosters = $db->table('users')
    ->whereIn('id', 
        $db->table('posts')
            ->select(['user_id'])
            ->where('created_at', '>', date('Y-m-d H:i:s', strtotime('-1 month')))
    )
    ->get();
?>

Aggregation and Reporting

<?php
// User statistics with grouping
$userStats = $db->table('users')
    ->select([
        'status',
        'COUNT(*) as user_count',
        'AVG(age) as average_age',
        'MIN(created_at) as first_registration',
        'MAX(created_at) as last_registration'
    ])
    ->groupBy(['status'])
    ->orderBy('user_count', 'DESC')
    ->get();

// Monthly registration report
$monthlyRegistrations = $db->table('users')
    ->select([
        'DATE_FORMAT(created_at, "%Y-%m") as month',
        'COUNT(*) as registrations'
    ])
    ->where('created_at', '>=', date('Y-m-d H:i:s', strtotime('-1 year')))
    ->groupBy(['month'])
    ->orderBy('month', 'ASC')
    ->get();
?>

Pagination Helper

<?php
class PaginatedResult
{
    public $data;
    public $total;
    public $page;
    public $perPage;
    public $totalPages;
    
    public function __construct($data, $total, $page, $perPage)
    {
        $this->data = $data;
        $this->total = $total;
        $this->page = $page;
        $this->perPage = $perPage;
        $this->totalPages = ceil($total / $perPage);
    }
    
    public function hasNextPage(): bool
    {
        return $this->page < $this->totalPages;
    }
    
    public function hasPreviousPage(): bool
    {
        return $this->page > 1;
    }
}

// Add pagination to QueryBuilder
class QueryBuilder // extends previous implementation
{
    public function paginate(int $page = 1, int $perPage = 15): PaginatedResult
    {
        $total = $this->count();
        $offset = ($page - 1) * $perPage;
        
        $data = $this->limit($perPage)->offset($offset)->get();
        
        return new PaginatedResult($data, $total, $page, $perPage);
    }
}

// Usage
$users = $db->table('users')
    ->where('status', '=', 'active')
    ->orderBy('created_at', 'DESC')
    ->paginate($_GET['page'] ?? 1, 20);

echo "Page {$users->page} of {$users->totalPages}";
echo "Showing {$users->perPage} of {$users->total} results";
?>

Performance Optimization

Query Caching

<?php
class CachedQueryBuilder extends QueryBuilder
{
    private $cache;
    private $cacheTtl = 3600;
    
    public function cache(int $ttl = null): self
    {
        $this->cacheTtl = $ttl ?? $this->cacheTtl;
        return $this;
    }
    
    public function get(): array
    {
        if ($this->cacheTtl > 0) {
            $cacheKey = md5($this->toSql() . serialize($this->getBindings()));
            
            if ($this->cache && $cached = $this->cache->get($cacheKey)) {
                return $cached;
            }
            
            $result = parent::get();
            
            if ($this->cache) {
                $this->cache->set($cacheKey, $result, $this->cacheTtl);
            }
            
            return $result;
        }
        
        return parent::get();
    }
}
?>

Query Optimization Tips

<?php
// Use indexes effectively
$users = $db->table('users')
    ->where('email', '=', $email)  // Assume email has unique index
    ->first();

// Limit selected columns
$users = $db->table('users')
    ->select(['id', 'name', 'email'])  // Don't select unnecessary columns
    ->where('status', '=', 'active')
    ->get();

// Use proper joins instead of multiple queries
$posts = $db->table('posts')
    ->select(['posts.*', 'users.name as author_name'])
    ->join('users', 'posts.user_id', '=', 'users.id')
    ->where('posts.published', '=', 1)
    ->get();

// Use LIMIT for large datasets
$recentPosts = $db->table('posts')
    ->orderBy('created_at', 'DESC')
    ->limit(100)  // Limit results to prevent memory issues
    ->get();
?>

Best Practices

  1. Always Use Parameter Binding: Never concatenate user input directly into SQL
  2. Validate Input: Check data types and ranges before building queries
  3. Use Indexes: Ensure WHERE and JOIN conditions use indexed columns
  4. Limit Results: Always use LIMIT for potentially large result sets
  5. Cache Expensive Queries: Cache results of complex queries when appropriate
  6. Monitor Performance: Log slow queries and optimize them
  7. Use Transactions: Group related operations in transactions
  8. Handle Errors: Properly handle database exceptions and connection failures

Query builders provide a powerful abstraction layer over raw SQL while maintaining flexibility and performance. They help create maintainable, secure, and database-agnostic applications.