原文提供的 Database 類別使用 mysql_* 函數,**已在 PHP 7.0 完全移除**。且存在:
<?php
declare(strict_types=1);
class Database
{
private PDO $pdo;
private string $tablePrefix = '';
public function __construct(array $config)
{
$dsn = "mysql:host={$config['host']};dbname={$config['name']};charset=utf8mb4";
$options = [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci",
];
$this->pdo = new PDO($dsn, $config['user'], $config['pass'], $options);
if (!empty($config['prefix'])) {
$this->tablePrefix = rtrim($config['prefix'], '_') . '_';
}
}
public function getPdo(): PDO
{
return $this->pdo;
}
// === 核心查詢方法 ===
public function query(string $sql, array $params = []): PDOStatement
{
$stmt = $this->pdo->prepare($sql);
$stmt->execute($params);
return $stmt;
}
public function fetchAll(string $sql, array $params = []): array
{
return $this->query($sql, $params)->fetchAll();
}
public function fetchOne(string $sql, array $params = []): array|false
{
return $this->query($sql, $params)->fetch();
}
public function fetchColumn(string $sql, array $params = [], int $column = 0): mixed
{
return $this->query($sql, $params)->fetchColumn($column);
}
// === CRUD 封裝 ===
public function insert(string $table, array $data): int
{
$table = $this->prefix($table);
$fields = implode(', ', array_keys($data));
$placeholders = ':' . implode(', :', array_keys($data));
$sql = "INSERT INTO `$table` ($fields) VALUES ($placeholders)";
$this->query($sql, $data);
return (int)$this->pdo->lastInsertId();
}
public function insertBatch(string $table, array $rows): int
{
if (empty($rows)) return 0;
$table = $this->prefix($table);
$fields = array_keys($rows[0]);
$fieldList = implode(', ', array_map(fn($f) => "`$f`", $fields));
$placeholders = '(' . implode(', ', array_map(fn($f) => ":$f", $fields)) . ')';
$allPlaceholders = implode(', ', array_fill(0, count($rows), $placeholders));
$sql = "INSERT INTO `$table` ($fieldList) VALUES $allPlaceholders";
$flatParams = [];
foreach ($rows as $i => $row) {
foreach ($row as $k => $v) {
$flatParams["{$k}_{$i}"] = $v;
}
}
$this->query($sql, $flatParams);
return $this->pdo->lastInsertId(); // 返回第一筆 ID
}
public function update(string $table, array $data, string $where, array $whereParams = []): int
{
$table = $this->prefix($table);
$set = implode(', ', array_map(fn($k) => "`$k` = :$k", array_keys($data)));
$sql = "UPDATE `$table` SET $set WHERE $where";
$params = array_merge($data, $whereParams);
$this->query($sql, $params);
return $this->pdo->lastInsertId(); // 實際上應用 rowCount()
}
public function delete(string $table, string $where, array $params = []): int
{
$table = $this->prefix($table);
$sql = "DELETE FROM `$table` WHERE $where";
$this->query($sql, $params);
return $this->pdo->lastInsertId(); // 同理
}
// === 交易支援 ===
public function transaction(callable $callback): mixed
{
$this->pdo->beginTransaction();
try {
$result = $callback($this);
$this->pdo->commit();
return $result;
} catch (Throwable $e) {
$this->pdo->rollBack();
throw $e;
}
}
// === Schema 相關 ===
public function getColumns(string $table): array
{
$table = $this->prefix($table);
return $this->fetchAll("SHOW COLUMNS FROM `$table`");
}
public function getEnumValues(string $table, string $column): array
{
$cols = $this->getColumns($table);
foreach ($cols as $col) {
if ($col['Field'] === $column && preg_match("/^enum\((.+)\)$/", $col['Type'], $m)) {
return array_map(fn($v) => trim($v, "'"), explode(',', $m[1]));
}
}
return [];
}
private function prefix(string $table): string
{
return $this->tablePrefix . $table;
}
}
?>
<?php
require 'Database.php';
$config = [
'host' => 'localhost',
'name' => 'myapp',
'user' => 'app_user',
'pass' => 'secret',
'prefix' => 'app_',
];
$db = new Database($config);
// 查詢
$users = $db->fetchAll('SELECT * FROM users WHERE status = ? AND created_at > ?', ['active', '2024-01-01']);
$user = $db->fetchOne('SELECT * FROM users WHERE id = ?', [123]);
// 新增
$userId = $db->insert('users', [
'username' => 'john',
'email' => 'john@example.com',
'password' => password_hash('secret', PASSWORD_DEFAULT),
'created_at' => date('Y-m-d H:i:s'),
]);
// 批次新增
$db->insertBatch('logs', [
['user_id' => 1, 'action' => 'login', 'ip' => '1.2.3.4'],
['user_id' => 2, 'action' => 'logout', 'ip' => '5.6.7.8'],
]);
// 更新
$db->update('users', ['last_login' => date('Y-m-d H:i:s')], 'id = ?', [123]);
// 刪除
$db->delete('sessions', 'expires_at < ?', [date('Y-m-d H:i:s', strtotime('-30 days'))]);
// 交易
$db->transaction(function ($db) {
$orderId = $db->insert('orders', ['user_id' => 1, 'total' => 100, 'status' => 'pending']);
$db->insert('order_items', ['order_id' => $orderId, 'product_id' => 5, 'qty' => 2, 'price' => 50]);
$db->update('products', ['stock' => 8], 'id = ?', [5]);
});
?>
<?php
// 簡易 Query Builder
class QueryBuilder
{
private Database $db;
private string $table;
private array $wheres = [];
private array $params = [];
private array $orders = [];
private ?int $limit = null;
private ?int $offset = null;
public function __construct(Database $db, string $table)
{
$this->db = $db;
$this->table = $table;
}
public function where(string $column, string $operator, mixed $value): self
{
$this->wheres[] = "`$column` $operator ?";
$this->params[] = $value;
return $this;
}
public function whereIn(string $column, array $values): self
{
$placeholders = implode(', ', array_fill(0, count($values), '?'));
$this->wheres[] = "`$column` IN ($placeholders)";
$this->params = array_merge($this->params, $values);
return $this;
}
public function orderBy(string $column, string $direction = 'ASC'): self
{
$this->orders[] = "`$column` $direction";
return $this;
}
public function limit(int $limit, int $offset = 0): self
{
$this->limit = $limit;
$this->offset = $offset;
return $this;
}
public function get(): array
{
$sql = "SELECT * FROM `{$this->table}`";
if ($this->wheres) $sql .= ' WHERE ' . implode(' AND ', $this->wheres);
if ($this->orders) $sql .= ' ORDER BY ' . implode(', ', $this->orders);
if ($this->limit) $sql .= " LIMIT {$this->offset}, {$this->limit}";
return $this->db->fetchAll($sql, $this->params);
}
public function first(): array|false
{
$this->limit = 1;
$results = $this->get();
return $results[0] ?? false;
}
public function count(): int
{
$sql = "SELECT COUNT(*) FROM `{$this->table}`";
if ($this->wheres) $sql .= ' WHERE ' . implode(' AND ', $this->wheres);
return (int)$this->db->fetchColumn($sql, $this->params);
}
}
// 使用
$users = (new QueryBuilder($db, 'users'))
->where('status', '=', 'active')
->whereIn('role', ['admin', 'editor'])
->orderBy('created_at', 'DESC')
->limit(20)
->get();
?>
mysql_* 替換為 PDOutf8mb4getLastID() 直接用 lastInsertId()getPossibleValues() 改用 getEnumValues()現代化資料庫層不僅修正安全漏洞,更能獲得型別提示、靜態分析、單元測試友善等工程化紅利。建議直接採用成熟 ORM(Eloquent、Doctrine、Cycle ORM)或輕量 Query Builder,避免重複造輪子。