PHP 數據庫連接類：PDO 風格封裝與最佳實踐

類別概覽

Db 類封裝了 MySQL 連接、查詢、結果集獲取、事務相關操作，採用類 PDO 風格介面，支援持久連接、字符集設置、錯誤異常處理。

核心代碼結構

class Db {
  public $conn;           // 連接資源
  public $root = 'localhost';
  public $user = 'root';
  public $pass = 'root';
  public $db = '111cn.cn';
  public $charset = 'gb2312';
  public $links = 'c';    // 'p'持久連接, 'c'普通連接
  
  function __construct() { $this->connect(); }
  
  function connect() {
    try {
      if ('p' == $this->links) {
        $this->conn = mysql_pconnect($this->root, $this->user, $this->pass);
      } else {
        $this->conn = mysql_connect($this->root, $this->user, $this->pass);
      }
      mysql_select_db($this->db, $this->conn);
      mysql_query("SET NAMES '$this->charset'");
    } catch (Exception $e) {
      echo '數據庫連接失敗'; exit;
    }
  }
  
  function query($sql) {
    $this->row = mysql_query($sql, $this->conn) or die(mysql_error());
    return $this->row;
  }
  
  function rows($row) { return mysql_num_rows($row); }
  
  function fetch($row, $tag=1) {
    if (1 == $tag) return mysql_fetch_array($row);
    $array = [];
    while ($rs = mysql_fetch_array($row)) $array[] = $rs;
    return $array;
  }
  
  function insert_id() { return @mysql_insert_id($this->row); }
  function close() { return @mysql_close($this->conn); }
  function version() { /* ... */ }
}

使用示例

$Db = new Db();
$query = $Db->query("SELECT id, typetitle FROM cn_111cn WHERE typeupid=0 ORDER BY orderid");
$rows = $Db->fetch($query, 0);
foreach ($rows as $v) {
  echo "{$v[1]}";
}
$Db->close();

關鍵問題分析

現代重構：PDO 版本

class Database {
  private PDO $pdo;
  private string $dsn;
  
  public function __construct(array $config) {
    $this->dsn = "mysql:host={$config['host']};dbname={$config['dbname']};charset={$config['charset']}";
    $this->pdo = new PDO(
      $this->dsn,
      $config['user'],
      $config['pass'],
      [
        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 {$config['charset']}"
      ]
    );
  }
  
  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 = []) {
    return $this->query($sql, $params)->fetch();
  }
  
  public function execute(string $sql, array $params = []): int {
    return $this->query($sql, $params)->rowCount();
  }
  
  public function lastInsertId(): string {
    return $this->pdo->lastInsertId();
  }
  
  public function transaction(callable $callback) {
    $this->pdo->beginTransaction();
    try {
      $result = $callback($this);
      $this->pdo->commit();
      return $result;
    } catch (Exception $e) {
      $this->pdo->rollBack();
      throw $e;
    }
  }
  
  public function getPdo(): PDO { return $this->pdo; }
}

使用新版示例

$db = new Database([
  'host' => 'localhost', 'dbname' => 'myapp', 
  'user' => 'user', 'pass' => 'secret', 'charset' => 'utf8mb4'
]);

// 安全查詢
$categories = $db->fetchAll(
  "SELECT id, name FROM categories WHERE parent_id = ? ORDER BY sort",
  [$parentId]
);

// 事務操作
$db->transaction(function($db) use ($data) {
  $db->execute("INSERT INTO orders (user_id, total) VALUES (?, ?)", [$uid, $total]);
  $orderId = $db->lastInsertId();
  foreach ($data as $item) {
    $db->execute("INSERT INTO order_items (order_id, product_id, qty) VALUES (?, ?, ?)", 
      [$orderId, $item['pid'], $item['qty']]);
  }
  return $orderId;
});