您现在的位置：程式碼之家 >> 編程語言 > >> 網頁程式設計 >> PHP 程式設計 >> PHP 綜合知識

PHP MySQL SQL 建構器類：參數化生成 SELECT/INSERT/UPDATE/DELETE

編輯：PHP 綜合知識

設計目標

透過結構化陣列參數，程式化生成標準 SQL 語句，減少字串拼接錯誤，統一參數綁定，支援 WHERE 條件、JOIN、ORDER BY、LIMIT 等子句。

現代實作：Fluent SQL Builder

<?php
class QueryBuilder {
    private $pdo;
    private $table;
    private $columns = ['*'];
    private $wheres = [];
    private $joins = [];
    private $orders = [];
    private $limit = null;
    private $offset = null;
    private $bindings = [];
    private $distinct = false;

    public function __construct(PDO $pdo) {
        $this->pdo = $pdo;
    }

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

    public function select(array|string $columns = ['*']): self {
        $this->columns = is_array($columns) ? $columns : func_get_args();
        return $this;
    }

    public function distinct(bool $flag = true): self {
        $this->distinct = $flag;
        return $this;
    }

    // WHERE 條件
    public function where(string $column, string $operator, $value, string $boolean = 'AND'): self {
        $this->wheres[] = ['type' => 'basic', 'column' => $column, 'operator' => $operator, 'value' => $value, 'boolean' => $boolean];
        return $this;
    }

    public function whereIn(string $column, array $values, string $boolean = 'AND'): self {
        $this->wheres[] = ['type' => 'in', 'column' => $column, 'values' => $values, 'boolean' => $boolean];
        return $this;
    }

    public function whereLike(string $column, string $value, string $boolean = 'AND'): self {
        return $this->where($column, 'LIKE', $value, $boolean);
    }

    public function orWhere(string $column, string $operator, $value): self {
        return $this->where($column, $operator, $value, 'OR');
    }

    // JOIN
    public function join(string $table, string $first, string $operator, string $second, string $type = 'INNER'): self {
        $this->joins[] = compact('table', 'first', 'operator', 'second', 'type');
        return $this;
    }

    public function leftJoin(string $table, string $first, string $operator, string $second): self {
        return $this->join($table, $first, $operator, $second, 'LEFT');
    }

    // ORDER BY
    public function orderBy(string $column, string $direction = 'ASC'): self {
        $this->orders[] = [$column, strtoupper($direction)];
        return $this;
    }

    // LIMIT / OFFSET
    public function limit(int $limit): self {
        $this->limit = $limit;
        return $this;
    }

    public function offset(int $offset): self {
        $this->offset = $offset;
        return $this;
    }

    // 建構 SELECT SQL
    public function toSql(): string {
        $sql = 'SELECT ';
        if ($this->distinct) $sql .= 'DISTINCT ';
        $sql .= implode(', ', array_map(fn($c) => $this->wrap($c), $this->columns));
        $sql .= ' FROM ' . $this->wrap($this->table);

        // JOINs
        foreach ($this->joins as $j) {
            $sql .= " {$j['type']} JOIN {$this->wrap($j['table'])} ON {$this->wrap($j['first'])} {$j['operator']} {$this->wrap($j['second'])}";
        }

        // WHERE
        if ($this->wheres) {
            $sql .= ' WHERE ';
            foreach ($this->wheres as $i => $w) {
                if ($i > 0) $sql .= " {$w['boolean']} ";
                if ($w['type'] === 'in') {
                    $placeholders = implode(',', array_fill(0, count($w['values']), '?'));
                    $sql .= "{$this->wrap($w['column'])} IN ($placeholders)";
                    $this->bindings = array_merge($this->bindings, $w['values']);
                } else {
                    $sql .= "{$this->wrap($w['column'])} {$w['operator']} ?";
                    $this->bindings[] = $w['value'];
                }
            }
        }

        // ORDER BY
        if ($this->orders) {
            $sql .= ' ORDER BY ' . implode(', ', array_map(fn($o) => "{$this->wrap($o[0])} {$o[1]}", $this->orders));
        }

        // LIMIT / OFFSET
        if ($this->limit !== null) {
            $sql .= ' LIMIT ' . (int)$this->limit;
            if ($this->offset !== null) $sql .= ' OFFSET ' . (int)$this->offset;
        }

        return $sql;
    }

