單一類別封裝 MySQL、SQLite 2/3、PDO 介面,統一資料庫操作 API,簡化跨資料庫開發。
// MySQL 連線
$db = new SQL('mysql:host=localhost;database=my_db;', 'user', 'pass');
// PDO SQLite3 連線
$db = new SQL('pdo:database=/path/to/db.sqlite3;');
// SQLite2 連線
$db = new SQL('sqlite:database=/path/to/db.sqlite;');
class SQL {
var $adapter = ""; // mysql / sqlite
var $method = ""; // mysql / pdo / sqlite
var $version = "";
var $conn = ""; // 連線資源/物件
var $options = ""; // 連線參數陣列
var $errorMessage = "";
var $db = ""; // 目前資料庫名
function SQL($connString, $user = "", $pass = "") {
// 解析連線字串:adapter:key=val;key=val
list($this->adapter, $options) = explode(":", $connString, 2);
if ($this->adapter != "sqlite") $this->adapter = "mysql";
$optionsList = explode(";", $options);
foreach ($optionsList as $option) {
list($a, $b) = explode("=", $option);
$opt[$a] = $b;
}
$this->options = $opt;
$database = isset($opt['database']) ? $opt['database'] : "";
// 依 adapter 與環境自動選擇 method
if ($this->adapter == "sqlite" && substr(sqlite_libversion(), 0, 1) == "3" && class_exists("PDO") && in_array("sqlite", PDO::getAvailableDrivers())) {
$this->method = "pdo";
try { $this->conn = new PDO("sqlite:" . $database, null, null, array(PDO::ATTR_PERSISTENT => true)); }
catch (PDOException $e) { $this->conn = false; $this->errorMessage = $e->getMessage(); }
}
elseif ($this->adapter == "sqlite" && substr(sqlite_libversion(), 0, 1) == "2" && class_exists("PDO") && in_array("sqlite2", PDO::getAvailableDrivers())) {
$this->method = "pdo";
try { $this->conn = new PDO("sqlite2:" . $database, null, null, array(PDO::ATTR_PERSISTENT => true)); }
catch (PDOException $e) { $this->conn = false; $this->errorMessage = $e->getMessage(); }
}
elseif ($this->adapter == "sqlite") {
$this->method = "sqlite";
$this->conn = sqlite_open($database, 0666, $sqliteError);
}
else {
$this->method = "mysql";
$host = isset($opt['host']) ? $opt['host'] : "";
$this->conn = @mysql_connect($host, $user, $pass);
}
// 通用後處理
if ($this->conn && $this->method == "pdo") $this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);
if ($this->conn && $this->adapter == "mysql") $this->query("SET NAMES 'utf8'");
if ($this->conn && $database) $this->db = $database;
}
// 統一介面方法:isConnected, close, disconnect, getAdapter, getMethod, getOptionValue
// selectDB, query, rowCount, num_rows, isResultSet, fetch, fetchArray, fetchAssoc...
}
query($sql):統一執行查詢,回傳結果集資源/物件,失敗記錄錯誤訊息至 $errorMessagefetchAssoc($res):取得關聯陣列(PDO 使用 PDO::FETCH_ASSOC)fetchArray($res):取得數值索引陣列rowCount($res):取得筆數(PDO 需 fetchAll() 計算,會移動指標)isResultSet($res):判斷結果集是否有資料disconnect():依 method 正確關閉連線