基于PHP MySQLi扩展的数据库操作Model

<?php/*****************************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'=>'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('<br/>ERROR MESSAGE:'.$this->link->error.'<br/>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('<br/>ERROR MESSAGE:'.$this->link->error.'<br/>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('<br/>ERROR MESSAGE:'.$this->link->error.'<br/>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('<br/>ERROR MESSAGE:'.$this->link->error.'<br/>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('<br/>ERROR MESSAGE:'.$this->link->error.'<br/>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('<br/>ERROR MESSAGE:'.$this->link->error.'<br/>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('<br/>ERROR MESSAGE:'.$this->link->error.'<br/>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('<br/>ERROR MESSAGE:'.$this->link->error.'<br/>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('<br/>ERROR MESSAGE:删除条件不可以是空');        $this->sql="DELETE FROM {$this->tableString} WHERE {$this->whereString}";    }    //更新    public function update($model){        if(empty($this->whereString))            trigger_error('<br/>ERROR MESSAGE:更新条件不可以是空');        if(empty($model)||count($model)==0)            trigger_error('<br/>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;    }}