1127_类封装 第23课

1. 把 php公用方法库中的 数据库操作函数， 重写到 Db类中。

class Db{
    private $dsn;
    private $user;
    private $pwd;
    private $pdo;
    public function __construct($host, $dbname, $charset, $user, $pwd){
        $this->dsn = "mysql:host={$host};dbname={$dbname};charset={$charset}";
        $this->user = $user;
        $this->pwd = $pwd;
        $this->connect();
    }
    private function connect(){
        try {
            # 实例化PDO类,创建PDO对象
            $this->pdo = new PDO( $this->dsn, $this->user, $this->pwd );
        } catch (PDOException $e) {
            die('数据库错误:'.$e->getMessage());
        }
    }
    /**
     * 查询
     * @param $table
     * @param $fields 格式'id' 或 【'name','id']
     * @param $where  格式['id'=>['>',5],'name'=>'Jason']
     * @return array
     */
    public function select($table, $fields, $where=[], $order='', $limit=''){
        # 创建SQL语句
        $exe = [];
        $sql = 'SELECT ';
        if( is_array($fields) ){
            foreach( $fields as $field ){
                $sql .= $field.', ';
            }
        }
        else{
            $sql .= $fields;
        }
        $sql = rtrim( trim($sql),',' );
        $sql .= '  FROM '.$table;
        # 查询条件
        if( !empty($where) ){
            $sql .= ' WHERE ';
            foreach( $where as $k => $v ){
                if( is_array($v) ){
                    $sql .= '`' .$k. '`' .$v[0]. ':' .$k. ' AND ';
                    $exe[ $k ] = $v[1];
                }
                else{
                    $sql .= '`' .$k. '`=:' .$k. ' AND ';
                    $exe[ $k ] = $v;
                }
            }
            $sql = rtrim( trim($sql),'AND');
        }
        # 排序条件
        if( !empty($order) ){
            $sql .= ' order by '.$order;
        }
        # 分页条件
        if( !empty($limit) ){
            $sql .= ' limit '.$limit;
        }
        $sql .= ';';
        return $this->prepare_exe( 'select', $sql, $exe );
    }
    /**
     * 查询单条记录
     * @param $table
     * @param $fields
     * @param $where  格式['id'=>['>',5],'name'=>'Jason']
     * @return array
     */
    function find( $table, $fields, $where=[] ){
        # 创建SQL语句
        $exe = [];
        $sql = 'SELECT ';
        if( is_array($fields) ){
            foreach( $fields as $field ){
                $sql .= $field.', ';
            }
        }
        else{
            $sql .= $fields;
        }
        $sql = rtrim(trim($sql),',');
        $sql .= ' FROM '.$table;
        # 查询条件
        if( !empty($where) ){
            $sql .= ' WHERE ';
            foreach( $where as $k => $v ){
                if( is_array($v) ){
                    $sql .= '`' .$k. '`' .$v[0]. ':' .$k. ' AND ';
                    $exe[ $k ] = $v[1];
                }
                else{
                    $sql .= '`' .$k. '`=:' .$k. ' AND ';
                    $exe[ $k ] = $v;
                }
            }
            $sql = rtrim( trim($sql),'AND');
        }
        $sql .= ';';
        return $this->prepare_exe( 'find', $sql, $exe );
    }
    /**
     * 新增
     * @param $table
     * @param $data 格式['user_name'=>'xx','desc'=>'xx','hobby'=>'xx']
     * @return bool
     */
    public function insert( $table, $data=[] ){
        # 创建SQL语句
        $exe = [];
        $sql = 'INSERT INTO '.$table.' SET ';
        # 组装插入语句
        if( !empty($data) ){
            foreach( $data as $k=>$v ){
                $sql .= '`' .$k. '`=:' .$k. ', ';
                $exe[ $k ] = $v;
            }
            $sql .= '`create_time`=:ct;';
            $exe[ 'ct' ] = time();
        }
        else{
            return '新增数据不能为空';
        }
        return $this->prepare_exe( 'insert', $sql, $exe );
    }
    /**
     * 更新
     * @param $table
     * @param $data 格式['email'=>'xx']
     * @return bool
     */
    public function update($table,$data=[], $where=[]) {
        # 创建SQL语句
        $exe = [];
        $sql = 'UPDATE '.$table.' SET ';
        # 组装修改语句
        if( !empty($data) ){
            foreach( $data as $key=>$val ){
                $sql .= '`' .$key.'`=:'.$key.', ';
                $exe[ $key ] = $val;
            }
            $sql .= '`update_time`=:ut';
            $exe[ 'ut' ] = time();
        }
        else{
            return '更新数据不能为空';
        }
        # 查询条件
        if( !empty($where) ){
            $sql .= ' WHERE ';
            foreach( $where as $k => $v ){
                if( is_array($v) ){
                    $sql .= '`' .$k. '`' .$v[0]. ':' .$k. ' AND ';
                    $exe[ $k ] = $v[1];
                }
                else{
                    $sql .= '`' .$k. '`=:' .$k. ' AND ';
                    $exe[ $k ] = $v;
                }
            }
            $sql = rtrim( trim($sql),'AND');
        }
        $sql .= ';';
        return $this->prepare_exe( 'update', $sql, $exe );
    }
    /**
     * 删除
     * @param $table
     * @param $where 格式['id'=>['>',5],'name'=>'Jason']
     * @return bool
     */
    public function delete($table,$where=[]){
        # 创建SQL语句
        $exe = [];
        $sql = "DELETE FROM {$table}  ";
        # 查询条件
        if( !empty($where) ){
            $sql .= ' WHERE ';
            foreach( $where as $k => $v ){
                if( is_array($v) ){
                    $sql .= '`' .$k. '`' .$v[0]. ':' .$k. ' AND ';
                    $exe[ $k ] = $v[1];
                }
                else{
                    $sql .= '`' .$k. '`=:' .$k. ' AND ';
                    $exe[ $k ] = $v;
                }
            }
            $sql = rtrim(trim($sql), 'AND');
        }
        $sql .= ';';
        return $this->prepare_exe( 'delete', $sql, $exe );
    }
    /**
     * 统计数量
     * @param $table
     * @param $where 格式['id'=>['>',5],'name'=>'Jason']
     * @return number
     */
    public function count_num($table, $where=[]){
        # 创建SQL语句
        $exe = [];
        $sql  = 'SELECT count(*) as count_number FROM '.$table;
        # 查询条件
        if( !empty($where) ){
            $sql .= ' WHERE ';
            foreach( $where as $k => $v ){
                if( is_array($v) ){
                    $sql .= '`' .$k. '`' .$v[0]. ':' .$k. ' AND ';
                    $exe[ $k ] = $v[1];
                }
                else{
                    $sql .= '`' .$k. '`=:' .$k. ' AND ';
                    $exe[ $k ] = $v;
                }
            }
            $sql = rtrim(trim($sql), 'AND');
        }
        $sql .= ';';
        return $this->prepare_exe( 'count', $sql, $exe );
    }
    /**
     * @param $type sql类型
     * @param $sql sql模版语句
     * @param $exe sql执行条件
     * @return bool
     */
    private function prepare_exe( $type, $sql, $exe ){
        # 创建PDO预处理对象
        $stmt = $this->pdo->prepare($sql);
        # 执行查询操作
        if( $stmt->execute( $exe ) ){
            if( $stmt->rowCount()>0 ){
                switch( $type ){
                    case $type=='select' || $type=='find':
                        $stmt->setFetchMode(PDO::FETCH_ASSOC);
                        if( $type =='select' ) return $stmt->fetchAll(); # 返回一个二维数组
                        else return $stmt->fetch();
                    case $type=='insert' || $type=='update' || $type=='delete':
                        return true;
                    case 'count':
                        $row  = $stmt->fetch(PDO::FETCH_ASSOC);
                        $n = $row['count_number'];
                        return $n;
                    default:
                        break;
                }
            }
        }
        else{
            return false;
        }
    }
}
# 实例化
$db = new Db('127.0.0.1','SqlTest', 'utf8','root', 'root');
$select = $db->select( 'zsgc',['id','user_name'],['id'=>['>',1],'email'=>['!=','']] ,'id desc');
var_dump( $select );
echo '<hr>';
$find = $db->find( 'zsgc',['id','user_name'],['id'=>['>',1],'email'=>['!=','']] );
var_dump( $find );
echo '<hr>';
//$insert = $db->insert( 'zsgc',['user_name'=>'Bonney1','desc'=>'她很优秀','hobby'=>'滑雪'] );
//print_r( $insert );
//echo '<hr>';
$update = $db->update( 'zsgc',['email'=>'123456@qq.com'],['id'=>27] );
print_r( $update );
echo '<hr>';
$delete = $db->delete( 'zsgc',['id'=>['>',20],'email'=>['!=','']] );
print_r( $delete );
echo '<hr>';
$count = $db->count_num( 'zsgc',['id'=>['>',20]] );
print_r( $count );
echo '<hr>';