手敲PHP抽像类及接口案例、用类实现数据CURD－第九期（191129作业）

手敲PHP抽像类及接口案例：

<?php
interface iCurd{
    public function create($data);
    public function read();
    public function update($data,$where);
    public function delete($where);
}
class Db implements iCurd{
    protected $pdo=null;
    protected $table;
    public function __construct($dsn,$user,$password,$table='staff')
    {
        $this->pdo=new PDO($dsn,$user,$password);
        $this->table=$table;
    }
    public function read($fields='*',$where='',$limit='0,5')
    {
        // TODO: Implement read() method.
        $where=empty($where)?'':' WHERE '.$where;
        $limit=' LIMIT '.$limit;
        $sql='SELETE '.$fields.' FROM '.$this->table.$where.$limit;
        $stsm=$this->pdo->prepare($sql);
        $stsm->execute();
        return $stsm->fetchAll(PDO::FETCH_ASSOC);
    }
    public function create($data)
    {
        // TODO: Implement create() method.
        $fields=' (name,age,sex,position,mobile,hiredate)';
        $values='(:name,:age,:sex,:position,:mobile,:hiredate)';
        $sql='INSERT INTO '.$this->table.$fields.' VALUES '.$values;
        $stsm=$this->pdo->prepare($sql);
        $stsm->execute($data);
        return [
            'count'=>$stsm->rowCount(),
            'id'=>$this->pdo->lastInsertId()
        ];
    }
    public function update($data, $where)
    {
        // TODO: Implement update() method.
        $keyArr=array_keys($data);
        $set='';
        foreach ($keyArr as $value){
            $set.=$value.' =:'.$value.', ';
        }
        $set=trim(rtrim($set,','));
        $sql='UPDATE '.$this->table.' SET '.$set.' WHERE '.$where;
        $stsm=$this->pdo->prepare($set);
        $stsm->execute($data);
        return $stsm->rowCount();
    }
    public function delete($where)
    {
        // TODO: Implement delete() method.
        $sql='DELETE FROM '.$this->table.' WHERE '.$where;
        $stsm=$this->pdo->prepare($sql);
        $stsm->execute();
        return $stsm->rowCount();
    }
}
$dsn='mysql:host=localhost;dbname=ouk';
$user='root';
$password='123456';
$db=new Db($dsn,$user,$password);
foreach ($db->read() as $item){
    print_r($item);
    echo '<br>';
}
echo '<hr>';
$data=[
    'name'=>'郭靖',
    'age'=>30,
    'sex'=>1,
    'position'=>'金刀附马',
    'mobile'=>1388888888,
    'hiretime'=>time()
];
$res=$db->create($data);
echo '成功新增'.$res['count'].'条记录，最新记录的主键是：'.$res['id'];
echo '<hr>';
$data=[
    'age'=>5,
    'position'=>'抗金英雄'
];
$where='id=5';
echo '成功更新了:'.$db->update($data,$where).'条记录';
echo '<hr>';
$where='id=5';
echo '成功删除了'.$db->delete($where).'条记录';

二、用类实现数据库CURD

<?php
class Db{
    public $dsn;
    public $user;
    public $password;
    public $pdo=null;
    public function __construct($dsn,$user,$password)
    {
        $this->dsn=$dsn;
        $this->user=$user;
        $this->password=$password;
        $this->connect();
    }
    public function connect(){
        $this->pdo=new PDO($this->dsn,$this->user,$this->password);
    }
    public function select($table,$fields='*',$where='',$order='',$limit=''){
        $sql='SELECT ';
        if (is_array($fields)){
            foreach ($fields as $field){
                $sql=$sql.$field.',';
            }
            $sql=rtrim(trim($sql),',');
        }else{
            $sql.=$fields;
        }
        $sql.=' FROM '.$table;
//        die($sql);
        if (!is_null($where)){
            $sql.=' WHERE '.$where;
        }
//        die($sql);
        if(!is_null($order)){
            $sql.=' order by '.$order;
        }
        if(!is_null($limit)){
            $sql.=' limit '.$limit;
        }
//        var_dump($this->pdo);die;
        $stsm=$this->pdo->prepare($sql);
        if($stsm->execute()){
            if($stsm->rowCount()>0){
                $stsm->setFetchMode(PDO::FETCH_ASSOC);
                return $stsm->fetchAll();
            }
        }
        return false;
    }
    public function insert($table,$data=[]){
        $sql='insert into '.$table.' set ';
        if (is_array($data)){
            foreach ($data as $field => $value){
                $sql.=($field.'=\''.$value.'\',');
            }
            $sql=rtrim(trim($sql),',');
            $stsm=$this->pdo->prepare($sql);
            if($stsm->execute()){
                if($stsm->rowCount()>0){
                    return '插入的ID号为'.$this->pdo->lastInsertID();
                }else{
                    return false;
                }
            }else{
                return false;
            }
        }
        return false;
    }
    public function update($table,$where=[],$set=[]){
        $sql="update {$table} set ";
        if(is_array($set)){
            foreach ($set as $field => $value){
                $sql.=($field.'=\''.$value.'\',');
            }
            $sql=rtrim(trim($sql),',');
//            die($sql);
        }else{
            return false;
        }
        if (is_array($where)){
            $sql.=' where ';
            foreach ($where as $field => $value){
                $sql.=($field.' = \''.$value.'\'' . ' and ');
            }
            $sql=rtrim(trim($sql),' and ');
            $stsm=$this->pdo->prepare($sql);
            if ($stsm->execute()){
                if ($stsm->rowcount()>0){
                    return '修改记录数为：'.$stsm->rowcount();
                }else{
                    return false;
                }
            }else{
                return false;
            }
        }else{
            return false;
        }
    }
    public function delete($table,$where=[]){
        $sql="delete from {$table} where ";
        if (is_array($where)){
            foreach ($where as $field => $value){
                $sql.=($field.' = \''.$value.'\'' . ' and ');
            }
            $sql=rtrim(trim($sql),' and ');
//            die($sql);
            $stsm=$this->pdo->prepare($sql);
            if ($stsm->execute()){
                if ($stsm->rowcount()>0){
                    return '删除记录数为：'.$stsm->rowcount();
                }else{
                    return false;
                }
            }else{
                return false;
            }
        }else{
            return false;
        }
    }
    public function count_num($table){
        $sql='select count(*) as total from '.$table;
        $stsm=$this->pdo->prepare($sql);
        if ($stsm->execute()){
            if ($stsm->rowCount()>0){
                $total=$stsm->fetchColumn();
                return "表{$table}总共有记录数{$total}条";
            }else{
                return false;
            }
        }else{
            return false;
        }
    }
}
$dsn='mysql:host=localhost;dbname=emshop';
$obj=new Db($dsn,'root',123456);
//print_r($obj->select('movies',['mov_id','name','cate_id'],'mov_id>3','mov_id desc','4'));
//echo $obj->insert('category',['name'=>'aa','alias'=>'bb']);
//echo $obj->update('category',['cate_id'=>'9','name'=>'aa'],['name'=>'cc','alias'=>'dd']);
//echo $obj->delete('category',['cate_id'=>'9','name'=>'aa']);
echo $obj->count_num('movies');