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['DOCUMENT_ROOT']);
class NewSql {
 
    public $Db; //数据库实例
    public $Error = False;
    public $ErrorMsg = '';
 
    //设定数据库连接信息
    protected $_Sql = 'mysql';
    protected $_Host = 'localhost';
    protected $_Port = 3306;
    protected $_User = 'yxb';
    protected $_Pass = 'yxbsqlpass';
    protected $_DbName = ''; //数据库名
    //配置参数
    protected $_NoAllAction = 1; //全局设定，1为禁止全表更新、删除操作，0为允许
    protected $_LastSQL = Null; //记录最后一次执行的SQL语句，方便进行错误调试
    //初始化条件信息
    protected $_Where = '';
    protected $_OrderBy = '';
    protected $_Limit = '';
    protected $_Field = '*';
    //初始化标记信息
    protected $_Clear = 0; //状态，0表示查询条件干净，1表示查询条件污染
    protected $_LinkClass = 'PDO'; //标记，设定使用PDO还是SQL方式
 
    /**
     * 初始化类
     * @param string $DbName 设置数据库名
     */
 
    public function __construct($DbName = 'yxb'){
        $this->_DbName = $DbName;
        if(class_exists('PDO')) {
            $Db = new PDO($this->_Sql . ':host=' . $this->_Host . ';dbname=' . $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[] = "'".$this -> _Real_ValString(trim($V))."'";
        }
        $Query = "Insert Into {$TableName}(".implode(',',$KeyArr).") values(".implode(',',$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, '`') !== False){
                $Sql .= " `".trim($K)."`=".$this -> _Real_ValString(trim($V)).",";
            }else{
                $Sql .= " `".trim($K)."`='".$this -> _Real_ValString(trim($V))."',";
            }
        }
        $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 == 'PDO'){
            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 == 'PDO'){
            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("'?'",'?',$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 == 'PDO'){
            return $this -> Db -> prepare(trim($Query));
        }else{
            $this -> Error = True;
            $this -> ErrorMsg = "参数错误：您没有开启PDO类库，无法使用PrePare语句";
        }
    }
 
    /**
     * 开启事务
     */
    public function BeginTransaction(){
        if($this -> _LinkClass == 'PDO'){
            $this -> Db -> beginTransaction();
        }else{
            $this -> Db -> autocommit(False);
        }
    }
 
    /**
     * 提交事务
     */
    public function Commit(){
        if($this -> _LinkClass == 'PDO'){
            $this -> Db -> commit();
        }else{
            $this -> Db -> commit();
            $this -> Db -> autocommit(True);
        }
    }
 
    /**
     * 回滚事务
     */
    public function RollBack(){
        if($this -> _LinkClass == 'PDO'){
            $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 = '';
        $Sql .= " `".trim($Field)."` ".trim($Conditions1)." (";
        foreach($Options as $vo){
            $Sql .= "'".trim($vo)."',";
        }
        $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['Field1'] = array('Test','=','and');
     * 注释：如果二维数组中的第二字段为in,not in,between,not between，则第一字段必须是数组，如下
     * 示例2：$Options['Field1'] = array(array(2,4),'between','and');
     * 注释：如果需要操作多个同名字段，则第二字段必须为multi，第一字段必须为数组，如下
     * 示例3：$Options['Field1'] = array(array(array('10','<','or'),array('30','>','and')),'multi','and');
     * @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 = 'where';
            $ContConditions = 'and';
 
            //开始循环条件数组
            foreach($Option as $K=>$V){
                if(is_scalar($V)){
                    //如果值不是数组，那么直接将其作为查询条件的值，其他条件默认为=、and
                    $this -> _Where .= " `".$K."` = '".$V."' and";
                }else{
                    if(!empty($V[1]) &&strtolower($V[1] == 'multi')){
                        //处理多条件查询
                        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('in','not in'))){
                                $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('between','not between'))){
                                $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])." '".trim($V[0][$I][0])."' ".trim($V[0][$I][2]);
                            }
                            $ContConditions = $V[0][$I][2];
                        }
                    }elseif(!empty($V[1]) && in_array(strtolower(trim($V[1])),array('in','not in','between','not between'))){
                        if(!is_array($V[0])){
                            $this -> Error = True;
                            $this -> ErrorMsg = "条件错误：{$K}项第一字段必须为数组";
                            return False;
                        }
                        $Conditions = isset($V[2]) ? $V[2] : 'and';
                        $ContConditions = $Conditions;
                        if(in_array(strtolower($V[1]),array('in','not in'))){
                            $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] : '=';
                        $Conditions2 = isset($V[2]) ? $V[2] : 'and';
                        $ContConditions = $Conditions2;
                        $this -> _Where .= " `".$K."` ".$Conditions1." '".$V[0]."' ".$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('sort'=>'desc')
     * @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 = '';
        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 = '';
        $this -> _OrderBy = '';
        $this -> _Limit = '';
        $this -> _Clear = 0;
        $this -> _Field = '*';
    }
 
    /**
     * 手动清理标记
     * @return $this
     */
 
    public function ClearKey(){
        $this -> _Where = '';
        $this -> _OrderBy = '';
        $this -> _Limit = '';
        $this -> _Clear = 0;
        $this -> _Field = '*';
        return $this;
    }
 
    /**
     * @param string $Address 备份类型，默认为SERVER，用以返回后判断动作
     * @return mixed|string 返回备份信息的JSON格式数据
     */
 
    public function BackUp($Address = 'SERVER'){
 
        //连接数据库
 
        $DB = new MySqli($this -> _Host,$this -> _User,$this -> _Pass,$this -> _DbName);
        $DB -> query("set names utf8");
 
        //检查并创建备份目录、名称
        $FilePath = '/BackUp/';
        if(!file_exists(__WEBROOT__.$FilePath)){
            mkdir(__WEBROOT__.$FilePath,0777,True);
        }
        $FileName = 'Back'.date('YmdHis').'.sql';
 
        $File = fopen(__WEBROOT__.$FilePath.$FileName,'a');     //追加模式打开文件句柄
 
        //创建头部信息
        $Sql = Null;
        $Sql .= "-- Server Type :MySql \r\n";
        $Sql .= "-- Create User : \r\n";
        $Sql .= "-- Create Time :".date('Y-m-d H:i:s')." \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 .= "'".$DB -> real_escape_string($Val)."',";
                    }
                    $Sql = substr($Sql,0,strlen($Sql) - 1);
                    $Sql .= ");\r\n";
                }
            }else{
                $BackUpArr['address'] = strtoupper($Address);
                $BackUpArr['state'] = 1;
                $BackUpArr['msg'] = '失败：无法读取表语句，请重试或联系管理员';
                $BackUpArr['filepath'] = '';
                @unlink(__WEBROOT__.$FilePath.$FileName);
                return json_encode($BackUpArr);
            }
            $Sql .= "\r\n\r\n";
 
            fwrite($File,$Sql);     //每个数据库表插入一次
        }
        fclose($File);
 
        $BackUpArr['address'] = strtoupper($Address);
        $BackUpArr['state'] = 200;
        $BackUpArr['msg'] = '备份成功';
        $BackUpArr['filepath'] = $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');