I've been looking at a piece of legacy code in one of my old projects, and it's a nightmare. There's this one product filtering page where the developer just kept concatenating strings to a $sql variable based on whether a user checked a box or filled in a price range. It looked something like this:

$sql = "SELECT * FROM products WHERE 1=1";
if ($category) {
    $sql .= " AND category_id = " . (int)$category;
}
if ($minPrice) {
    $sql .= " AND price >= " . (float)$minPrice;
}
// ... and so on for ten more fields

It "works," but it's brittle. One missing space before an AND and the whole query crashes. Plus, adding a LIMIT or an ORDER BY at the end feels like an afterthought. I want something cleaner. Let's try to build a tiny Query Builder to handle this state for us.

Fighting with String Glue

My first instinct is to just make a class that holds these pieces. I don't want to worry about the final string until the very last second. Let's start with something that can track which table we're using and what filters we've applied.

class QueryBuilder {
    protected $table;
    protected $wheres = [];

    public function table($table) {
        $this->table = $table;
        return $this; 
    }

    public function where($column, $value) {
        $this->wheres[] = "$column = '$value'";
        return $this;
    }

    public function getSql() {
        $sql = "SELECT * FROM {$this->table}";
        if (!empty($this->wheres)) {
            $sql .= " WHERE " . implode(' AND ', $this->wheres);
        }
        return $sql;
    }
}

$query = new QueryBuilder();
echo $query->table('products')->where('category', 'electronics')->where('status', 'active')->getSql();
// Output: SELECT * FROM products WHERE category = 'electronics' AND status = 'active'

The return $this; part is the "magic" here. It allows method chaining, which makes the code read more like a sentence and less like a checklist. But I immediately see a problem: I've hardcoded the equals sign =. What if I want "greater than" for a price filter?

Adding Flexibility to the Filters

If I change the where method to accept an operator, I can handle more cases. I'll just add a third argument with a default value of =. While I'm at it, I'll realize that putting quotes directly in the string is a security disaster (SQL injection), but for the sake of understanding the builder pattern, I'll focus on the structure first and handle the values separately.

public function where($column, $value, $operator = '=') {
    $this->wheres[] = [
        'column' => $column,
        'operator' => $operator,
        'value' => $value
    ];
    return $this;
}

public function getSql() {
    $sql = "SELECT * FROM {$this->table}";
    if (!empty($this->wheres)) {
        $clauses = [];
        foreach ($this->wheres as $where) {
            $clauses[] = "{$where['column']} {$where['operator']} '{$where['value']}'";
        }
        $sql .= " WHERE " . implode(' AND ', $clauses);
    }
    return $sql;
}

Now I can do ->where('price', 100, '>'). It's getting better. But a real query isn't just a SELECT and a WHERE. I usually need to limit the results or sort them. If I just add them to the end of the string, I have to make sure they happen in the correct SQL order: WHERE, then ORDER BY, then LIMIT.

Managing the Order of Operations

I can't just append things to the SQL string as they are called. If the user calls ->limit(10)->where('status', 'active'), the SQL would be invalid. I need to store each "part" of the query in its own bucket and assemble them in the correct sequence inside getSql().

class QueryBuilder {
    protected $table;
    protected $wheres = [];
    protected $orderBy = null;
    protected $limit = null;

    public function table($table) {
        $this->table = $table;
        return $this;
    }

    public function where($column, $value, $operator = '=') {
        $this->wheres[] = compact('column', 'value', 'operator');
        return $this;
    }

    public function orderBy($column, $direction = 'ASC') {
        $this->orderBy = "$column $direction";
        return $this;
    }

    public function limit($number) {
        $this->limit = (int)$number;
        return $this;
    }

    public function getSql() {
        $sql = "SELECT * FROM {$this->table}";

        if ($this->wheres) {
            $clauses = array_map(fn($w) => "{$w['column']} {$w['operator']} '{$w['value']}'", $this->wheres);
            $sql .= " WHERE " . implode(' AND ', $clauses);
        }

        if ($this->orderBy) {
            $sql .= " ORDER BY {$this->orderBy}";
        }

        if ($this->limit) {
            $sql .= " LIMIT {$this->limit}";
        }

        return $sql;
    }
}

I used compact() here just to be concise—it's a handy way to turn those variables into an array. Now, regardless of the order I call the methods in my PHP code, the getSql() method ensures the generated SQL string follows the strict rules of the database engine.

One final thought: in a production environment, you would never actually put '{$w['value']}' directly in the string. You'd replace that with a placeholder like ? and keep an array of values to pass to a prepared statement. But as a structural exercise, we've just turned a mess of if statements into a reusable, fluent object.

📋 Practical Task

Exercise: Adding an Offset for Pagination

Our current QueryBuilder can limit the number of results, but it can't skip any. To implement pagination, we need an OFFSET clause.

Your Task: Modify the QueryBuilder class to support an offset() method. This method should allow you to specify how many rows to skip before starting to return results. Ensure that in the getSql() method, the OFFSET is added to the query string after the LIMIT clause (or as part of the limit logic), as per standard SQL syntax.

Test your implementation by chaining table(), where(), limit(), and offset() to produce a query like: SELECT * FROM products WHERE status = 'active' LIMIT 10 OFFSET 20.