当前位置:Gxlcms > PHP教程 > 基于PHPMySQLi扩展的数据库操作Model

基于PHPMySQLi扩展的数据库操作Model

时间:2021-07-01 10:21:17 帮助过:22人阅读

'192.168.1.246',            'username'=>'liuchuan',            'password'=>'liuchuan123',            'database'=>'whatcart'        );        $this->link=new mysqli($dbinfo['hostname'],$dbinfo['username'],$dbinfo['password'],$dbinfo['database']);        if($this->link->connect_errno)            trigger_error('Error:Could not make a database link ('.$this->link->connect_errno.')'.$this->link->connect_errno);        $this->link->set_charset("utf8");        $this->fieldString='*';        $this->tableString=$tableName;        $this->leftJoinString='';        $this->whereString='';        $this->orderString='';        $this->limitString='';    }     /********封装的MySQLI扩展方法********/    //参数过滤,输入参数只能是基本类型或一维数组    public function escape($param){        if(is_array($param)){            foreach ($param as $key => $value) {                $param[$key]=$this->link->real_escape_string($value);            }        }else{            $param=$this->link->real_escape_string($value);        }        return $param;    }    //获取插入后生成的ID    public function getLastId(){        return $this->link->insert_id;    }    //获取影响的行数    public function countAffected(){        return $this->link->affected_rows;    }    //执行SQL命令返回TRUE或FALSE    public function execute($sql){        $this->sql=$sql;        $result=$this->link->real_query($this->sql);        if(false===$result){            trigger_error('
ERROR MESSAGE:'.$this->link->error.'
THE SQL:'.$this->sql); return false; }else{ $sql=strtolower($this->sql); return (false===strpos($sql,'insert')?true:$this->link->insert_id;//如果包含insert就返回插入记录的ID } } //执行SQL查询返回关联数组 public function query($sql){ $result=$this->link->real_query($sql); $this->sql=$sql; if($result!==false){ $record=array(); $list=array(); while($record=$result->fetch_assoc()){ $list[]=$record; } return $list; }else{ trigger_error('
ERROR MESSAGE:'.$this->link->error.'
THE SQL:'.$this->sql); return false; } } /********单表增删查改********/ public function create($table,$model){ $fields=''; $values=''; foreach ($model as $key => $value) { if($fields){ $fields.=','; } $fields.="`$key`"; if($values){ $values.=','; } $values.=is_numeric($value)?$value:"'".$this->link->real_escape_string($value)."'"; } $this->sql="INSERT INTO `$table`($fields) VALUES($values)"; if(false===$this->link->real_query($this->sql)) trigger_error('
ERROR MESSAGE:'.$this->link->error.'
THE SQL:'.$this->sql); return $this->link->insert_id; } public function modify($table,$model,$where){ $assins=''; $where=$this->rewhere($where); foreach ($model as $key => $value) { $rkey=$this->link->real_escape_string($key); $rvalue=$this->link->real_escape_string($value); if(!is_numeric($rvalue)){ $rvalue="'".$rvalue."'"; } $assins.=$assins?",`$rkey`=$rvalue":"`$rkey`=$rvalue"; } $result=$this->link->real_query($this->sql); if(false===$result) trigger_error('
ERROR MESSAGE:'.$this->link->error.'
THE SQL:'.$this->sql); } public function remove($table,$where){ $where=$this->rewhere($where); $this->sql="DELETE FROM `$table` WHERE $where"; $result=$this->link->real_query($this->sql); if(false===$result) trigger_error('
ERROR MESSAGE:'.$this->link->error.'
THE SQL:'.$this->sql); return $flag; } public function unique($where){ $where=$this->rewhere($where); $this->sql="SELECT * FROM `$table` WHERE $where LIMIT 1"; $result=$this->link->real_query($this->sql); if($result===false) trigger_error('
ERROR MESSAGE:'.$this->link->error.'
THE SQL:'.$this->sql); return $result -> fetch_object(); } public function countRow($where){ $where=$this->rewhere($where); $this->sql=($where=='')?"SELECT COUNT(*) as 'totalRow' FROM `{$this->tableString}`":"SELECT COUNT(*) FROM `{$this->tableString}` WHERE $where"; $result=$this->link->real_query($this->sql); if($result===false) trigger_error('
ERROR MESSAGE:'.$this->link->error.'
THE SQL:'.$this->sql); $record=$result->fetch_Object(); return $record->totalRow; } //按条件查找函数 public function search($table,$fields,$leftJoin='',$where='',$order='',$limit=''){ if(is_array($fields)) $fields=implode(",",$fields); $this->sql=empty($fields)?"SELECT * FROM `$table`":"SELECT $fields FROM `$table`"; if(!empty($where)){ $where=$this->rewhere($where); $this->sql.=" WHERE $where"; } if(!empty($order)) $this->sql.=" ORDER BY $order"; if(!empty($order)) $this->sql.="LIMIT $limit"; $result=$this->link->real_query($this->sql); if($result===false){ trigger_error('
ERROR MESSAGE:'.$this->link->error.'
THE SQL:'.$this->sql); }else{ $tempItem=array(); $tempList=array(); while($tempItem=$result -> fetch_assoc()){ if(!empty($tempItem['create_time'])) $tempItem['create_time_exp']=date('Y-m-d H:i:s',$tempItem['create_time']); if(!empty($tempItem['add_time'])) $tempItem['add_time_exp']=date('Y-m-d H:i:s',$tempItem['end_time']); if(!empty($tempItem['start_time'])) $tempItem['start_time_exp']=date('Y-m-d H:i:s',$tempItem['start_time']); if(!empty($tempItem['end_time'])) $tempItem['end_time_exp']=date('Y-m-d H:i:s',$tempItem['end_time']); $tempList[]=$tempItem; }; return $tempList; } } public function keyIn($ids){ if(!empty($ids)){ return false; } if(is_array($ids)){ foreach ($ids as $key => $value) { $ids[$key]=$this->link->real_escape_string($value); } $ids=implode(',',$ids); }else{ $ids=$this->link->real_escape_string($ids); } $primary=$this->getPrimaryKey(); if(!empty($this->whereString)) $this->whereString.=" AND "; $this->whereString.="`$primary` in ($ids)"; return $this; }/******************仿ThinkPHP的链式操作*******************************/ //设置查询的字段 public function field($field){ if(is_array($field)) $field=implode(',',$field); $this->fieldString=$field; return $this; } public function table($table){ $this->tableString=$table; } public function order($order){ $this->orderString=$order; } public function limit($limit){ $this->limitString=$limit; } //将数组格式的条件组装成字符串 public function where($where){ if(is_array($where)&&count($where)>0){ $str=''; foreach($where as $key=>$value){ if(!empty($str)){ $str.=' AND '; } $rekey=$this->link->real_escape_string($key); $revalue=$this->link->real_escape_string($value); $str.=is_numeric($revalue)?"`$rekey`=$revalue":"`$rekey`='$revalue'"; } $this->whereString=$str; }else{ $this->whereString=$where; } return $this; } //删除 public function delete(){ if(empty($this->whereString)) trigger_error('
ERROR MESSAGE:删除条件不可以是空'); $this->sql="DELETE FROM {$this->tableString} WHERE {$this->whereString}"; } //更新 public function update($model){ if(empty($this->whereString)) trigger_error('
ERROR MESSAGE:更新条件不可以是空'); if(empty($model)||count($model)==0) trigger_error('
ERROR MESSAGE:更新参数不可以是空') $assins=''; foreach ($model as $key => $value) { $rkey=$this->link->real_escape_string($key); $rvalue=$this->link->real_escape_string($value); if(!is_numeric($rvalue)){ $rvalue="'".$rvalue."'"; } $assins.=$assins?",`$rkey`=$rvalue":"`$rkey`=$rvalue"; } $this->sql="UPDATE {$this->tableString} SET $assins WHERE {$this->whereString}"; } //查询 public function select(){ $this->sql="SELECT {$this->fieldString} FROM {$this->tableString}"; if(!empty($this->whereString)) $this->sql.=' WHERE '.$this->whereString; if(!empty($this->orderString)) $this->sql.=' ORDER BY '.$this->orderString; if(!empty($this->limitString)) $this->sql.=' LIMIT '.$this->limitString; return $this->query($this->$sql); } /*开发环境用于调试的语句,生产环境可以删除*/ public function getSql(){ return $this->sql; }}

人气教程排行