    public function getBindings(): array {
        return $this->bindings;
    }

    public function get(): array {
        $sql = $this->toSql();
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($this->getBindings());
        return $stmt->fetchAll();
    }

    public function first(): ?array {
        $this->limit(1);
        $results = $this->get();
        return $results[0] ?? null;
    }

    public function count(): int {
        $clone = clone $this;
        $clone->columns = ['COUNT(*) as count'];
        $clone->orders = [];
        $clone->limit = null;
        $clone->offset = null;
        $result = $clone->first();
        return (int)($result['count'] ?? 0);
    }

    private function wrap(string $identifier): string {
        return '`' . str_replace('`', '``', $identifier) . '`';
    }

    // INSERT
    public function insert(array $data): int|false {
        $columns = array_keys($data);
        $placeholders = implode(',', array_fill(0, count($columns), '?'));
        $sql = "INSERT INTO {$this->wrap($this->table)} (" . implode(',', array_map([$this, 'wrap'], $columns)) . ") VALUES ($placeholders)";
        $stmt = $this->pdo->prepare($sql);
        if ($stmt->execute(array_values($data))) {
            return (int)$this->pdo->lastInsertId();
        }
        return false;
    }

    // UPDATE
    public function update(array $data): int {
        $sets = [];
        $bindings = [];
        foreach ($data as $col => $val) {
            $sets[] = "{$this->wrap($col)} = ?";
            $bindings[] = $val;
        }
        $sql = "UPDATE {$this->wrap($this->table)} SET " . implode(', ', $sets);
        if ($this->wheres) {
            $sql .= ' WHERE ';
            foreach ($this->wheres as $i => $w) {
                if ($i > 0) $sql .= " {$w['boolean']} ";
                $sql .= "{$this->wrap($w['column'])} {$w['operator']} ?";
                $bindings[] = $w['value'];
            }
        }
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($bindings);
        return $stmt->rowCount();
    }

    // DELETE
    public function delete(): int {
        $sql = "DELETE FROM {$this->wrap($this->table)}";
        $bindings = [];
        if ($this->wheres) {
            $sql .= ' WHERE ';
            foreach ($this->wheres as $i => $w) {
                if ($i > 0) $sql .= " {$w['boolean']} ";
                $sql .= "{$this->wrap($w['column'])} {$w['operator']} ?";
                $bindings[] = $w['value'];
            }
        }
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($bindings);
        return $stmt->rowCount();
    }
}
?>

使用範例

<?php
require_once 'QueryBuilder.php';
$qb = new QueryBuilder($pdo);

// SELECT 範例
$users = $qb->table('users')
    ->select(['id', 'name', 'email', 'created_at'])
    ->where('status', '=', 1)
    ->whereLike('name', '%John%')
    ->orderBy('created_at', 'DESC')
    ->limit(10)
    ->get();

// 產生 SQL：
// SELECT `id`, `name`, `email`, `created_at` FROM `users` 
// WHERE `status` = ? AND `name` LIKE ? ORDER BY `created_at` DESC LIMIT 10
// Bindings: [1, '%John%']

// JOIN 範例
$posts = $qb->table('posts')
    ->select(['posts.id', 'posts.title', 'users.name as author'])
    ->leftJoin('users', 'posts.user_id', '=', 'users.id')
    ->where('posts.published', '=', 1)
    ->orderBy('posts.created_at', 'DESC')
    ->limit(5)
    ->get();

// INSERT
$newId = $qb->table('users')->insert([
    'name' => 'Alice',
    'email' => 'alice@example.com',
    'password_hash' => password_hash('secret', PASSWORD_DEFAULT),
    'status' => 1,
]);

// UPDATE
$affected = $qb->table('users')
    ->where('id', '=', $newId)
    ->update(['name' => 'Alice Updated']);

// DELETE
$deleted = $qb->table('users')
    ->where('id', '=', $newId)
    ->delete();

// 分頁輔助
$page = max(1, (int)($_GET['page'] ?? 1));
$perPage = 15;
$total = $qb->table('products')->where('active', '=', 1)->count();
$products = $qb->table('products')
    ->where('active', '=', 1)
    ->orderBy('id', 'DESC')
    ->limit($perPage)
    ->offset(($page - 1) * $perPage)
    ->get();