php db

db.php

<?php
/**
 *============================
 * author:Farmer
 * time:2017/11/20
 * blog:blog.icodef.com
 * function:数据库查询
 *============================
 */

namespace lib;

use PDO;

class db {

    /**
     * 数据库连接对象
     * @var PDO
     */
    private static $db = null;

    /**
     * 构造函数
     * db constructor.
     * @param string $table
     */
    public function __construct($table = '') {
        $this->table = static::$prefix . str_replace('|', ',' . static::$prefix, $table);
    }

    /**
     * 表前缀
     * @var string
     */
    protected static $prefix = '';

    /**
     * 连接参数
     * @var array
     */
    protected static $connectParam = [];

    /**
     * 初始化
     * @param string $host
     * @param string $user
     * @param string $pwd
     * @param string $db
     * @param string $prefix
     */
    public static function init($host = '', $user = '', $pwd = '', $db = '', $prefix = '') {
        static::$connectParam = func_get_args();
        call_user_func_array([db::class, 'reconnect'], static::$connectParam);
    }

    public static function table($table = '') {
        return new db($table);
    }

    /**
     * 重连数据库
     * @param string $host
     * @param string $user
     * @param string $pwd
     * @param string $db
     * @param string $prefix
     */
    public static function reconnect($host = '', $user = '', $pwd = '', $db = '', $prefix = '') {
        $dns = 'mysql:dbname=' . $db . ';host=';
        $dns .= $host . ';charset=utf8';
        static::$db = new PDO($dns, $user, $pwd);
        static::$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        static::$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
        static::$prefix = $prefix;
    }

    private $table = '';
    private $where = '';
    private $field = '';
    private $order = '';
    private $limit = '';
    private $join = '';

    private $lastOper = 'and';
    private $bindParam = [];

    /**
     * 条件
     * @author Farmer
     * @param mixed $field
     * @param null $value
     * @param string $operator
     * @return $this
     */
    public function where($field, $value = null, $operator = '=') {
        $this->where .= ' ' . ((empty($this->where) || $this->where == '(') ? '' : $this->lastOper . ' ');
        //恢复默认运算符
        $this->lastOper = 'and';
        if (is_array($field)) {
            //获取最后一个
            $keys = array_keys($field);
            foreach ($field as $key => $item) {
                if (is_string($key)) {
                    $this->where .= "`$key`$operator? ";
                    $this->bindParam[] = $item;
                    if ($key !== end($keys)) {
                        $this->where .= 'and ';
                    }
                } else if (is_numeric($key)) {
                    $this->where .= "$item";
                    if ($key !== end($keys)) {
                        $this->where .= 'and ';
                    }
                }
            }
        } else if (is_string($field)) {
            if (is_null($value)) {
                $this->where .= $field;
            } else {
                $this->where .= " `$field`$operator?";
                $this->bindParam[] = $value;
            }
        }
        return $this;
    }

    public function _lb() {
        if (empty($this->where)) {
            $this->where .= '(';
        } else {
            $this->lastOper .= '(';
        }
        return $this;
    }

    public function _rb() {
        $this->where .= ')';
        return $this;
    }

    public function join($table, $on = '', $link = 'left') {
        if (is_array($table)) {
            foreach ($table as $key => $value) {
                if (is_string($key)) {
                    $this->join .= " $link join `" . input('config.db.prefix') . $key . "` as $value " . (empty($on) ? '' : "on $on");
                } else if (is_numeric($key)) {
                    $this->join .= ' ' . $value;
                }
            }
        } else if (is_string($table)) {
            $table = str_replace(':', input('config.db.prefix'), $table);
            $this->join .= " $link join $table " . (empty($on) ? '' : "on $on");
        }
        return $this;
    }

    /**
     * 预处理执行
     * @param $sql
     * @param $param
     * @return bool|\PDOStatement
     */
    public function execute($sql, $param) {
        try {
            $result = static::$db->prepare($sql);
            if (@$result->execute($param)) {
                return $result;
            } else {
                $error = $result->errorInfo();
                if ($error[1]) {
                    throw new \ErrorException($error[2], $error[1]);
                }
            }
        } catch (\Throwable $exception) {
            if (strpos($exception->getMessage(), 'failed with errno=10054')) {
                call_user_func_array([db::class, 'reconnect'], static::$connectParam);
                return $this->execute($sql, $param);
            }
        }
        return false;
    }

    /**
     * 插入数据
     * @author Farmer
     * @param array $items
     * @return bool|int
     */
    public function insert(array $items) {
        if (!empty ($items)) {
            $param = [];
            $sql = 'insert into ' . $this->table . '(`' . implode('`,`', array_keys($items)) . '`) values(';
            foreach ($items as $value) {
                $sql .= '?,';
                $param[] = $value;
            }
            $sql = substr($sql, 0, strlen($sql) - 1);
            $sql .= ')';
            if ($result = $this->execute($sql, $param)) {
                return $result->rowCount();
            }
            return false;
        }
        return false;
    }

    /**
     * and
     * @author Farmer
     * @return $this
     */
    public function _and() {
        $this->lastOper = 'and';
        return $this;
    }

