一个简单的基于PDO的数据库操作类

Db.php

<?php    
use PDO;

class Db
{
/*
PDO对象
*/
    protected static $pdo;

/*
配置数组
*/
    protected static $config;

/*
是否开启了事务
*/
    protected static $transaction = false;

/*

执行语句数
*/
    protected static $transaction_exec = 0;

/*
本次事务执行成功语句数
*/
    protected static $transaction_success = 0;

/*
本次事务执行成功并影响了数据的语句数
*/
    protected static $transaction_valid = 0;

/*
初始化
*/
    protected static function init()
    {
        $config = require('config.php');
        self::$config = $config;
        $pdo = new PDO('mysql:host='. $config['location'] .';port='. $config['hostport'] .';dbname='. $config['database'] .';', $config['username'], $config['password']);
        $pdo -> query('set names '. $config['charset']);
        $pdo->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, false);
        $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
        self::$pdo = $pdo;
        include ROOT_PATH.'Query.php';
    }

/*
制定表全名并开始查询
table：完整的表名
*/
    public static function table($table)
    {
        if(!self::$pdo) self::init();
        $querier = new Query(self::$pdo);
        return $querier->table($table);
    }

/*
指定不带前缀的表名并开始查询
name：不带前缀的表名
*/
    public static function name($name)
    {
        if(!self::$pdo) self::init();
        $querier = new Query(self::$pdo);
        return $querier->table(self::$config['prefix'].$name);
    }

/*
开启事务
*/
    public static function startTrans()
    {
        self::$transaction = true;
        self::$transaction_exec = 0;
        self::$transaction_success = 0;
        self::$transaction_valid = 0;
        self::$pdo->beginTransaction();
        return true;
    }

/*
提交事务并判断是否回滚

mode：事务模式
0、无条件提交
1、有SQL语句执行失败即回滚
2、有写入操作影响行数为0即回滚
*/
    public static function commit($mode = 1)
    {
        self::$transaction = false;
        switch($mode)
        {
            case 0:
                self::$pdo->commit();
                return true;
            case 1:
                if(self::$transaction_exec === self::$transaction_success){
                    self::$pdo->commit();
                    return true;
                }else{
                    $this->rollback();
                    return false;
                }
                break;
            case 2:
                if(self::$transaction_exec === self::$transaction_success){
                    if(self::$transaction_exec === self::$transaction_valid){
                        self::$pdo->commit();
                        return true;
                    }
                }
                $this->rollback();
                return false;
            default:
                $this->rollback();
                return false;
        }
    }

/*
回滚事务
*/
    public static function rollback()
    {
        self::$pdo->rollBack();
        self::$transaction = false;
        return true;
    }

/*
执行语句数 + 1
*/
    public static function incExecNum()
    {
        self::$transaction_exec++;
    }

/*
执行成功语句数 + 1
*/
    public static function incSuccessNum()
    {
        self::$transaction_success++;
    }

/*
执行成功并影响数据的语句数 + 1
*/
    public static function incValidNum()
    {
        self::$transaction_valid++;
    }

    public static function error()
    {
        print_r(self::$pdo->errorCode());
        print_r(self::$pdo->errorInfo());
    }
}

Query.php

<?php    
use PDO;

