PHP对MySql的增删改查的链式封装

PHP数据库操作类轮子
使用环境：PHP 8

个人博客：https://dearain.cn
Github：https://github.com/Drizzle365

先演示使用，最后放出全部代码：

演示（假设数据库操作类为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();
    }
}