php的pdo数据层mysql类

短命女 2022-07-19 05:25 204阅读 0赞

闲着在家没事儿，写着玩。。。

<?php
    header("Content-type:text/html;charset=utf-8");
    /**
     * Created by PhpStorm.
     * User: Administrator
     * Date: 2016/8/9
     * Time: 11:48
     */
    abstract class DB{
        protected abstract function connect();
    
        public abstract function query($sql);
    
        public abstract function insert($arr);
    
        public abstract function getALL();
    
        public abstract function delete($key,$id);
    
        public abstract function update($arr);
    }
    
    class pdoMysql extends DB{
        private $host;
        private $username;
        private $password;
        private $dbname;
        private $table;
        public $pdh;
    
        private $where_sql = null;
        private $table_fields = null;
    
        private static $mysql = null;
    
        final protected function __construct($host,$username,$password,$dbname,$table){
            $this->host = $host;
            $this->username = $username;
            $this->password = $password;
            $this->dbname = $dbname;
            $this->table = $table;
    
            $this->connect();
        }
    
        final public static function getInstance($host,$username,$password,$dbname,$taable){
            if(self::$mysql instanceof self)
                return self::$mysql;
            else{
                self::$mysql = new self($host,$username,$password,$dbname,$taable);
                return self::$mysql;
            }
        }
    
        final protected function connect(){
            $this->pdh = new PDO('mysql:host='. $this->host .';dbname=' .$this->dbname , $this->username, $this->password);
            if($this->pdh === null)
                die('connect error');
    
            $stmt = $this->pdh->prepare('DESC '.$this->table);
            $stmt->execute();
            $this->table_fields = $stmt->fetchAll(PDO::FETCH_COLUMN);
            if($this->table_fields === null)
                die('this table ' . $this->table . ' do not exists');
    
            $this->query("set names utf8");
        }
    
        public function query($sql){
            $res = $this->pdh->query($sql);
            return $res;
        }
    
        protected function check_sql_arr($arr){
            $key_arr = array_keys($arr);
            foreach($key_arr as $v){
                if(!in_array($v,$this->table_fields))
                    die('sql fields error');
            }
            return $arr;
        }
    
        public function insert($arr){
            $arr = $this->check_sql_arr($arr);
    
            $sql = 'insert into ' . $this->table . ' (' . implode(',',array_keys($arr)) . ')';
            $sql .= ' values (\'';
            $sql .= implode("','",array_values($arr));
            $sql .= '\')';
    
            $this->query($sql);
        }
    
        public function getALL(){
            $res = $this->pdh->query("SELECT * FROM ". $this->table , PDO::FETCH_ASSOC);
            return $res->fetchAll();
        }
    
        public function delete($key,$id){
            $this->where($key, $id);
    
            $sql = 'delete from ' . $this->table . ' where ' . $this->where_sql;
    
            $this->query($sql);
            $this->where_sql = null;
        }
    
        public function update($arr){
            $arr = $this->check_sql_arr($arr);
            if(!empty($this->where_sql))
            {
                $sql = 'update ' . $this->table .' set ';
                foreach($arr as $k=>$v) {
                    $sql .= $k . "='" . $v ."',";
                }
                $sql = rtrim($sql,',');
                $sql .= ' where ' .$this->where_sql;
                $this->query($sql);
            }
            $this->where_sql = null;
        }
    
        public function where(){
            $func_args_arr = func_get_args();
    
            $key = array_intersect($func_args_arr,$this->table_fields);
            unset($func_args_arr[array_search($key,$func_args_arr)]);
            $key = array_pop($key);
    
            $sign = array_intersect($func_args_arr,array('>','<','='));
            unset($func_args_arr[array_search($sign,$func_args_arr)]);
            $sign = array_pop($sign);
    
            $num = array_pop($func_args_arr);
            if($key && $num)
            {
                $this->where_sql .= $this->where_sql == null ? sprintf("(%s %s %d)",$key,$sign == null ? '=' : $sign,$num) : sprintf(" and (%s %s %d)",$key,$sign == null ? '=' : $sign,$num);
            }else{
                die('arguments error in pdoMysql::where()');
            }
        }
    
        public function whereor(){
            $func_args_arr = func_get_args();
    
            $key = array_intersect($func_args_arr,$this->table_fields);
            unset($func_args_arr[array_search($key,$func_args_arr)]);
            $key = array_pop($key);
    
            $sign = array_intersect($func_args_arr,array('>','<','=','like'));
            unset($func_args_arr[array_search($sign,$func_args_arr)]);
            $sign = array_pop($sign);
    
            $num = array_pop($func_args_arr);
    
            if($key && $num)
            {
                if(is_int($num))
                    $this->where_sql .= $this->where_sql == null ? sprintf("(%s %s %d)",$key,$sign == null ? '=' : $sign,$num) : sprintf(" or (%s %s %d)",$key,$sign == null ? '=' : $sign,$num);
                else
                    $this->where_sql .= $this->where_sql == null ? sprintf("(%s %s '%s')",$key,$sign == null ? '=' : $sign,$num) : sprintf(" or (%s %s '%s')",$key,$sign == null ? '=' : $sign,$num);
            }else{
                die('arguments error in pdoMysql::where()');
            }
        }
    
        public function find(){
            if(!empty($this->where_sql))
            {
                $res = $this->pdh->query("SELECT * FROM ". $this->table . ' where ' . $this->where_sql, PDO::FETCH_ASSOC);
                print_r($res->fetchAll());
            }
            $this->where_sql = null;
        }
    }
    $pdoMysql = pdoMysql::getInstance(YOURHOST,USERNAME,PASSWORD,DBNAME,TABLENAME);
    /*where查询
    $pdoMysql->where('goods_id','>',8);
    $pdoMysql->where('goods_id','<',10);
    $pdoMysql->whereor('goods_name','like','两%');
    $pdoMysql->find();
    */
    
    /*update修改
    $pdoMysql->where('goods_id','=',100);
    $pdoMysql->update(array(
        'goods_id' => 10
        )
    );
    */
    
    /*insert插入
    $pdoMysql->insert(array(
            'goods_id' => 100,
            'goods_name' => 'insert_goods_name'
        )
    );
    */
    
    /*delete删除
    $pdoMysql->delete('goods_id',100);
    */