/*****************************
Model类(使用MySQL扩展访问数据库)
******************************/
class Model{
private $table;
//数据库链接
private $link;
//最后一次执行的sql语句
private $sql;
private $tableString;
private $fieldString;
private $whereString;
private $orderString;
private $limitString;
//初始化类
public function __construct($tableName){
$dbinfo=array(
'hostname'=>'',
'username'=>'',
'password'=>'',
'database'=>''
);
$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;
}
}