PDO (PHP Data Objects) 提供統一的資料庫存取介面,支援多種資料庫驅動。本文展示一套完整的 PDO 封裝,包含單例模式連線管理、欄位映射防注入、以及 CRUD 核心操作方法。
extension=pdo_mysql(或對應資料庫驅動)<?php
class DPDO {
private $DSN;
private $DBUser;
private $DBPwd;
private $longLink;
private $pdo;
// 私有建構子,防止直接實例化
private function __construct($dsn, $DBUser, $DBPwd, $longLink = false) {
$this->DSN = $dsn;
$this->DBUser = $DBUser;
$this->DBPwd = $DBPwd;
$this->longLink = $longLink;
$this->connect();
}
// 防止複製
private function __clone() {}
// 靜態工廠方法,支援多連線配置
static public function instance($dsn, $DBUser, $DBPwd, $longLink = false) {
static $singleton = array();
$singIndex = md5($dsn . $DBUser . $DBPwd . $longLink);
if (empty($singleton[$singIndex])) {
$singleton[$singIndex] = new self($dsn, $DBUser, $DBPwd, $longLink);
}
return $singleton[$singIndex]->pdo;
}
private function connect() {
try {
$options = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION);
if ($this->longLink) {
$options[PDO::ATTR_PERSISTENT] = true;
}
$this->pdo = new PDO($this->DSN, $this->DBUser, $this->DBPwd, $options);
$this->pdo->query('SET NAMES UTF8');
} catch (PDOException $e) {
die('Connection Error: ' . $e->getMessage());
}
}
}
<?php
// 批次欄位映射:用於 INSERT/UPDATE 的 SET 子句或 WHERE 條件
public function FDFields($data, $link = ',', $judge = array(), $aliasTable = '') {
$sql = '';
$mapData = array();
foreach ($data as $key => $value) {
$mapIndex = ':' . ($link != ',' ? 'c' : '') . $aliasTable . $key;
$sql .= ' ' . ($aliasTable ? $aliasTable . '.' : '') . '`' . $key . '` ' . ($judge[$key] ? $judge[$key] : '=') . ' ' . $mapIndex . ' ' . $link;
$mapData[$mapIndex] = $value;
}
return array(trim($sql, $link), $mapData);
}
// 單一欄位映射
public function FDField($field, $value, $judge = '=', $preMap = 'cn', $aliasTable = '') {
$mapIndex = ':' . $preMap . $aliasTable . $field;
$sql = ' ' . ($aliasTable ? $aliasTable . '.' : '') . '`' . $field . '`' . $judge . $mapIndex;
return array($sql, array($mapIndex => $value));
}
// 條件組合處理
public function FDCondition($condition, $mapData) {
if (is_string($condition)) {
return array($condition, $mapData);
}
if (is_array($condition)) {
$where = $condition['str'] ?? '';
if (isset($condition['data']) && is_array($condition['data'])) {
$link = $condition['link'] ?? 'AND';
list($conSql, $mapConData) = $this->FDFields($condition['data'], $link, $condition['judge'] ?? array());
if ($conSql) {
$where .= ($where ? ' ' . $link . ' ' : '') . $conSql;
$mapData = array_merge($mapData, $mapConData);
}
}
return array($where, $mapData);
}
return array('', $mapData);
}
<?php
class DB {
private $pdo;
public function __construct(PDO $pdo) {
$this->pdo = $pdo;
}
// 單筆查詢
public function fetch($sql, $params = array(), $mode = PDO::FETCH_ASSOC) {
$stmt = $this->pdo->prepare($sql);
$stmt->execute($params);
return $stmt->fetch($mode);
}
// 多筆查詢(支援分頁)
public function fetchAll($sql, $params = array(), $limit = array(0, 10), $mode = PDO::FETCH_ASSOC) {
$sql .= ' LIMIT ' . (int)$limit[0] . ',' . (intval($limit[1]) > 0 ? intval($limit[1]) : 10);
$stmt = $this->pdo->prepare($sql);
$stmt->execute($params);
return $stmt->fetchAll($mode);
}
// 新增資料
public function insert($table, $data, $returnId = false) {
list($fields, $values) = $this->buildFieldValue($data);
$sql = "INSERT INTO `$table` ($fields) VALUES ($values)";
$stmt = $this->pdo->prepare($sql);
$stmt->execute($data);
return $returnId ? $this->pdo->lastInsertId() : $stmt->rowCount();
}
// 更新資料
public function update($table, $data, $where, $whereParams = array()) {
list($setClause, $params) = $this->FDFields($data, ',');
$sql = "UPDATE `$table` SET $setClause WHERE $where";
$stmt = $this->pdo->prepare($sql);
return $stmt->execute(array_merge($params, $whereParams));
}
// 刪除資料
public function delete($table, $where, $params = array()) {
$sql = "DELETE FROM `$table` WHERE $where";
$stmt = $this->pdo->prepare($sql);
return $stmt->execute($params);
}
// 執行任意 SQL(非查詢)
public function exec($sql, $params = array()) {
$stmt = $this->pdo->prepare($sql);
return $stmt->execute($params);
}
private function buildFieldValue($data) {
$fields = array_keys($data);
$placeholders = ':' . implode(', :', $fields);
return array('`' . implode('`, `', $fields) . '`', $placeholders);
}
}
<?php
// 取得 PDO 實例
$pdo = DPDO::instance('mysql:host=localhost;dbname=test', 'user', 'pass');
$db = new DB($pdo);
// 新增
$id = $db->insert('users', array(
'username' => 'john',
'email' => 'john@example.com',
'created_at' => date('Y-m-d H:i:s')
), true);
// 查詢
$user = $db->fetch('SELECT * FROM users WHERE id = :id', array(':id' => $id));
// 更新
$db->update('users', array('email' => 'new@example.com'), 'id = :id', array(':id' => $id));
// 刪除
$db->delete('users', 'id = :id', array(':id' => $id));
// 複雜查詢
$users = $db->fetchAll(
'SELECT * FROM users WHERE status = :status ORDER BY created_at DESC',
array(':status' => 1),
array(0, 20)
);
SET NAMES UTF8 避免中文亂碼此架構適合中大型專案,提供清晰的資料存取層,便於維護與擴充。