PHP高级实战-数据库操作类代码

admin
2021-01-21 / 0 评论 / 1,092 阅读 / 正在检测是否收录...
<?PHP
/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2020/2/5
 * Time: 9:25
 */
class Model{
    //主机名
    protected $host;
    //用户名
    protected $user;
    //密码
    protected $pwd;
    //数据库名
    protected $dbname;
    //字符集
    protected $charset;
    //数据表前缀
    protected $prefix;
    //数据库链接资源
    protected $link;
    //数据表名 这里可以自己指定表名
    protected $tableName;
    //sql语句
    protected $sql;
    //操作数组 存放的就是所有的查询条件
    protected $options;
    //构造方法
    public function __construct($config)
    {
        $this->host   =$config['DB_HOST'];
        $this->user   =$config['DB_USER'];
        $this->pwd    =$config['DB_PWD'];
        $this->dbname =$config['DB_NAME'];
        $this->charset=$config['DB_CHARSET'];
        $this->prefix =$config['DB_PREFIX'];
        //连接数据库
        $this->link=$this->connect();
        //得到数据表名
        $this->tableName=$this->getTableName();
        //初始化options数组
        $this->initOptions();
    }
    protected function connect(){
        $link=mysqli_connect($this->host,$this->user,$this->pwd);
        if(!$link){die('数据库连接失败');}
        mysqli_select_db($link,$this->dbname);
        mysqli_set_charset($link,$this->charset);
        return $link;
    }
    protected function getTableName(){
        //第一种,如果设置了成员变量
        if(!empty($this->tableName)){
            return $this->prefix.$this->tableName;
        }
        //第二种,没有设置成员变量,通过类名得到表名
        //得到当前类名字符串
        $className=get_class($this);
        $table=strtolower(substr($className,0,-5));
        return $this->prefix.$table;
    }
    protected function initOptions(){
        $arr=[
            'where',
            'table',
            'field',
            'order',
            'group',
            'having',
            'limit'
        ];
        foreach ($arr as $value){
            //将options数组中这些键对应的值全部清空
            $this->options[$value]='';
            //将table默认设置为tableName
            if($value == 'table'){
                $this->options[$value]=$this->tableName;
            }else if($value == 'field'){
                $this->options['field']='*';
            }
        }
    }
    //field方法
    public function field($field){
      if(!empty($field)){
        if(is_string($field)){
            $this->options['field']=$field;
        }else if(is_array($field)){
            $this->options['field']=join(',',$field);
        }
      }
      return $this;
    }
    //table方法
    public function table($table){
        if(!empty($table)){
            $this->options['table']=$table;
        }
        return $this;
    }
    //where方法
    public function where($where){
        if(!empty($where)){
          $this->options['where']='where '.$where;
        }
        return $this;
    }
    //group方法
    public function group($group){
        if(!empty($group)){
            $this->options['group']='group by '.$group;
        }
        return $this;
    }
    //having方法
    public function having($having){
       if(!empty($having)){
           $this->options['having']='having '.$having;
       }
       return $this;
    }
    //order方法
    public function order($order){
        if(!empty($order)){
            $this->options['order']='order by '.$order;
        }
        return $this;
    }
    //limit方法
    public function limit($limit){
        if(!empty($limit)){
            if(is_string($limit)){
                $this->options['limit']='limit '.$limit;
            }else if(is_array($limit)){
                $this->options['limit']='limit '.join(',',$limit);
            }
        }
        return $this;
    }
    //select方法
    public function select(){
     //先预写一个带有占位符的sql语句
        $sql='select %FIELD% from %TABLE% %WHERE% %GROUP% %HAVING% %ORDER% %LIMIT%';
        //将options中对应的值依次的替换上面的占位符
        $sql=str_replace(
            ['%FIELD%','%TABLE%','%WHERE%','%GROUP%','%HAVING%','%ORDER%','%LIMIT%'],
            [$this->options['field'],$this->options['table'],$this->options['where'],$this->options['group'],$this->options['having'],$this->options['order'],$this->options['limit']],
            $sql);
        //保存一份sql语句
        $this->sql=$sql;
        //执行sql
        return $this->query($sql);
    }
    //query
    public function query($sql){

        //清空options数组中的值
        $this->initOptions();
//        var_dump($sql);
//        die();
        //执行sql语句
      $result=mysqli_query($this->link,$sql);
      //提取结果集存放到数组中
      if($result && mysqli_affected_rows($this->link)){
         while ($data=mysqli_fetch_assoc($result)){
          $newData[]=$data;
         }
      }
      //返回结果集
      return $newData;
    }
    //exec
    public function exec($sql,$isInsert = false){
        //清空options数组中的值
        $this->initOptions();
        //执行sql语句
        $result=mysqli_query($this->link,$sql);
        if($result && mysqli_affected_rows($this->link)){
            //判断是否是插入语句,根据不同的语句返回不同的结果
            if($isInsert){
              return mysqli_insert_id($this->link);
            }else{
              return mysqli_affected_rows($this->link);
            }
        }
        return false;
    }
    //获取sql语句
    public function __get($name)
    {
        // TODO: Implement __get() method.
        if($name == 'sql'){
           return $this->sql;
        }
        return false;
    }
    //insert函数
    //$data:关联数组,键就是字段名,值是字段值
    //insert into table (字段)value(值);
    public function insert($data){
        //处理值是字符串问题,两边需要加单或者双引号
        $data=$this->parseValue($data);
        //提取所有的键,即就是所有的字段
        $keys=array_keys($data);
        //提取所有的值
        $values=array_values($data);
        //增加数据的sql
        $sql='insert into %TABLE%(%FIELD%)VALUES(%VALUES%)';
        $sql=str_replace(
            ['%TABLE%','%FIELD%','%VALUES%'],
            [$this->options['table'],join(',',$keys),join(',',$values)],
            $sql
        );
        $this->sql=$sql;
        return $this->exec($sql,true);
    }
    //传递一个数组,将数组中值为字符串的两边加引号
    protected function parseValue($data){
        foreach ($data as $key => $value){
            if(is_string($value)){
                $value='"'.$value.'"';
            }
            $newData[$key]= $value;
        }
        return $newData;
    }
    //删除函数
    public function delete(){
        //拼接sql语句
        $sql='delete from %TABLE% %WHERE%';
        $sql=str_replace(['%TABLE%','%WHERE%'],
            [$this->options['table'],$this->options['where']],
            $sql);
        //保存sql语句
        $this->sql=$sql;
        //执行sql语句
        return $this->exec($sql);
    }
    //更新函数 update 表名 set 字段名=字段值,字段名=字段值 where
    public function update($data){
        //处理$data数组中值为字符串加引号的问题
        $data=$this->parseValue($data);
        //将关联数组拼接为固定的格式 键=值,键=值
        $value=$this->parseUpdate($data);
        //准备sql语句
        $sql='update %TABLE% set %VALUES% %WHERE%';
        $sql=str_replace(['%TABLE%','%VALUES%','%WHERE%'],
            [$this->options['table'],$value,$this->options['where']],
            $sql);
        //保存sql语句
        $this->sql=$sql;
        //执行sql语句
        return $this->exec($sql);
    }
    protected function parseUpdate($data){
        foreach ($data as $key=>$value){
            $newData[]=$key.'='.$value;
        }
        return join(',',$newData);
    }
    //max函数
    public function max($field){
        $result=$this->field('max('.$field.') as max')
            ->select();
        //select方法返回的是一个二维数组
        return $result[0]['max'];
    }
    //析构方法
    public function __destruct()
    {
        // TODO: Implement __destruct() method.
        mysqli_close($this->link);
    }
    public function __call($name, $arguments)
    {
        // TODO: Implement __call() method.
        //获取前五个字符
        $str=substr($name,0,5);
        $field=strtolower(substr($name,5));
        //判断前五个字符是否是getBy
        if($str == 'getBy'){
            return $this->where($field.'="'.$arguments[0].'"')->select();
        }
        return false;
    }

}

//$config=include 'config.PHP';
//$m= new Model($config);
//$data=['name'=>'chen','age'=>50];
//$arr= $m->table('user')->getByName('范冰冰');
//var_dump($arr);
0

评论 (0)

取消