php 73 lines · 10 steps

Building a fluent SQL query builder in PHP

A chainable class assembles a parameterized SELECT statement piece by piece, then runs it safely through PDO.

Explained by highlit
Intermediate fluent-interface method-chaining sql prepared-statements builder-pattern
1<?php
2 
3namespace App\Database;
4 
5use PDO;
6 
7class QueryBuilder
8{
9 private string $table;
10 private array $columns = ['*'];
11 private array $wheres = [];
12 private array $bindings = [];
13 private array $orders = [];
14 private ?int $limit = null;
15 
16 public function __construct(private PDO $pdo)
17 {
18 }
19 
20 public function table(string $table): static
21 {
22 $this->table = $table;
23 return $this;
24 }
25 
26 public function select(string ...$columns): static
27 {
28 $this->columns = $columns ?: ['*'];
29 return $this;
30 }
31 
32 public function where(string $column, string $operator, mixed $value): static
33 {
34 $this->wheres[] = "{$column} {$operator} ?";
35 $this->bindings[] = $value;
36 return $this;
37 }
38 
39 public function orderBy(string $column, string $direction = 'asc'): static
40 {
41 $direction = strtolower($direction) === 'desc' ? 'DESC' : 'ASC';
42 $this->orders[] = "{$column} {$direction}";
43 return $this;
44 }
45 
46 public function limit(int $count): static
47 {
48 $this->limit = $count;
49 return $this;
50 }
51 
52 public function get(): array
53 {
54 $sql = 'SELECT ' . implode(', ', $this->columns) . ' FROM ' . $this->table;
55 
56 if ($this->wheres) {
57 $sql .= ' WHERE ' . implode(' AND ', $this->wheres);
58 }
59 
60 if ($this->orders) {
61 $sql .= ' ORDER BY ' . implode(', ', $this->orders);
62 }
63 
64 if ($this->limit !== null) {
65 $sql .= ' LIMIT ' . $this->limit;
66 }
67 
68 $statement = $this->pdo->prepare($sql);
69 $statement->execute($this->bindings);
70 
71 return $statement->fetchAll(PDO::FETCH_ASSOC);
72 }
73}
01 / 01
STEP 01

Walkthrough

Space play ←→ step click any line
Three takeaways
  1. 1Returning $this from each setter lets callers chain calls into a readable query expression.
  2. 2Collecting clauses and bindings separately keeps user values out of the raw SQL string.
  3. 3Deferring SQL assembly to a single terminal method centralizes how the query is compiled and executed.

Related explainers

php 
<?php
 
class NameParser
{

Parsing a full name into components in PHP

string-parsing arrays normalization
Intermediate 8 steps
php 
<?php
 
namespace App\Services\Checkout;
 

Validating coupons with Laravel's Pipeline

pipeline chain of responsibility transactions
Intermediate 7 steps
php 
<?php
 
namespace App\Services;
 

How a password strength validator works in PHP

validation regular-expressions data-driven
Intermediate 8 steps
php 
<?php
 
namespace App\Services;
 

Building a cached daily leaderboard in Laravel

caching aggregation eager-loading
Intermediate 9 steps
php 
<?php
 
final class RotatingFileLogger
{

How a rotating file logger works in PHP

logging file-rotation io
Intermediate 9 steps
php 
class TwoFactorController extends Controller
{
    public function show(Request $request): View|RedirectResponse
    {

Two-factor auth challenge flow in Laravel

authentication two-factor middleware
Intermediate 10 steps

Share this explainer

Here's the card — post it anywhere.

Building a fluent SQL query builder in PHP — share card
Made with highlit — turn any snippet into a walkthrough like this in about a minute.
Explain your code