class Query
{
/*
PDO对象
*/
    protected $pdo;

/*
查询表名
*/
    protected $table;

/*
查询字段
*/
    protected $field = '*';

/*
where and条件
*/
    protected $where = [];

/*
where or条件
*/
    protected $whereOr = [];

/*
排序字段
*/
    protected $order = 'id';

/*
限制查询数
*/
    protected $limit;

/*
实例化时存入PDO对象
*/
    public function __construct($pdo)
    {
        $this->pdo = $pdo;
        return $this;
    }

/*
设置表名
*/
    public function table($table)
    {
        $this->table = $table;
        return $this;
    }

/*
设置请求字段
*/
    public function field($field)
    {
        if(strpos($field, ',')){
            $field = explode(',', $field);
            $field_tmp = null;
            foreach($field as $k=>$row){
                if($k > 0){
                    $field_tmp = $field_tmp .',`'. $row .'`';
                }else{
                    $field_tmp = $field_tmp .'`'. $row .'`';
                }
            }
            $field = $field_tmp;
        }
        $this->field = $field;
        return $this;
    }

/*
设置where条件
*/
    public function where($key, $op, $value = null)
    {
        if($value === null){
            $value = $op;
            $op = '=';
        }
        $this->where[] = [
            'key' => $key,
            'op' => $op,
            'value' => $value
        ];
        return $this;
    }

/*
设置where下的or条件
*/
    public function whereOr($key, $op, $value = null)
    {
        if($value === null){
            $value = $op;
            $op = '=';
        }
        $this->whereOr[] = [
            'key' => $key,
            'op' => $op,
            'value' => $value
        ];
        return $this;
    }

/*
生成where条件的SQL语句与数据数组
*/
    protected function whereBuilder()
    {
        $sql = null; $data = [];
        if($this->where){
            foreach($this->where as $k=>$row){
                if($k > 0){
                    $sql = $sql . 'AND `'. $row['key'] .'` '. $row['op'] . ' ';
                }else{
                    $sql = $sql . 'WHERE `'. $row['key'] .'` '. $row['op'] . ' ';
                }
                if($row['op'] === 'in'){
                    $sql = $sql . '(';
                    foreach($row['value'] as $r_k=>$r_v){
                        if($r_k > 0){
                            $sql = $sql . ',?';
                        }else{
                            $sql = $sql . '?';
                        }
                        $data[] = $r_v;
                    }
                    $sql = $sql . ') ';
                }else{
                    $sql = $sql . '? ';
                    $data[] = $row['value'];
                }
            }
        }
        if($this->whereOr){
            foreach($this->whereOr as $row){
                $sql = $sql . 'OR `'. $row['key'] .'` '. $row['op'] . ' ';
                if($row['op'] === 'in'){
                    $sql = $sql . '(';
                    foreach($row['value'] as $r_k=>$r_v){
                        if($r_k > 0){
                            $sql = $sql . ',?';
                        }else{
                            $sql = $sql . '?';
                        }
                        $data[] = $r_v;
                    }
                    $sql = $sql . ') ';
                }else{
                    $sql = $sql . '? ';
                    $data[] = $row['value'];
                }
            }
        }
        return [
            'sql' => $sql,
            'data' => $data
        ];
    }

/*
设置排序字段
*/
    public function order($order)
    {
        $this->order = $order;
        return $this;
    }

/*
设置限制查询数
*/
    public function limit($num)
    {
        $this->limit = $num;
        return $this;
    }

/*
查询特定字段
*/
    public function value($field)
    {
        $param = $this->whereBuilder();
        $sql = 'SELECT `'. $field . '` FROM `'. $this->table .'` '. $param['sql'] .'LIMIT 1;';
        $obj = $this->pdo->prepare($sql);
        if(!$obj) return Db::error();
        $res = $obj->execute($param['data']);
        $list = $obj->fetchAll(PDO::FETCH_ASSOC);
        if(isset($list[0][$field])){
            return $list[0][$field];
        }else{
            return false;
        }
    }

/*
查询一条记录
*/
    public function find()
    {
        $param = $this->whereBuilder();
        $sql = 'SELECT '. $this->field . ' FROM `'. $this->table .'` '. $param['sql'] .'LIMIT 1;';
        $obj = $this->pdo->prepare($sql);
        if(!$obj) return Db::error();
        $res = $obj->execute($param['data']);
        $data = $obj->fetchAll(PDO::FETCH_ASSOC);
        if(isset($data[0])){
            return $data[0];
        }else{
            return false;
        }
    }

/*
查询列表
*/
    public function select()
    {
        $param = $this->whereBuilder();
        $sql = 'SELECT '. $this->field . ' FROM `'. $this->table .'` '. $param['sql'] .' ';
        $sql .= 'ORDER BY '. $this->order . ' ';
        if($this->limit) $sql .= 'LIMIT '. $this->limit .';';
        $obj = $this->pdo->prepare($sql);
        if(!$obj) return Db::error();
        $res = $obj->execute($param['data']);
        $list = $obj->fetchAll(PDO::FETCH_ASSOC);
        return $list;
    }

/*
按页查询
*/
    public function page($num, $key = 'page')
    {
        if(input('?'.$key)){
            if(is_numeric(input($key))){
                $page = intval(input($key));
            }else{
                $page = 1;
            }
        }else{
            $page = 1;
        }
        $param = $this->whereBuilder();
        $sql = 'SELECT '. $this->field . ' FROM `'. $this->table .'` '. $param['sql'] .' ';
        $sql .= 'ORDER BY '. $this->order . ' ';
        $sql .= 'LIMIT '. ($num * ($page - 1)) .','. $num .' ';
        $sql .= ';';
        $obj = $this->pdo->prepare($sql);
        if(!$obj) return Db::error();
        $res = $obj->execute($param['data']);
        $list = $obj->fetchAll(PDO::FETCH_ASSOC);
        
        $count = $this->count('id');

        $url = $_SERVER["REQUEST_URI"];
        if(strpos($url, '?')) $url = substr($url, 0, strpos($url, '?'));
        if(count($_GET) > 0){
            $i = 0;
            foreach($_GET as $get_k=>$get_v){
                if($get_k !== $key){
                    if($i > 0){
                        $url .= '&'. $get_k .'='. $get_v;
                    }else{
                        $url .= '?'. $get_k .'='. $get_v;
                        $i++;
                    }
                }
            }
            if($i > 0){
                $url .= '&page=';
            }else{
                $url .= '?page=';
            }
        }else{
            $url .= '?page=';
        }

        return [
            'data' => $list,
            'url' => $url,
            'limit' => $num,
            'page' => $page,
            'count' => $count
        ];
    }

/*
插入一条记录
*/
    public function insert($data)
    {
        $sql = 'INSERT INTO `'. $this->table .'` ';
        $i = 0; $key = '('; $value = '('; $sub_data = [];
        foreach($data as $k=>$v){
            if($i > 0){
                $key .= ',`'. $k .'`';
                $value .= ',?';
            }else{
                $key .= '`'. $k .'`';
                $value .= '?';
            }
            $i++;
            $sub_data[] = $v;
        }
        $key .= ')'; $value .= ')';
        $sql .= $key . ' VALUES '. $value .';';
        $obj = $this->pdo->prepare($sql);
        if(!$obj) return Db::error();
        $res = $obj->execute($sub_data);
        if($res){
            return $this->pdo->lastinsertid();
        }else{
            return false;
        }
        Db::incExecNum();
        if($res){
            Db::incSuccessNum(); Db::incValidNum();
            return intval($this->pdo->lastinsertid());
        }else{
            return false;
        }
    }

/*
更新记录
*/
    public function update($data)
    {
        $sql = 'UPDATE `'. $this->table .'` SET ';
        $sub_data = []; $i = 0;
        foreach($data as $k=>$v){
            if($i > 0){
                $sql .= ', `'. $k .'` = ? ';
            }else{
                $i++;
                $sql .= '`'. $k .'` = ? ';
            }
            $sub_data[] = $v;
        }
        $param = $this->whereBuilder();
        $sql .= $param['sql'] .' ';
        $param['data'] = array_merge($sub_data, $param['data']);
        if($this->limit) $sql .= 'LIMIT '. $this->limit;
        $sql .= ';';
        $obj = $this->pdo->prepare($sql);
        if(!$obj) return Db::error();
        $res = $obj->execute($param['data']);
        Db::incExecNum();
        if($res){
            Db::incSuccessNum();
            $num = intval($obj->rowCount());
            if($num > 0) Db::incValidNum();
            return $num;
        }else{
            return false;
        }
    }

/*
执行字段自增
*/
    public function setInc($field, $num = 1)
    {
        return $this->setNumQuerier($field, '+', $num);
    }

/*
执行字段自减
*/
    public function setDec($field, $num = 1)
    {
        return $this->setNumQuerier($field, '-', $num);
    }

/*
字段自增自减执行器
*/
    protected function setNumQuerier($field, $op, $num = 1)
    {
        $sql = 'UPDATE `'. $this->table .'` SET `'. $field .'` = `'. $field .'` '. $op .' ? ';
        $param = $this->whereBuilder();
        $sql .= $param['sql'] .' ';
        $param['data'] = array_merge([$num], $param['data']);
        if($this->limit) $sql .= 'LIMIT '. $this->limit;
        $sql .= ';';
        $obj = $this->pdo->prepare($sql);
        if(!$obj) return Db::error();
        $res = $obj->execute($param['data']);
        Db::incExecNum();
        if($res){
            Db::incSuccessNum();
            $num = intval($obj->rowCount());
            if($num > 0) Db::incValidNum();
            return $num;
        }else{
            return false;
        }
    }

/*
执行删除
*/
    public function delete()
    {
        $sql = 'DELETE FROM `'. $this->table .'` ';
        $param = $this->whereBuilder();
        $sql .= $param['sql'] .' ';
        if($this->limit) $sql .= 'LIMIT '. $this->limit;
        $sql .= ';';
        $obj = $this->pdo->prepare($sql);
        if(!$obj) return Db::error();
        $res = $obj->execute($param['data']);
        Db::incExecNum();
        if($res){
            Db::incSuccessNum();
            $num = intval($obj->rowCount());
            if($num > 0) Db::incValidNum();
            return $num;
        }else{
            return false;
        }
    }

/*
获取字段记录数
*/
    public function count($field = '*')
    {
        return $this->funcQuerier('COUNT(`'. $field .'`)');
    }

/*
执行字段求和
*/
    public function sum($field)
    {
        return $this->funcQuerier('SUM(`'. $field .'`)');
    }

/*
Mysql函数执行器
*/
    protected function funcQuerier($field)
    {
        $param = $this->whereBuilder();
        $sql = 'SELECT '. $field .' FROM `'. $this->table .'` '. $param['sql'] .' ';
        $sql .= 'ORDER BY '. $this->order . ' ';
        if($this->limit) $sql .= 'LIMIT '. $this->limit .';';
        $obj = $this->pdo->prepare($sql);
        if(!$obj) return Db::error();
        $res = $obj->execute($param['data']);
        $value = $obj->fetchAll();
        if($res){
            return intval($value[0][$field]);
        }else{
            return false;
        }
    }
}

config.php

<?php
return [
    // 主机地址
    'location' => '127.0.0.1',
    // 数据库名
    'database' => 'hotel',
    // 用户名
    'username' => 'root',
    // 数据库密码
    'password' => 'root',
    // 数据库连接端口
    'hostport' => '3306',
    // 数据库编码默认采用utf8
    'charset'  => 'utf8mb4',
    // 数据库表前缀
    'prefix'   => 'test_',
];