1、实现效果，链式sql语句封装

order、where、group、limit等封装

 

 2、数据表构造函数入参，ModelBase.php

    public $table = NULL;  public function __construct($table){$this->table=$table;if(!$this->table){die("no table" );}$this->_connect();$this->_opt();}

3、ModelBase.php定义order、where、group、limit等参数以及默认值

  public $opt;private function _opt(){$this->opt=array('filed'=>'*','where'=>'','group'=>'','having'=>'','order'=>'','limit'=>'');}public function where($where){$this->opt['where']=  ' WHERE '.$where;return $this;}public function order($order){$this->opt['order']=  ' ORDER BY '.$order;return $this;}public function group($group){$this->opt['group']=  ' GROUP BY '.$group;return $this;}public function having($having){$this->opt['having']=  ' having '.$having;return $this;}public function filed($filed){$this->opt['filed']= $filed;return $this;}public function limit($limit){$this->opt['limit']=  ' limit '.$limit;return $this;}

4、封装select、find、delete等方法动态参数ModelBase.php

  public function select(){$sql = "SELECT ".$this->opt['filed']. "FROM ".$this->table.$this->opt['where'].$this->opt['group'].$this->opt['having'].$this->opt['order'].$this->opt['limit'];$stmt = $this->pdo->prepare($sql);$stmt->execute();return $stmt->fetchAll(PDO::FETCH_ASSOC);}public function find(){$data=$this->limit(1)->select();return current($data);}public function delete(){$sql = "DELETE FROM ".$this->table.$this->opt['where'];$stmt = $this->pdo->prepare($sql);$stmt->execute();return $stmt->rowCount();}public function insert( $data) {// 准备SQL语句$fields = implode(', ', array_keys($data));$values = ':' . implode(', :', array_keys($data));$sql = "INSERT INTO ". $this->table." (".$fields.") VALUES (".$values.")";// 绑定参数并执行SQL语句$stmt = $this->pdo->prepare($sql);foreach ($data as $key => $value) {$stmt->bindValue(':' . $key, $value);}return $stmt->execute();}public function update( $data){if(empty($this->opt['where'])) die('更新语句必须有were条件');$up='';foreach ($data as $k=>$v){$up.="`".$k."`='".$v."'";}$sql="update  ".$this->table.' set '.$up.$this->opt['where'];$stmt = $this->pdo->prepare($sql);$stmt->execute();return $stmt->rowCount();}

5、indexCrl.php调用链式sql

<?php
class indexCrl{public function index(){$model= new ModelBase("test");//链式sql查询$data=$model->where('id>0')->order('id desc')->group('name')->having("AVG(age)>10")->limit(1)->select();var_dump($data);//链式sql更新$up=["name"=>"小李"];$model->where('id=2')->update($up);//链式sql删除$model->where('id=2')->delete();//添加$insert=["name"=>"张三","age"=>13,];$model->insert($insert);}
}

6、完整的ModelBase.php文件

<?php
class ModelBase
{public $pdo = NULL;public $table = NULL;public $opt;public function __construct($table){$this->table=$table;if(!$this->table){die("no table" );}$this->_connect();$this->_opt();}private function _connect(){if($this->pdo){return true;}$host = DB_HOST;$db = DB_DATABASE;$user = DB_USER;$pass = DB_PWD;$dsn = "mysql:host=$host;dbname=$db;charset=utf8";try {$this->pdo = new PDO($dsn, $user, $pass, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);} catch (PDOException $e) {die("数据库连接失败: " . $e->getMessage());}}private function _opt(){$this->opt=array('filed'=>'*','where'=>'','group'=>'','having'=>'','order'=>'','limit'=>'');}public function where($where){$this->opt['where']=  ' WHERE '.$where;return $this;}public function order($order){$this->opt['order']=  ' ORDER BY '.$order;return $this;}public function group($group){$this->opt['group']=  ' GROUP BY '.$group;return $this;}public function having($having){$this->opt['having']=  ' having '.$having;return $this;}public function filed($filed){$this->opt['filed']= $filed;return $this;}public function limit($limit){$this->opt['limit']=  ' limit '.$limit;return $this;}public function select(){$sql = "SELECT ".$this->opt['filed']. "FROM ".$this->table.$this->opt['where'].$this->opt['group'].$this->opt['having'].$this->opt['order'].$this->opt['limit'];$stmt = $this->pdo->prepare($sql);$stmt->execute();return $stmt->fetchAll(PDO::FETCH_ASSOC);}public function find(){$data=$this->limit(1)->select();return current($data);}public function delete(){$sql = "DELETE FROM ".$this->table.$this->opt['where'];$stmt = $this->pdo->prepare($sql);$stmt->execute();return $stmt->rowCount();}public function insert( $data) {// 准备SQL语句$fields = implode(', ', array_keys($data));$values = ':' . implode(', :', array_keys($data));$sql = "INSERT INTO ". $this->table." (".$fields.") VALUES (".$values.")";// 绑定参数并执行SQL语句$stmt = $this->pdo->prepare($sql);foreach ($data as $key => $value) {$stmt->bindValue(':' . $key, $value);}return $stmt->execute();}public function update( $data){if(empty($this->opt['where'])) die('更新语句必须有were条件');$up='';foreach ($data as $k=>$v){$up.="`".$k."`='".$v."'";}$sql="update  ".$this->table.' set '.$up.$this->opt['where'];$stmt = $this->pdo->prepare($sql);$stmt->execute();return $stmt->rowCount();}}