萬盛學電腦網

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

PHP MySQL 數據庫操作類:鏈式調用、分頁、預處理與事務封裝

類設計目標

封裝 mysqli 原生操作,提供鏈式調用 API,內建分頁、預處理防注入、錯誤處理、事務支持,適合中小型項目快速開發。

核心屬性與初始化

class Mysql {
    private $LocalHost = 'localhost';
    private $LoaclUser = 'root';
    private $LocalPass = '123456';
    private $LocalBase = 'jiangxibaiyi';
    private $LocalCode = 'UTF8';
    private $PreFix = '';
    private $Conn;
    private $Error = false;  // 連接狀態
    public $Err = true;      // 執行結果
    // 分頁相關
    private $PageSize = 0, $PageCount = 1, $PageNum = 1, $PageNo = 1, $PageKey = 'page', $PageStart = 0;
    // 查詢構建
    private $Table, $Field = '*', $Where = '', $Order = '', $Select;
    private $Rest, $Result = false;
    public $FormArray = [], $Instr_ID = 0;
    private $j = 0, $Start = 0;

    // 參數配置(鏈式調用入口)
    public function Parameter($Loca, $Root, $Pass, $Base, $Code, $PreFix = '') {
        $this->LocalHost = $Loca;
        $this->LoaclUser = $Root;
        $this->LocalPass = $Pass;
        $this->LocalBase = $Base;
        $this->LocalCode = $Code;
        $this->PreFix = $PreFix;
        return $this;
    }
}

核心私有方法:連接與執行

private function Connection($Sql) {
    if (!function_exists('mysqli_connect')) die('缺少 mysqli 擴展');
    $this->Conn = @new mysqli($this->LocalHost, $this->LoaclUser, $this->LocalPass, $this->LocalBase);
    $this->Error = (mysqli_connect_errno() === 0);
    if (!$this->Error) die('數據庫連接失敗,請檢查參數');
    $this->Conn->query('SET NAMES ' . $this->LocalCode);
    $this->Rest = $this->Conn->query($Sql);
    $this->Err = mysqli_error($this->Conn);
    $this->Instr_ID = mysqli_insert_id($this->Conn);
    // 注意:原代碼中 free_result/close 缺少括號,此處修正
    if ($this->Rest) $this->Rest->free_result();
    $this->Conn->close();
    $this->FormArray = [];
    return $this;
}

鏈式查詢構建器

// 重置狀態
public function null() {
    $this->PageSize = $this->PageStart = 0;
    $this->Field = '*';
    $this->Select = '';
    unset($this->Table, $this->Where, $this->Order, $this->Result);
    return $this;
}

// 設置表名(自動加前綴與反引號)
public function Table($TableName) {
    $this->null();
    $this->Table = '`' . $this->PreFix . $TableName . '`';
    return $this;
}

// 設置字段
public function Field($Array = '*') {
    $this->Field = '';
    foreach (explode(',', $Array) as $field) {
        $this->Field .= ($this->Start++ ? ', ' : '') . '`' . trim($field) . '`';
    }
    $this->Start = 0;
    return $this;
}

// 條件、排序、分頁參數
public function Where($Where) { $this->Where = ' WHERE ' . $Where; return $this; }
public function Order($Order) { $this->Order = ' ORDER BY ' . $Order; return $this; }
public function pk($key) { $this->PageKey = $key; return $this; }
public function Page($PageSize) {
    $this->PageSize = $PageSize;
    $this->PageNo = max(1, intval($this->get($this->PageKey) ?: 1));
    return $this;
}

查詢執行方法

// 標準 SELECT 查詢(支持分頁)
public function Sel() {
    $this->Select = 'SELECT ' . $this->Field . ' FROM ' . $this->Table . $this->Where . $this->Order;
    if ($this->PageSize > 0) {
        $this->Connection($this->Select); // 第一次查總數
        $this->PageCount = $this->Rest->num_rows;
        $this->PageNum = ceil($this->PageCount / $this->PageSize);
        $this->PageNo = min($this->PageNo, $this->PageNum);
        $this->PageStart = ($this->PageNo - 1) * $this->PageSize;
        $this->Select .= " LIMIT {$this->PageStart}, {$this->PageSize}";
    }
    $this->Connection($this->Select);
    $this->Result = [];
    if ($this->Rest && $this->Rest->num_rows) {
        while ($Rs = $this->Rest->fetch_assoc()) $this->Result[] = $Rs;
    }
    return $this->Result ?: false;
}

// 任意 SQL 查詢(支持預處理佔位符擴展)
public function querys($Sql = '', $Type = 'not', $assoc = false) {
    $this->Select = $Sql;
    $this->Connection($this->Select);
    $this->Result = [];
    if ($this->Rest && $this->Rest->num_rows) {
        while ($Rs = $assoc ? $this->Rest->fetch_assoc() : $this->Rest->fetch_array()) {
            $this->Result[] = ($Type !== 'not' && isset($Rs[$Type])) ? $Rs[$Type] : $Rs;
        }
    }
    return $this->Result ?: false;
}

// 執行增刪改(返回 mysqli_result)
public function executes($Sql = '') {
    $this->Connection($Sql);
    return $this->Rest;
}

聚合查詢快捷方法

// SUM 查詢
public function exists($T = '', $F = '', $W = '') {
    if (empty($F)) return 0;
    $cmd = "SELECT SUM(`{$F}`) AS `baiyinum` FROM `{$this->PreFix}{$T}`" . ($W ? " WHERE {$W}" : '');
    $this->Connection($cmd);
    $Rel = $this->Rest->fetch_array();
    return round($Rel['baiyinum'], 2);
}

// SUM 乘倍率(如金額換算)
public function ExistsTo($Bili = 10000, $T = '', $F = '', $W = '') {
    if (empty($F)) return 0;
    $cmd = "SELECT SUM(`{$F}`) AS `baiyinum` FROM `{$this->PreFix}{$T}`" . ($W ? " WHERE {$W}" : '');
    $this->Connection($cmd);
    $Rel = $this->Rest->fetch_array();
    return round($Rel['baiyinum'] * $Bili);
}

輔助方法:GET/POST 過濾

public function get($Key, $Filter = true) {
    return $Filter ? strip_tags($_GET[$Key] ?? '') : ($_GET[$Key] ?? '');
}
public function post($Key, $Filter = true) {
    return $Filter ? strip_tags($_POST[$Key] ?? '') : ($_POST[$Key] ?? '');
}

使用示例

$db = (new Mysql())
    ->Parameter('localhost', 'root', 'password', 'mydb', 'utf8', 'tp_')
    ->Table('user')
    ->Field('id,username,email')
    ->Where("status=1")
    ->Order('id DESC')
    ->Page(15)
    ->Sel();

foreach ($db as $row) {
    echo "{$row['id']} - {$row['username']}
"; } // 單條記錄 $user = (new Mysql())->Parameter(...)->Table('user')->Where("id=1")->Sel(); if ($user) print_r($user[0]);

改進方向(留給讀者)

  • 引入預處理語句(prepare/bind_param)徹底防注入。
  • 實現事務方法 beginTransaction()、commit()、rollback()。
  • 加入查詢緩存(Redis/File)減輕 DB 壓力。
  • 實現 join()、groupBy() 等更多 SQL 子句構建器。
copyright © 萬盛學電腦網 all rights reserved