查询构造器实践

查询构造器

代码

<?php
namespace ns;
use PDO;
class DB 
{
    protected $db;//数据库对象
    protected $table;//表名
    protected $field;//字段名
    protected $limit;//限制
    protected $opt =[];//具体操作
    // 链接数据库
    public function __construct($dsn,$username,$password)
    {
        $this->db= new PDO($dsn,$username,$password);
    }
    // 表名赋值
    public function table($table)
    {
        $this->table = $table;
        return $this;
    }
    // 字段赋值
    public function field($field)
    {
        $this->field = $field;
        return $this;
    }
    //分页操作
    public function page($page =1)
    {
        $this->opt['offset']=' OFFSET '.($page -1 )*$this->limit;
        return $this;
    }
    //限制设置
    public function limit($limit = 10)
    {
        $this->limit = $limit;
        $this->opt['limit'] = "LIMIT $limit";
        return $this;
    }
    //查询条件
    public function where($where = '')
    {
        $this->opt['where']= "WHERE $where";
        return $this;
    }
    // 查询
    public function select()
    {
        $sql = 'SELECT '. $this->field . ' FROM '.$this->table;
        $sql.=$this->opt['where'] ?? null;
        $sql.=$this->opt['limit'] ?? null;
        $sql.=$this->opt['offset'] ?? null;
        $stmt = $this->db->prepare($sql);
        $stmt->execute();
        $this->opt['where'] = null;
        return $stmt->fetchAll();
    }
    // 插入操作
    public function insert($data)
    {
        $str='';
        foreach ($data as $key=>$value)
        {
            $str.=$key.'= "'.$value.'",';
        }
        $sql = 'INSERT '.$this->table . 'SET '.rtrim($str,',');
        $stmt=$this->db->prepare($sql);
        $stmt->execute();
        $this->opt['where'] = null;
        return $stmt->rowCount();
    }
    //更新操作
    public function updata($data)
    {
        $str='';
        foreach($data as $key=>$value){
            $str.=$key.'= "'.$value.'",';
        } 
        $sql= 'UPDATE '.$this->table.'SET'.rtrim($str,',');
        $stmt=$this->db->prepare($sql);
        $stmt->execute();
        $this->opt['where']=null;
        return $stmt->rowCount();
    }
    //删除操作
    public function delete()
    {
        $sql = 'DELETE FROM ' .$this->table;
        $sql.= $this->opt['where']??die('禁止无条件删除');
        $stmt= $this->db->prepare($sql);
        $stmt->execute();
        $this->opt['where']=null;
        return $stmt->rowCount();
    }
}

实例化

1、查询

$db = new DB('mysql:dbname=phpedu','root','root');
$res = $db->table('people')->field('id,name,email')->select();
printf('<pre>%s</pre>',print_r($res,true));

效果：

2、条件查询

$res = $db->table('people')
->field('id,name,email')
->where('id > 20')
->limit(3)
->page(2)
->select();
printf('<pre>%s</pre>',print_r($res,true));

效果：

3、删除

$n = $db->table('people')->where('id=12')->delete();
echo $n > 0 ? '删除成功<br>' : '删除失败或没有数据被删除<br>';

效果：

4、新增操作

$n = $db->table('people')
->insert(['name' => 'Jack', 
'email' => 'jack@php.cn', 
'gender' => 1]);
echo $n > 0 ? '<br>新增成功<br>' : '<br>新增失败或没有数据被添加<br>';

效果：

5、更新操作

$n = $db->table('people')->updata(['name' => 'zhu']);
echo $n > 0 ? '更新成功<br>' : '更新失败或没有数据被更新<br>';

效果：

6、删除操作

$n = $db->table('people')->where('id = 7')->delete();
echo $n > 0 ? '<br>删除成功<br>' : '<br>删除失败或没有数据被删除<br>';

效果：