萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> PHP 程式設計

PHP 多資料庫抽象類別:同時支援 MySQL、SQLite、PDO

單一類別封裝 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;');

核心類別架構 (sqldbs.class.php)

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):統一執行查詢,回傳結果集資源/物件,失敗記錄錯誤訊息至 $errorMessage
  • fetchAssoc($res):取得關聯陣列(PDO 使用 PDO::FETCH_ASSOC)
  • fetchArray($res):取得數值索引陣列
  • rowCount($res):取得筆數(PDO 需 fetchAll() 計算,會移動指標)
  • isResultSet($res):判斷結果集是否有資料
  • disconnect():依 method 正確關閉連線

設計優點

  • 單一介面操作多種資料庫
  • 自動偵測最佳驅動 (PDO > 原生)
  • 統一錯誤處理與字元集設定 (MySQL utf8)
  • 持久連線支援 (PDO::ATTR_PERSISTENT)
copyright © 萬盛學電腦網 all rights reserved