php的pdo数据层mysql类

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

<?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);
*/