PHP全套PDO資料庫MySql操作

PHP資料庫操作類輪子
使用環境：PHP 8

因為最近想用PHP做一個MUD文字網游，為了方便操作資料庫就造了個資料庫操作的輪子，使用時直接require即可，輪子最后放出，先來演示使用方法吧

打個廣告：https://github.com/Drizzle365/fairy，這是正在制作的開源游戲

演示（假設資料庫操作類為mysql.php）：

1.查詢某一條資料

<?php
require_once 'lib/mysql.php';  //引入咱寫的物件
$db = new Mysql();             //new個物件
/*
資料庫操作模式：鏈式操作
table函式引數為資料表，
field為獲取的欄位，*為全部欄位
where就好理解了，里面可以放文本也可以放陣列
item回傳一條資料,資料形式為陣列
*/
$db->table('user')->field('*')->where('Id=1')->item();

回傳資料截圖：

2.查詢多條資料

<?php
require_once 'lib/mysql.php';
$db = new Mysql();
$a = $db->table('user')->field('*')->where('Id>1')->list(3);
//list為回傳數量，里面引數為空則回傳表中所有資料，為n則范圍n條資料

截圖演示：

3.查詢資料總數

require_once 'lib/mysql.php';
$db = new Mysql();
$a = $db->table('user')->field('*')->where('')->count();

截圖演示：

4.查詢分頁資料

<?php
require_once 'lib/mysql.php';
$db = new Mysql();
$a = $db->table('user')->field('*')->where('Id>0')->pages(1,2);
//page里面的2個引數，第一個為當前頁數，第二個為每頁的大小

回傳資料為陣列，在[‘total’]內有總數，[‘data’]內有資料
截圖演示：

5.洗掉資料

<?php
require_once 'lib/mysql.php';
$db = new Mysql();
$a = $db->table('user')->field('*')->where('Id>0')->delete();

此操作回傳的是影響的資料行數，即洗掉的資料行數

6.插入資料

<?php
require_once 'lib/mysql.php';
$db = new Mysql();
$a = $db->table('user')->insert(array('Id'=>5,'name'=>'drizzle'));
//INSERT引數為插入的資料，必須是陣列形式，
//如上圖意義為插入一個ID欄位為5，name欄位為dirzzle的資料

輪子：

<?php

use JetBrains\PhpStorm\ArrayShape;
use JetBrains\PhpStorm\Pure;

class Mysql
{
    //查詢表名
    private string $table;
    private string $filed;
    private mixed $where;
    private PDO $pdo;
    private string $order;
    private string $order_mode;
    private string $limit;

    public function __construct()//構造器
    {
        $dsn = "mysql:host=127.0.0.1;dbname=xm";
        $this->pdo = new PDO($dsn, 'xm', 'snXKiw6wL3yz3Wc7');
    }

    public function table($table): Mysql
    {
        $this->table = $table;
        return $this;
    }

    //查詢欄位
    public function field($field): Mysql
    {
        $this->filed = $field;
        return $this;
    }

    //查詢條件
    public function where($where): Mysql
    {
        $this->where = $where;
        return $this;
    }

    //封裝where陳述句
    #[Pure] private function build_where(): string
    {
        $where = '';
        if (is_array($where)) {
            foreach ($this->where as $key => $value) {
                $value = is_string($value) ? "'" . $value . "'" : $value;
                $where .= "{$key} = {$value} and ";
            }
        } else {
            $where = $this->where;
        }
        $where = rtrim($where, ' and ');
        if ($where) {
            $where = "where {$where}";
        }
        return $where;
    }

    //封裝Sql陳述句

    /** @noinspection SqlWithoutWhere */
    #[Pure] private function build_sql($type, $data = null): string
    {
        $sql = '';
        if ($type == 'select') {
            $where = $this->build_where();
            $sql = "select {$this->filed} from {$this->table} {$where}";
            if (isset($this->order)) {
                $sql .= " order by `{$this->order}` {$this->order_mode}";
            }
            if (isset($this->limit)) {
                $sql .= " limit {$this->limit}";
            }

        }
        if ($type == 'insert') {
            $k = '';
            $v = '';
            foreach ($data as $key => $value) {
                $k .= $key . ',';
                $value = is_string($value) ? "'$value'" : $value;
                $v .= $value . ',';
            }
            $k = rtrim($k, ',');
            $v = rtrim($v, ',');
            $sql = "insert into {$this->table}($k) value($v) ";

        }
        if ($type == 'delete') {
            $where = $this->build_where();
            $sql = "delete from {$this->table} {$where}";
        }
        if ($type == 'update') {
            $where = $this->build_where();
            $set = '';
            foreach ($data as $key => $value) {
                $value = is_string($value) ? "'" . $value . "'" : $value;
                $set .= "{$key}={$value},";
            }
            $set = rtrim($set, ',');
            $set = $set ? " set {$set}" : $set;
            $sql = "update {$this->table} {$set} {$where}";
        }
        if ($type == 'count') {
            $where = $this->build_where();
            $sql = "select count(*) from {$this->table} {$where}";
        }
        //echo $sql;exit();
        return $sql;
    }

    //查詢結果排序
    public function order($order, $order_mode): Mysql
    {
        $this->order = $order;
        $this->order_mode = $order_mode;
        return $this;
    }

    //回傳一條資料
    public function item()
    {
        $sql = $this->build_sql('select') . " limit 1";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute();
        $res = $stmt->fetchAll(PDO::FETCH_ASSOC);
        return isset($res[0]) ? $res[0] : false;
    }

    //回傳多條資料
    public function list($list_num = null): array
    {
        $sql = $this->build_sql('select');
        if (isset($list_num)) {
            $sql .= " limit {$list_num}";
        }
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    //查詢資料總數
    public function count()
    {
        $sql = $this->build_sql('count');
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute();
        return $stmt->fetchColumn();
    }

    //分頁
    #[ArrayShape(['total' => "mixed", 'date' => "array"])] public function pages($page, $page_size = 10): array
    {
        $count = $this->count();
        $this->limit = ($page - 1) * $page_size . ',' . $page_size;
        $data = $this->list();
        return array('total' => $count, 'date' => $data);
    }

    //插入資料
    public function insert($data): int
    {
        $sql = $this->build_sql('insert', $data);
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute();
        return $this->pdo->lastInsertId();
    }

    //洗掉資料,并回傳影響行數
    public function delete(): int
    {
        $sql = $this->build_sql('delete');
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute();
        return $stmt->rowCount();
    }

    //更新資料,并回傳影響行數
    public function update($data): int
    {
        $sql = $this->build_sql('update', $data);
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute();
        return $stmt->rowCount();
    }
}