基于PHP MySQLi扩展的数据库操作Model
程序员文章站
2022-04-25 23:31:04
...
'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; }} 推荐阅读
-
解析在PHP中使用mysqli扩展库对mysql的操作
-
PHP基于MySQLI函数封装的数据库连接工具类【定义与用法】
-
PHP数据库操作之基于Mysqli的数据库操作类库
-
php使用mysqli和pdo扩展，测试对比mysql数据库的执行效率完整示例
-
php使用mysqli和pdo扩展，测试对比连接mysql数据库的效率完整示例
-
基于Enterprise Library的Winform开发框架实现支持国产达梦数据库的扩展操作
-
PHP封装mysqli基于面向对象的mysql数据库操作类与用法示例
-
PHP基于MySQLI函数封装的数据库连接工具类【定义与用法】
-
PHP中的MySQLi扩展学习（五）MySQLI_STMT对象操作
-
PHP使用PDO、mysqli扩展实现与数据库交互操作详解