    /**
     * or
     * @author Farmer
     * @return $this
     */
    public function _or() {
        $this->lastOper = 'or';
        return $this;
    }

    /**
     * 查询记录
     * @author Farmer
     * @return bool|record
     */
    public function select() {
        $sql = 'select ' . ($this->field ?: '*') . " from {$this->table} {$this->join} " . ($this->where ? 'where' : '');
        $sql .= $this->dealParam();
        if ($result = $this->execute($sql, $this->bindParam)) {
            return new record($result);
        }
        return false;
    }

    public function count() {
        $tmpField = $this->field;
        $tmpLimit = $this->limit;
        $this->field = '';
        $count = $this->field('count(*)')->find()['count(*)'];
        $this->field = $tmpField;
        $this->limit = $tmpLimit;
        return $count;
    }

    /**
     * 数据更新
     * @author Farmer
     * @param $set
     * @return bool|int
     */
    public function update($set) {
        $data = null;
        if (is_string($set)) {
            $data = $set;
        } else if (is_array($set)) {
            foreach ($set as $key => $value) {
                if (is_numeric($key)) {
                    $data .= ',' . $set[$key];
                } else {
                    $data .= ",`{$key}`=?";
                    $tmpParam[] = $value;
                }
            }
            $this->bindParam = array_merge($tmpParam, $this->bindParam);
            $data = substr($data, 1);
        }
        $sql = "update {$this->table} set $data where" . $this->dealParam();
        if ($result = $this->execute($sql, $this->bindParam)) {
            return $result->rowCount();
        }
        return false;
    }

    /**
     * 删除数据
     * @author Farmer
     * @return bool|int
     */
    public function delete() {
        $sql = "delete from {$this->table} where" . $this->dealParam();
        if ($result = $this->execute($sql, $this->bindParam)) {
            return $result->rowCount();
        }
        return false;
    }

    /**
     * 对where等进行处理
     * @author Farmer
     * @return string
     */
    private function dealParam() {
        $sql = $this->where ?: '';
        $sql .= $this->order ?: '';
        $sql .= $this->limit ?: '';
        return $sql;
    }

    /**
     * 绑定参数
     * @author Farmer
     * @param $key
     * @param string $value
     * @return $this
     */
    public function bind($key, $value = '') {
        if (is_array($key)) {
            $this->bindParam = array_merge($this->bindParam, $key);
        } else {
            $this->bindParam[$key] = $value;
        }
        return $this;
    }

    /**
     * 排序
     * @author Farmer
     * @param $field
     * @param string $rule
     * @return $this
     */
    public function order($field, $rule = 'desc') {
        if ($this->order) {
            $this->order .= ",`$field` $rule";
        } else {
            $this->order = " order by `$field` $rule";
        }
        return $this;
    }

    /**
     * 分页
     * @author Farmer
     * @param $start
     * @param int $count
     * @return $this
     */
    public function limit($start, $count = 0) {
        if ($count) {
            $this->limit = " limit $start,$count";
        } else {
            $this->limit = " limit $start";
        }
        return $this;
    }

    /**
     * 查询出单条数据
     * @author Farmer
     * @return mixed
     */
    public function find() {
        return $this->limit('1')->select()->fetch();
    }

    /**
     * 开始事务
     * @author Farmer
     */
    public function begin() {
        self::$db->exec('begin');
    }

    /**
     * 提交事务
     * @author Farmer
     */
    public function commit() {
        self::$db->exec('commit');
    }

    /**
     * 回滚事务
     * @author Farmer
     */
    public function rollback() {
        self::$db->exec('rollback');
    }

    public function field($field, $alias = '') {
        if (is_string($field)) {
            if (empty($alias)) {
                $this->field .= (empty($this->field) ? '' : ',') . $field . ' ';
            } else {
                $this->field .= (empty($this->field) ? '' : ',') . $field . ' as ' . $alias . ' ';
            }
        } else if (is_array($field)) {
            foreach ($field as $key => $value) {
                if (is_string($key)) {
                    $this->field .= (empty($this->field) ? '' : ',') . $key . ' as ' . $value . ' ';
                } else {
                    $this->field .= (empty($this->field) ? '' : ',') . $value . ' ';
                }
            }
        }
        return $this;
    }

    /**
     * 上一个插入id
     * @author Farmer
     * @return int
     */
    public function lastinsertid() {
        return self::$db->lastInsertId();
    }

    public function __call($func, $arguments) {
        if (is_null(self::$db)) {
            return 0;
        }
        return call_user_func_array(array(
            self::$db,
            $func
        ), $arguments);
    }
}

/**
 * 记录集类
 * @author Farmer
 * @package icf\lib
 */
class record {
    private $result;

    public function __call($func, $arguments) {
        if (is_null($this->result)) {
            return 0;
        }
        return call_user_func_array(array(
            $this->result,
            $func
        ), $arguments);
    }

    function __construct(\PDOStatement $result) {
        $this->result = $result;
        $this->result->setFetchMode(PDO::FETCH_ASSOC);
    }

    function fetchAll() {
        return $this->result->fetchAll();
    }

    function fetch() {
        return $this->result->fetch();
    }

}