mysql数据库处理类，持续更新(Update:2015-08-18)

简单的模仿TP做了一个数据库处理类，在平时处理数据库语句的时候可以方便一点
Select,PrePaer,Row_Count,Delete,Update,Insert方法应放在最后使用，Where,OrderBy,Limit,Field方法则不要求顺序，ClearKey方法如使用需放在最前使用，我为了以前代码的兼容性没有删除，已不建议使用

<?php
/**
 * Created by PhpStorm.
 * User: MCtion
 * Date: 2014/11/30 0030
 * Time: 15:40
 * 接收数据库名，连接数据库，提供CURD接口
 * 连接模式：PDO
 * 方法：Insert,Update,Delete,Select,PrePare,ClearKey,Limit,OrderBy,Where
 */
if(!defined("__WEBROOT__")) define("__WEBROOT__",$_SERVER[&#39;DOCUMENT_ROOT&#39;]);
class NewSql {
 
    public $Db; //数据库实例
    public $Error = False;
    public $ErrorMsg = &#39;&#39;;
 
    //设定数据库连接信息
    protected $_Sql = &#39;mysql&#39;;
    protected $_Host = &#39;localhost&#39;;
    protected $_Port = 3306;
    protected $_User = &#39;yxb&#39;;
    protected $_Pass = &#39;yxbsqlpass&#39;;
    protected $_DbName = &#39;&#39;; //数据库名
    //配置参数
    protected $_NoAllAction = 1; //全局设定，1为禁止全表更新、删除操作，0为允许
    protected $_LastSQL = Null; //记录最后一次执行的SQL语句，方便进行错误调试
    //初始化条件信息
    protected $_Where = &#39;&#39;;
    protected $_OrderBy = &#39;&#39;;
    protected $_Limit = &#39;&#39;;
    protected $_Field = &#39;*&#39;;
    //初始化标记信息
    protected $_Clear = 0; //状态，0表示查询条件干净，1表示查询条件污染
    protected $_LinkClass = &#39;PDO&#39;; //标记，设定使用PDO还是SQL方式
 
    /**
     * 初始化类
     * @param string $DbName 设置数据库名
     */
 
    public function __construct($DbName = &#39;yxb&#39;){
        $this->_DbName = $DbName;
        if(class_exists(&#39;PDO&#39;)) {
            $Db = new PDO($this->_Sql . &#39;:host=&#39; . $this->_Host . &#39;;dbname=&#39; . $this->_DbName, $this->_User, $this->_Pass) or die("PDO对象初始化失败");
            $Db->exec("set names utf8");
            $this -> Db = $Db;
            //因为PDO方式没有类似mysql_real_escape_string()函数的方法，因此如果是PDO连接就创建一个过程连接
            mysql_connect($this -> _Host.":".$this -> _Port,$this -> _User,$this -> _Pass);
        }else{
            $Db = new MySQLi($this -> _Host,$this -> _User,$this -> _Pass,$this -> _DbName,$this -> _Port) or die("MySQLi对象初始化失败");
            $Db -> query("set names utf8");
            $this -> Db = $Db;
            $this -> _LinkClass = "SQL";
        }
    }
 
    /**
     * __set方法的实现
     * @param $Key
     * @param $Val
     */
    public function __set($Key,$Val){
        if(isset($this -> $Key)){
            $this -> $Key = $Val;
        }
    }
 
    /**
     * 插入方法
     * @param string $TableName 操作的数据表名
     * @param array $Options 字段-值数组
     * @return int 受影响的行数
     */
 
    public function Insert($TableName,array $Options){
        if(!is_array($Options)){
            $this -> Error = True;
            $this -> ErrorMsg = "条件错误：插入语句必须传入插入数据";
            return False;
        }
        foreach($Options as $K=>$V){
            if(!is_scalar($K)) continue;
            $KeyArr[] = "`".trim($K)."`";
            $ValArr[] = "&#39;".$this -> _Real_ValString(trim($V))."&#39;";
        }
        $Query = "Insert Into {$TableName}(".implode(&#39;,&#39;,$KeyArr).") values(".implode(&#39;,&#39;,$ValArr).")";
        $this -> _LastSQL = $Query;
        //return $Query;
 
        $this -> _Clear = 1;
        $this -> Clear();
 
        if($this -> _LinkClass == "PDO"){
            return $this -> Db -> exec(trim($Query));
        }else{
            $this -> Db -> query(trim($Query));
            return $this -> Db -> affected_rows;
        }
    }
 
    /**
     * 删除方法
     * @param string $TableName 操作的数据表名
     * @return int 受影响的行数
     */
 
    public function Delete($TableName){
        if(!$this -> _Where && $this -> _NoAllAction){
            $this -> Error = True;
            $this -> ErrorMsg = "参数错误：没有指定更新条件，禁止全表更新";
            return False;
        };
        if($this -> Error){die($this -> ErrorMsg);}
        $Query = "Delete from {$TableName} ".trim($this -> _Where);
        $this -> _LastSQL = $Query;
 
        $this -> _Clear = 1;
        $this -> Clear();
 
        if($this -> _LinkClass == "PDO"){
            return $this -> Db -> exec(trim($Query));
        }else{
            $this -> Db -> query(trim($Query));
            return $this -> Db -> affected_rows;
        }
    }
 
    /**
     * 更新函数
     * @param string $TableName 操作的数据表名
     * @param array $Options 参数数组
     * @return int 受影响的行数
     */
 
    public function Update($TableName,array $Options){
        if(!$this -> _Where && $this -> _NoAllAction){
            $this -> Error = True;
            $this -> ErrorMsg = "参数错误：没有指定更新条件，禁止全表更新";
            return False;
        };
        if(!is_array($Options)){
            $this -> Error = True;
            $this -> ErrorMsg = "条件错误：更新语句必须传入更新数据";
            return False;
        }
        if($this -> Error){die($this -> ErrorMsg);}
        $Sql = "Update {$TableName} Set";
        foreach($Options as $K=>$V){
            if(!is_scalar($K)) continue;
            if(strpos($V, &#39;`&#39;) !== False){
                $Sql .= " `".trim($K)."`=".$this -> _Real_ValString(trim($V)).",";
            }else{
                $Sql .= " `".trim($K)."`=&#39;".$this -> _Real_ValString(trim($V))."&#39;,";
            }
        }
        $Sql = substr($Sql,0,strlen($Sql) - 1);
        $Query =  $Sql . " ".trim($this -> _Where);
        $this -> _LastSQL = $Query;
 
        $this -> _Clear = 1;
        $this -> Clear();
 
        if($this -> _LinkClass == "PDO"){
            return $this -> Db -> exec($Query);
        }else{
            $this -> Db -> query(trim($Query));
            return $this -> Db -> affected_rows;
        }
    }
 
    /**
     * 创建查询语句
     * @param $TableName
     * @return string
     */
    protected function _Create_Query($TableName){
        return trim("Select ".trim($this -> _Field)." from {$TableName} ".trim($this -> _Where)." ".trim($this -> _OrderBy)." ".trim($this -> _Limit));
    }
 
    /**
     * 查询函数
     * @param string $TableName 操作的数据表名
     * @return array 查询到的所有行数组
     */
 
    public function Select($TableName){
        if($this -> Error){die($this -> ErrorMsg);}
        $Query = $this -> _Create_Query($TableName);
        $this -> _LastSQL = $Query;
        //return $Query;
 
        $this -> _Clear = 1;
        $this -> Clear();
 
        $SqlObject =  $this -> Db -> query($Query);
 
        if($this -> _LinkClass == &#39;PDO&#39;){
            return $SqlObject -> fetchAll(PDO::FETCH_ASSOC);
        }else{
            return $SqlObject -> fetch_all(MYSQL_ASSOC);
        }
    }
 
    /**
     * 统计查询条数
     * @param $TableName
     * @return int
     */
    public function Row_Count($TableName){
        if($this -> Error){die($this -> ErrorMsg);}
        $Query = $this -> _Create_Query($TableName);
        $this -> _LastSQL = $Query;
 
        $this -> _Clear = 1;
        $this -> Clear();
 
        $SqlObject =  $this -> Db -> query($Query);
 
        if($this -> _LinkClass == &#39;PDO&#39;){
            return $SqlObject -> rowCount();
        }else{
            return $SqlObject -> num_rows;
        }
    }
 
    /**
     * PDO的PrePare语句，没有开启PDO类库是返回FALSE
     * @param string $TableName 要操作的数据表名
     * @return object|bool PrePare查询对象，需在外部调用相关方法
     */
 
    public function PrePare($TableName){
        if($this -> Error){die($this -> ErrorMsg);}
        $this -> _Where = str_replace("&#39;?&#39;",&#39;?&#39;,$this -> _Where);
        $Query = "Select ".trim($this -> _Field)." from {$TableName} ".trim($this -> _Where)." ".trim($this -> _OrderBy)." ".trim($this -> _Limit);
        $this -> _LastSQL = $Query;
 
        $this -> _Clear = 1;
        $this -> Clear();
 
        if($this -> _LinkClass == &#39;PDO&#39;){
            return $this -> Db -> prepare(trim($Query));
        }else{
            $this -> Error = True;
            $this -> ErrorMsg = "参数错误：您没有开启PDO类库，无法使用PrePare语句";
        }
    }
 
    /**
     * 开启事务
     */
    public function BeginTransaction(){
        if($this -> _LinkClass == &#39;PDO&#39;){
            $this -> Db -> beginTransaction();
        }else{
            $this -> Db -> autocommit(False);
        }
    }
 
    /**
     * 提交事务
     */
    public function Commit(){
        if($this -> _LinkClass == &#39;PDO&#39;){
            $this -> Db -> commit();
        }else{
            $this -> Db -> commit();
            $this -> Db -> autocommit(True);
        }
    }
 
    /**
     * 回滚事务
     */
    public function RollBack(){
        if($this -> _LinkClass == &#39;PDO&#39;){
            $this -> Db -> rollback();
        }else{
            $this -> Db -> rollback();
            $this -> Db -> autocommit(True);
        }
    }
 
    /**
     * 返回最后一次执行的SQL语句
     * @return null|string
     */
    public function GetLastSQL(){
        return $this -> _LastSQL;
    }
 
    /**
     * 字符串转义方法，以防止数据无法插入数据库
     * @param string $Val 转义的字符串
     * @return string
     */
    protected function _Real_ValString($Val){
        if($this -> _LinkClass == "PDO"){
            //如果连接方式为PDO则使用过程方法中的mysql_real_escape_string()函数
            return mysql_real_escape_string($Val);
        }else{
            return $this -> Db -> real_escape_string($Val);
        }
    }
 
    /**
     * 创建in,not in语句
     * @param $Field
     * @param array $Options
     * @param $Conditions1
     * @param $Conditions2
     * @return string
     */
    protected function _Create_In_Sql($Field,array $Options,$Conditions1,$Conditions2){
        $Sql = &#39;&#39;;
        $Sql .= " `".trim($Field)."` ".trim($Conditions1)." (";
        foreach($Options as $vo){
            $Sql .= "&#39;".trim($vo)."&#39;,";
        }
        $Sql = substr($Sql,0,strlen($Sql) - 1);
        $Sql .= ") ".trim($Conditions2);
        return $Sql;
    }
 
    /**
     * 创建between,not between语句
     * @param $Field
     * @param array $Options
     * @param $Conditions1
     * @param $Conditions2
     * @return string
     */
    protected function _Create_Between_Sql($Field,array $Options,$Conditions1,$Conditions2){
        return " `".trim($Field)."` ".trim($Conditions1)." ".trim($Options[0])." and ".trim($Options[1])." ".$Conditions2;
    }
 
    /**
     * 支持条件：=,>,<,>=,<=,in,not in,between,not between,like,not like,multi
     * 支持组合：and or
     * 示例：$Options[&#39;Field1&#39;] = array(&#39;Test&#39;,&#39;=&#39;,&#39;and&#39;);
     * 注释：如果二维数组中的第二字段为in,not in,between,not between，则第一字段必须是数组，如下
     * 示例2：$Options[&#39;Field1&#39;] = array(array(2,4),&#39;between&#39;,&#39;and&#39;);
     * 注释：如果需要操作多个同名字段，则第二字段必须为multi，第一字段必须为数组，如下
     * 示例3：$Options[&#39;Field1&#39;] = array(array(array(&#39;10&#39;,&#39;<&#39;,&#39;or&#39;),array(&#39;30&#39;,&#39;>&#39;,&#39;and&#39;)),&#39;multi&#39;,&#39;and&#39;);
     * @param array|string $Option 组合条件数组
     * @return $this
     */
 
    public function Where($Option){
        if($this -> _Clear > 0) $this -> Clear();
        if(is_string($Option)){
            $this -> _Where = trim($Option);
        }elseif(is_array($Option)){
            $this -> _Where = &#39;where&#39;;
            $ContConditions = &#39;and&#39;;
 
            //开始循环条件数组
            foreach($Option as $K=>$V){
                if(is_scalar($V)){
                    //如果值不是数组，那么直接将其作为查询条件的值，其他条件默认为=、and
                    $this -> _Where .= " `".$K."` = &#39;".$V."&#39; and";
                }else{
                    if(!empty($V[1]) &&strtolower($V[1] == &#39;multi&#39;)){
                        //处理多条件查询
                        if(!is_array($V[0])){
                            $this -> Error = True;
                            $this -> ErrorMsg = "条件错误：{$K}项第一字段必须为数组";
                            return False;
                        }
                        for($I = 0;$I < count($V[0]);$I++){
                            //遍历第一个条件数组
                            if(in_array(strtolower($V[0][$I][2]),array(&#39;in&#39;,&#39;not in&#39;))){
                                $this -> _Where .= $this -> _Create_In_Sql($K,$V[0][$I][0],$V[0][$I][1],$V[0][$I][2]);
                            }elseif(in_array(strtolower($V[0][$I][2]),array(&#39;between&#39;,&#39;not between&#39;))){
                                $this -> _Where .= $this -> _Create_Between_Sql($K,$V[0][$I][0],$V[0][$I][1],$V[0][$I][2]);
                            }else{
                                $this -> _Where .= " `".trim($K)."` ".trim($V[0][$I][1])." &#39;".trim($V[0][$I][0])."&#39; ".trim($V[0][$I][2]);
                            }
                            $ContConditions = $V[0][$I][2];
                        }
                    }elseif(!empty($V[1]) && in_array(strtolower(trim($V[1])),array(&#39;in&#39;,&#39;not in&#39;,&#39;between&#39;,&#39;not between&#39;))){
                        if(!is_array($V[0])){
                            $this -> Error = True;
                            $this -> ErrorMsg = "条件错误：{$K}项第一字段必须为数组";
                            return False;
                        }
                        $Conditions = isset($V[2]) ? $V[2] : &#39;and&#39;;
                        $ContConditions = $Conditions;
                        if(in_array(strtolower($V[1]),array(&#39;in&#39;,&#39;not in&#39;))){
                            $this -> _Where .= $this -> _Create_In_Sql($K,$V[0],$V[1],$Conditions);
                        }else{
                            $this -> _Where .= $this -> _Create_Between_Sql($K,$V[0],$V[1],$Conditions);
                        }
                    }else{
                        $Conditions1 = isset($V[1]) ? $V[1] : &#39;=&#39;;
                        $Conditions2 = isset($V[2]) ? $V[2] : &#39;and&#39;;
                        $ContConditions = $Conditions2;
                        $this -> _Where .= " `".$K."` ".$Conditions1." &#39;".$V[0]."&#39; ".$Conditions2;
                    }
                }
            }
            $this -> _Where = trim(substr($this -> _Where,0,strlen($this -> _Where) - strlen($ContConditions)));
            //return $this -> _Where;
        }else{
            $this -> Error = True;
            $this -> ErrorMsg = "条件错误：WHERE语句只允许使用字符串或数组";
            return False;
        }
 
        return $this;
    }
 
    /**
     * 设置排序
     * @param array $Val 排序条件数组 例:array(&#39;sort&#39;=>&#39;desc&#39;)
     * @return $this
     */
 
    public function OrderBy(array $Val){
        if($this -> _Clear > 0) $this -> Clear();
        $this -> _OrderBy = "order by ";
        foreach($Val as $K=>$V){
            $this -> _OrderBy .= trim($K)." ".trim($V).",";
        }
        $this -> _OrderBy = trim(substr($this -> _OrderBy,0,strlen($this -> _OrderBy) - 1));
        return $this;
    }
 
    /**
     * 设置查询行数及页数
     * @param $Page $PageSize不为空时为页数，否则为行数
     * @param null $PageSize 为空则函数设定取出行数，不为空则设定取出行数及页数
     * @return $this
     */
 
    public function Limit($Page,$PageSize = null){
        if($this -> _Clear > 0) $this -> Clear();
        if($PageSize == null){
            $this -> _Limit = "limit ".$Page;
        }else{
            $SelStart = ($Page - 1) * $PageSize;
            $this -> _Limit = "limit ".$SelStart.",".$PageSize;
        }
        return $this;
    }
 
    /**
     * 设置查询字段
     * @param array $Field 字段数组
     * @return $this
     */
 
    public function Field(array $Field){
        if($this -> _Clear > 0) $this -> Clear();
        $this -> _Field = &#39;&#39;;
        foreach($Field as $K=>$V){
            $this -> _Field .= trim($V).",";
        }
        $this -> _Field = trim(substr($this -> _Field,0,strlen($this -> _Field) - 1));
        return $this;
    }
 
    /**
     * 清理标记函数
     */
 
    protected function Clear(){
        $this -> _Where = &#39;&#39;;
        $this -> _OrderBy = &#39;&#39;;
        $this -> _Limit = &#39;&#39;;
        $this -> _Clear = 0;
        $this -> _Field = &#39;*&#39;;
    }
 
    /**
     * 手动清理标记
     * @return $this
     */
 
    public function ClearKey(){
        $this -> _Where = &#39;&#39;;
        $this -> _OrderBy = &#39;&#39;;
        $this -> _Limit = &#39;&#39;;
        $this -> _Clear = 0;
        $this -> _Field = &#39;*&#39;;
        return $this;
    }
 
    /**
     * @param string $Address 备份类型，默认为SERVER，用以返回后判断动作
     * @return mixed|string 返回备份信息的JSON格式数据
     */
 
    public function BackUp($Address = &#39;SERVER&#39;){
 
        //连接数据库
 
        $DB = new MySqli($this -> _Host,$this -> _User,$this -> _Pass,$this -> _DbName);
        $DB -> query("set names utf8");
 
        //检查并创建备份目录、名称
        $FilePath = &#39;/BackUp/&#39;;
        if(!file_exists(__WEBROOT__.$FilePath)){
            mkdir(__WEBROOT__.$FilePath,0777,True);
        }
        $FileName = &#39;Back&#39;.date(&#39;YmdHis&#39;).&#39;.sql&#39;;
 
        $File = fopen(__WEBROOT__.$FilePath.$FileName,&#39;a&#39;);     //追加模式打开文件句柄
 
        //创建头部信息
        $Sql = Null;
        $Sql .= "-- Server Type :MySql \r\n";
        $Sql .= "-- Create User : \r\n";
        $Sql .= "-- Create Time :".date(&#39;Y-m-d H:i:s&#39;)." \r\n\r\n\r\n";
 
        fwrite($File,$Sql);     //写入头部信息
 
        $Databases = $DB -> query("show tables");       //查询所有数据表
 
        while($vo = $Databases -> fetch_array(MYSQL_NUM)){        //遍历数据表
            $Sql = Null;
            $Table = $DB -> query("show create table ".$vo[0]);     //查询当前表的创建语句
            if($vo2 = $Table -> fetch_array(MYSQL_NUM)){
                $Sql = Null;
                //创建表语句
                $Sql .= "-- Create Table ".$vo2[0]." \r\n";
                $Sql .= $vo2[1].";\r\n\r\n";
 
                //创建数据语句
                $Sql .= "-- Insert Table ".$vo2[0]." \r\n";
                $Insert = $DB -> query("select * from ".$vo2[0]);
                while($vo3 = $Insert -> fetch_array(MYSQL_ASSOC)){
                    $Sql .= "Insert Into ".$vo2[0]." Values(";
                    foreach($vo3 as $Key=>$Val){
                        $Sql .= "&#39;".$DB -> real_escape_string($Val)."&#39;,";
                    }
                    $Sql = substr($Sql,0,strlen($Sql) - 1);
                    $Sql .= ");\r\n";
                }
            }else{
                $BackUpArr[&#39;address&#39;] = strtoupper($Address);
                $BackUpArr[&#39;state&#39;] = 1;
                $BackUpArr[&#39;msg&#39;] = &#39;失败：无法读取表语句，请重试或联系管理员&#39;;
                $BackUpArr[&#39;filepath&#39;] = &#39;&#39;;
                @unlink(__WEBROOT__.$FilePath.$FileName);
                return json_encode($BackUpArr);
            }
            $Sql .= "\r\n\r\n";
 
            fwrite($File,$Sql);     //每个数据库表插入一次
        }
        fclose($File);
 
        $BackUpArr[&#39;address&#39;] = strtoupper($Address);
        $BackUpArr[&#39;state&#39;] = 200;
        $BackUpArr[&#39;msg&#39;] = &#39;备份成功&#39;;
        $BackUpArr[&#39;filepath&#39;] = $FilePath.$FileName;
 
        return json_encode($BackUpArr);
    }
}

示例

$DbRes = new NewSql();
 
$ConditionsTempArr['id'] = array(array(1,10),'between');
$ConditionsTempArr['title'] = array('测试数据','=','or');
$ConditionsTempArr['description'] = array('%测试数据%','like','or');
$ConditionsTempArr['sort'] = array(array(array(array(3,5),'in','or'),array(10,'>','and')),'multi','and');
 
$OptionsInsertArr['description'] = 3;
$OptionsUpdateArr['number'] = "`number` + 3";
$DbRes -> Where($ConditionsTempArr) -> Select('img');
$DbRes -> Insert('img',$OptionsInsertArr);
$DbRes -> Where($ConditionsTempArr) -> Update('img',$OptionsUpdateArr);
$DbRes -> Where($ConditionsTempArr) -> Delete('img');