類別總覽

DbQueryForMysql：輕量級 PHP MySQL 存取封裝，支援預備語句風格查詢、分頁、事務、CRUD 便利方法、錯誤處理與資源管理。

核心屬性

屬性	型別	說明
MAX_ROW_NUM	const int	單次查詢最大回傳行數（預設 1000）
$dataSet	resource	當前查詢結果集
$ds	object	資料源連線物件（含 connect 屬性）
$sql	string	最後執行的 SQL
$transCnt	int	事務嵌套計數器
$executeMode	string	執行模式（保留相容性）

關鍵方法詳解

建構子

function __construct($ds=NULL, $sql=NULL) {
    if (!$ds) throw new DbException('資料庫未連接', DbException::DB_UNCONNECTED);
    $this->ds = $ds;
    if ($sql) $this->open($sql);
}

資源釋放

public function close($dataSet=NULL) {
    @mysql_free_result($dataSet ?: $this->dataSet);
    $this->eof = false; $this->recordCount = 0; $this->recNo = -1;
}

密碼加密

public function encodePassword($pass) {
    return $this->getValue("SELECT PASSWORD('$pass') AS pass");
}

錯誤處理

protected function errorInfo() {
    return ['message' => @mysql_error($this->ds->connect),
            'code'    => @mysql_errno($this->ds->connect)];
}
protected function error($id, $msg) {
    throw new DbException($msg, $id);
}

核心執行：execute() / open()

public function execute($sql='', $rowFrom=0, $rowTo=self::MAX_ROW_NUM, $error=true) {
    // 分頁邏輯：自動拼接 LIMIT offset, count
    if ($rowTo != self::MAX_ROW_NUM || $rowFrom!=0) {
        $nrows = $rowTo - $rowFrom + 1;
        $start = max(0, $rowFrom - 1);
        $sql .= ' LIMIT ' . $start . ',' . $nrows;
    }
    $dataSet = @mysql_query($sql, $this->ds->connect);
    if (!$dataSet && $error) {
        $e = $this->errorInfo();
        $this->error(DbException::DB_QUERY_ERROR,
            "執行[$sql]出錯！
[$e[code]]: $e[message]");
    }
    return $dataSet;
}

public function open($sql='', $rowFrom=0, $rowTo=self::MAX_ROW_NUM) {
    $this->dataSet = $this->execute($sql, $rowFrom, $rowTo);
    $this->sql = $sql;
    return $this->dataSet;
}

資料讀取

public function fetchRecord($dataSet=NULL, $resultType=MYSQL_BOTH) {
    return @mysql_fetch_array($dataSet ?: $this->dataSet, $resultType);
}
public function next() { return $this->fetchRecord(); }

單值/單行快速取得

public function getValue($sql='') {
    $dataSet = $this->execute($sql, 1, 1);
    if ($row = $this->fetchRecord($dataSet)) {
        $this->close($dataSet);
        return ($this->getFieldCount($dataSet) <= 1) ? $row[0] : $row;
    }
    return false;
}
public function getInsertId() { return $this->getValue('SELECT LAST_INSERT_ID()'); }

事務控制

public function begin() {
    $this->transCnt++;
    $this->execute('BEGIN TRAN');
    return true;
}
public function commit() {
    if ($this->transCnt) $this->transCnt--;
    $this->execute('COMMIT TRAN');
    return true;
}
public function rollback() {
    if ($this->transCnt) $this->transCnt--;
    $this->execute('ROLLBACK TRAN');
    return true;
}

CRUD 便利方法

INSERT

public function insert($tableName, $fieldArray, $whereForUnique=NULL) {
    // $fieldArray: ['col1'=>'val1', 'col2'=>'val2', ...]
    // 可選唯一性檢查
    if ($whereForUnique) {
        $cnt = $this->getValue("SELECT COUNT(*) FROM $tableName WHERE $whereForUnique");
        if ($cnt) throw new DbException('記錄已存在', DbException::DB_RECORD_IS_EXISTED);
    }
    $cols = implode(',', array_keys($fieldArray));
    $vals = "'" . implode("','", array_values($fieldArray)) . "'";
    return $this->execute("INSERT INTO $tableName ($cols) VALUES ($vals)");
}

UPDATE

public function update($tableName, $fieldArray, $whereForUpdate=NULL, $whereForUnique=NULL) {
    if ($whereForUnique) { /* 唯一性檢查同 insert */ }
    $set = [];
    foreach ($fieldArray as $k=>$v) if (!is_int($k)) $set[] = "$k='$v'";
    $sql = "UPDATE $tableName SET " . implode(',', $set);
    if ($whereForUpdate) $sql .= " WHERE $whereForUpdate";
    return $this->execute($sql);
}

SELECT（多種回傳格式）

public function select($sql, $format='array', $rowFrom=0, $rowTo=self::MAX_ROW_NUM) {
    $dataSet = $this->execute($sql, $rowFrom, $rowTo);
    switch ($format) {
        case 'array':
            $res = []; $multi = $this->getFieldCount($dataSet) > 1;
            while ($row = $this->fetchRecord($dataSet))
                $res[] = $multi ? $row : $row[0];
            $this->close($dataSet); break;
        case 'hashmap':   // [key=>value] 取前兩欄
            $res = [];
            while ($row = $this->fetchRecord($dataSet))
                $res[$row[0]] = $row[1];
            $this->close($dataSet); break;
        case 'hashmap_str': // "k1=v1|k2=v2"
            $res = [];
            while ($row = $this->fetchRecord($dataSet, MYSQL_NUM))
                $res[] = "$row[0]=$row[1]";
            $this->close($dataSet);
            $res = implode('|', $res); break;
        default: $res = $dataSet; // dataset 原始資源
    }
    return $res;
}

其他工具

public function getMax($table, $field, $where=NULL) {
    $where = $where ? " WHERE $where" : '';
    return $this->getValue("SELECT MAX($field) FROM $table$where");
}
public function tableIsExists($tableName) {
    $res = @mysql_query("SELECT * FROM $tableName LIMIT 0,1", $this->db->connect);
    return $res !== false;
}

使用範例

$db = new DbQueryForMysql($dataSource);

// 單值查詢
$count = $db->getValue("SELECT COUNT(*) FROM users WHERE status=1");

// 分頁查詢（第 2 頁，每頁 20 筆）
$users = $db->select("SELECT * FROM users ORDER BY id DESC", 'array', 21, 40);

// 事務範例
$db->begin();
$db->insert('orders', ['user_id'=>100, 'amount'=>99.9]);
$db->update('accounts', ['balance'=>500], "user_id=100");
$db->commit();

// 取得自增 ID
$newId = $db->getInsertId();

遷移建議

此類別基於已棄用的 mysql_* 函數。**新專案請使用 PDO 或 mysqli**，並搭配預備語句防止 SQL 注入。若維護舊專案，本類別提供可用的封裝參考。