PHP操作数据库类

<?php
/**
 * 功能：  数据库操作类 .
 * 作者:  赵铭哲 
 * 日期: 2016-05-23
 * 时间: 9:43
 */

namespace ZH\DataBase;

use \Exception as Exception;

class mysql {

    #region ----声明公用变量----
    public $db;             //数据库连接对象
    public $config;         //数据库配置数据
    public $server;         //服务器连接地址
    public $username;       //数据库用户名
    public $password;       //数据库密码
    public $sql;            //sql语句变量
    public $defaultDatabase;//默认设置的数据库
    public $limitcount;     //限制的数量
    public $message = array(
        'charset'=>'',      //返回
        'execsql'=>'',      //执行的sql语句
        'resmsg'=>'',       //结果信息
        'recordmsg'=>'',    //记录信息
        'affected_rows'=>0, //上次执行SQL所影响的行数
        'errormsg'=>'',     //错误信息
        'errorcode'=>'',    //错误代码
        'errorline'=>'',    //错误行
        'errorfile'=>''     //错误文件
    );
    #endregion

    #region ----声明公用常量----
    const SELECT = "SELECT ";
    const INSERT = "INSERT ";
    const UPDATE = "UPDATE ";
    const DELETE = "DELETE ";
    const FROM = "FROM ";
    const WHERE = " WHERE 1=1 ";
    const INTO = "INTO ";
    const SET = "SET ";
    const VALUES = "VALUES ";
    const ORDERBY = " ORDER BY ";
    const GROUPBY = " GROUP BY ";
    const HAVING = "HAVING ";
    const LIMIT = " LIMIT ";
    const JOIN = "JOIN ";
    const ON = "ON ";
    const _AND = "AND ";
    const _OR = "OR ";
    const _LIKE = "LIKE ";
    const _IN = "IN ";
    const _BETWEEN = "BETWEEN ";
    #endregion

    /**
     * 构造函数
     */
    public function __construct() {
        //开发环境
        $this->server = "localhost";
        $this->port = "3306";
        $this->username = "root";
        $this->password = "123456";
    }

    /**
     * 功能:析构函数
     * 作者: 赵铭哲
     * 日期: 2016-06-03
     */
    public function _destruct(){
        $this->server = '';
        $this->username = '';
        $this->password = '';
        $this->port = '';
        $this->defaultDatabase = '';
        $this->dbDisconnect();
    }

    #region ----数据库连接----
    /**
     * 功能:连接数据库
     * 作者: 赵铭哲
     * 日期: 2016-05-28
     * @return string
     */
    public function dbConnect(){
        try{
            //连接mysql数据库
            $this->db=mysql_connect($this->server.':'.$this->port,$this->username,$this->password);
            $charset = mysql_client_encoding($this->db);
            if($this->db == false){
                $this->message['errormsg'] = "Connected failed " . mysqli_connect_error();;
            }else{
                $this->message['resmsg'] = "Connected successfully.";
            }
            $this->message['charset'] = $charset;
        }catch (Exception $ex){
            $this->message['errorcode'] = "Exception code:".$ex->getCode();
            $this->message['errormsg'] = "Exception msg:".$ex->getMessage();
            $this->message['errorline'] = "Exception line:".$ex->getLine();
            $this->message['errorfile'] = "Exception file:".$ex->getFile();
        }
        return $this->message;
    }

    /**
     * 功能:断开数据库连接
     * 作者: 赵铭哲
     * 日期: 2016-05-28
     */
    public function dbDisconnect(){
        mysql_close($this->db);
    }

    /**
     * 功能:选择数据库
     * 作者: 赵铭哲
     * 日期: 2016-06-12
     */
    public function dbSelectDataBase($databaseName){
        try{
            if(isset($databaseName)){
                mysql_select_db($databaseName);
            }else{
                mysql_select_db($this->defaultDatabase);
            }
            $this->message['resmsg'] = "Select Database successfully.";
        }catch (Exception $ex){
            $this->message['errorcode'] = "Exception code:".$ex->getCode();
            $this->message['errormsg'] = "Exception msg:".$ex->getMessage();
            $this->message['errorline'] = "Exception line:".$ex->getLine();
            $this->message['errorfile'] = "Exception file:".$ex->getFile();
        }
        return $this->message;
    }

    #endregion



    #region ----数据库查询----
    /**
     * 功能:通用执行
     * 作者: 赵铭哲
     * 日期: 2016-06-12
     * @param $sql      普通的SQL语句
     * @return array    返回一个信息数组，方便查看执行后的结果
     */
    public function dbExec($sql){
        $this->dbConnect();
        $this->dbSelectDataBase($this->defaultDatabase);
        try{
            $isQuery = mysql_query($sql);
            if($isQuery){
                $this->message['resmsg'] .= "Query Successful";
            }else{
                $this->message['resmsg'] .= "Query Failed";
            }
            $this->message['affected_rows'] = mysql_affected_rows();
        }catch (Exception $ex){
            $this->message['errorcode'] .= "Exception code:".$ex->getCode();
            $this->message['errormsg'] .= "Exception msg:".$ex->getMessage();
            $this->message['errorline'] .= "Exception line:".$ex->getLine();
            $this->message['errorfile'] .= "Exception file:".$ex->getFile();
        }
        $this->dbDisconnect();
        return $this->message;
    }

    /**
     * 功能:通用查询获取单条数据
     * 作者: 赵铭哲
     * 日期: 2016-06-03
     * @param $sql      普通的SQL语句
     * @return resource 返回数组格式的单条数据信息
     */
    public function dbQuerySingle($sql){
        $this->dbConnect();
        $this->dbSelectDataBase($this->defaultDatabase);
        try{
            $obj = mysql_query($sql);
            $row = mysql_fetch_array($obj, MYSQL_BOTH);
            $this->message['affected_rows'] = mysql_affected_rows();
            mysql_free_result($obj);
            return $row;
        }catch (Exception $ex){
            $this->message['errorcode'] .= "Exception code:".$ex->getCode();
            $this->message['errormsg'] .= "Exception msg:".$ex->getMessage();
            $this->message['errorline'] .= "Exception line:".$ex->getLine();
            $this->message['errorfile'] .= "Exception file:".$ex->getFile();
        }
        $this->dbDisconnect();
    }

    /**
     * 功能:获取多条数据
     * 作者: 赵铭哲
     * 日期: 2016-06-14
     * @param $sql      普通的SQL语句
     * @return array    返回多条数据的数组
     */
    public function dbQueryMutiple($sql){
        $this->dbConnect();
        $this->dbSelectDataBase($this->defaultDatabase);
        try{
            $result = array();
            $obj = mysql_query($sql);
            while($row = mysql_fetch_array($obj, MYSQL_BOTH)){
                array_push($result,$row);
            }
            $this->message['affected_rows'] = mysql_affected_rows();
            mysql_free_result($obj);
            return $result;
        }catch (Exception $ex){
            $this->message['errorcode'] .= "Exception code:".$ex->getCode();
            $this->message['errormsg'] .= "Exception msg:".$ex->getMessage();
            $this->message['errorline'] .= "Exception line:".$ex->getLine();
            $this->message['errorfile'] .= "Exception file:".$ex->getFile();
        }
        $this->dbDisconnect();
    }

    /**
     * 功能:扩展查询功能
     * 作者: 赵铭哲
     * 日期: 2016-06-14
     * @param $table    表名
     * @param $cols     列名：数组格式(array(1,2,3))|字符串格式(1,2,3)
     * @param $where    Where语句：一维数组格式(array(a=>'1',b=>'2'))|字符串格式(a='1',b='2')
     * @param $order    OrderBy语句：字符串格式(id asc|id desc)
     * @param $group    GroupBy语句：字符串格式(id)
     * @param $limit    Limit语句：字符串格式(1|1,2)
     * @return array
     */
    public function dbQueryExt($table,$cols='*',$where=null,$order=null,$group=null,$limit=null){
        $this->dbConnect();
        $this->dbSelectDataBase($this->defaultDatabase);
        try{
            $result = array();
            $strWhere = self::WHERE;
            $strCols = '';

            if(is_array($cols)){
                foreach($cols as $kc => $vc){
                    $strCols .= $vc.',';
                }
                $strCols = substr($strCols,0,strlen($strCols)-1);
            }else if(is_string($cols)){
                $strCols = $cols;
            }
            $this->sql = self::SELECT.$strCols.' '.self::FROM.$table;
            if(is_array($where)){
                foreach($where as $kw=>$vw){
                    $strWhere .= " and ".$kw."='".$vw."'";
                }
                $strWhere = substr($strWhere,0,strlen($strWhere)-5);
            }else{
                $strWhere .= $where;
            }
            $this->sql .= $strWhere;
            if(isset($order)){
                $this->sql .= self::ORDERBY.$order;
            }
            if(isset($group)){
                $this->sql .= self::GROUPBY.$group;
            }
            if(isset($limit)){
                $this->sql .= self::LIMIT.$limit;
            }
            $this->message['execsql'] = $this->sql;
            $obj = mysql_query($this->sql);
            while($row = mysql_fetch_array($obj, MYSQL_BOTH)){
                array_push($result,$row);
            }
            $this->message['affected_rows'] = mysql_affected_rows();
            mysql_free_result($obj);
            return $result;
        }catch (Exception $ex){
            $this->message['errorcode'] .= "Exception code:".$ex->getCode();
            $this->message['errormsg'] .= "Exception msg:".$ex->getMessage();
            $this->message['errorline'] .= "Exception line:".$ex->getLine();
            $this->message['errorfile'] .= "Exception file:".$ex->getFile();
        }
        $this->dbDisconnect();
    }

    /**
     * 功能:扩展查询功能2
     * 作者: 赵铭哲
     * 日期: 2016-07-20
     * @param $table         表名
     * @param string $cols   列名：数组格式(array(1,2,3))|字符串格式(1,2,3)
     * @param null $where    Where语句：
     *                          一维数组格式(array(a=>'1',b=>'2'))|字符串格式(a='1',b='2')
     *                          二维数组格式(array(array(a1=>'1',a2=>'2'),array(a3=>'3',a4=>'4'))|array(array(1,2),array(3,4)))
     * @return array
     */
    public function dbQueryExtTwo($table,$cols='*',$where=null,$pager = array(),$sort = array()){
        try{
            $result = array();
            $strWhere = self::WHERE;
            $strCols = '';

            if(is_array($cols)){
                foreach($cols as $kc => $vc){
                    $strCols .= $vc.',';
                }
                $strCols = substr($strCols,0,strlen($strCols)-1);
            }else if(is_string($cols)){
                $strCols = $cols;
            }
            $this->sql = self::SELECT.$strCols.' '.self::FROM.$table;
            if(is_array($where)){
                $startIndex = 0;
                foreach($where as $kw=>$vw){
                    if(is_array($vw)){
                        foreach($vw as $k => $v){
                            if($startIndex == 0){
                                $strWhere .= " and ".$k.$this->formatWhere($v);
                            }else{
                                $strWhere .= $k.$this->formatWhere($v)." and ";
                            }
                        }
                        if($startIndex > 0){
                            $strWhere = substr($strWhere,0,strlen($strWhere)-5);
                        }
                        $strWhere .= " or  ";
                    }else{
                        $strWhere .= " and ".$kw."='".$vw."'";
                    }
                    $startIndex = $startIndex + 1;
                }
                $strWhere = substr($strWhere,0,strlen($strWhere)-5);
            }else{
                $strWhere .= $where;
            }
            //增加分页的功能
            if (!empty($pager)) {
                if (!isset($pager['page']) || $pager['page'] < 1) {
                    $pager['page'] = 1;
                }
                $offset = ($pager['page'] - 1) * $pager['page_size'];
                $limit = $pager['page_size'];
                $strWhere .= sprintf(' LIMIT %d,%d', $offset, $limit);
            }
            $this->sql .= $strWhere;
            $this->message['execsql'] = $this->sql;
            $result = $this->dbQueryMutiple($this->sql);
            return $result;
        }catch (Exception $ex){
            $this->message['errorcode'] .= "Exception code:".$ex->getCode();
            $this->message['errormsg'] .= "Exception msg:".$ex->getMessage();
            $this->message['errorline'] .= "Exception line:".$ex->getLine();
            $this->message['errorfile'] .= "Exception file:".$ex->getFile();
        }
        $this->dbDisconnect();
    }
    #endregion

    #region ----数据库插入----
    /**
     * 功能:通用插入单条数据
     * 作者: 赵铭哲
     * 日期: 2016-06-14
     * @param $table    表名
     * @param $cols     列名：数组格式(array(1,2,3))|字符串格式(1,2,3)
     * @param $values   值：数组格式(array(1,2,3))|字符串格式(1,2,3)
     */
    public function dbInsertSingle($table,$cols,$values){
        //判断$cols,$values是否是数组，是则自动组合，否则直接返回值
        $strCols = '';
        $strValues = '';
        if(is_array($cols)){
            foreach($cols as $kc => $vc){
                $strCols .= $vc.',';
            }
            $strCols = substr($strCols,0,strlen($strCols)-1);
        }else if(is_string($cols)){
            $strCols = $cols;
        }else{
            $this->message['errmsg'] = "数据类型不合法";
        }

        if(is_array($values)){
            foreach($values as $kv => $vv){
                $strValues .= "'".$vv."',";
            }
            $strValues = substr($strValues,0,strlen($strValues)-1);
        }else if(is_string($values)){
            $strValues = $this->CombinationInsert($values);
        }else{
            $this->message['errmsg'] = "数据类型不合法";
        }
        $this->sql = self::INSERT.self::INTO.$table."(".$strCols.") ".self::VALUES."(".$strValues.")";
        $this->dbExec($this->sql);
    }
    #endregion

    #region ----数据库修改----
    /**
     * 功能:通用更新
     * 作者: 赵铭哲
     * 日期: 2016-06-14
     * @param $table    表名
     * @param $values   更新的数据：数组格式(array(a=>'1',b=>'2'))|字符串格式(a='1',b='2')
     * @Param $where    参数Where:数组格式(array(a=>'1',b=>'2'))|字符串格式(a='1',b='2')
     */
    public function dbUpdate($table,$values,$where=null){
        $strValues = '';
        $strWhere = '';
        if(is_array($values)){
            foreach($values as $k=>$v){
                $strValues .= $k."='".$v."',";
            }
            $strValues = substr($strValues,0,strlen($strValues)-1);
        }else{
            $strValues = $strValues;
        }
        $this->sql = self::UPDATE.$table.' '.self::SET.$strValues;

        if(is_array($where)){
            foreach($where as $kw=>$vw){
                $strWhere .= $kw."='".$vw."' and ";
            }
            $strWhere = substr($strWhere,0,strlen($strWhere)-5);
        }else{
            $strWhere = $where;
        }
        $this->sql .= self::WHERE.self::_AND.$strWhere;
        $this->dbExec($this->sql);
    }
    #endregion

    #region ----数据库删除----
    /**
     * 功能:通用删除
     * 作者: 赵铭哲
     * 日期: 2016-06-14
     * @param $table    表名
     * @param $where    Where语句：数组格式(array(a=>'1',b=>'2'))|字符串格式(a='1',b='2')
     */
    public function dbDelete($table,$where){
        $strWhere = '';
        $this->sql = self::DELETE.self::FROM.$table.self::WHERE;
        if(is_array($where)){
            foreach($where as $kw=>$vw){
                $strWhere .= $kw."='".$vw."' and ";
            }
            $strWhere = substr($strWhere,0,strlen($strWhere)-5);
        }else{
            $strWhere = $where;
        }
        $this->sql .= self::_AND.$strWhere;
        $this->dbExec($this->sql);
        print_r($this->sql);
    }
    #endregion

    #region ----格式化返回数据----
    /**
     * 功能:格式化数据
     * 作者: 赵铭哲
     * 日期: 2016-07-21
     * @param $data     查询出来的结果集：数组形式
     * @return array
     */
    public function formatData($data){
        return array(
            'statistic' => count($data),
            'data' => $data,
        );
    }

    /**
     * 功能:格式化Where子句
     * 作者: 赵铭哲
     * 日期: 2016-07-21
     * @param $condition    数组格式(array('operation'=>'eq','value'=>''))|字符串格式(='')
     * @return string
     */
    public function formatWhere($condition){
        if(is_array($condition)){
            if($condition['operation'] == "lt"){
                return "<'".$condition['value']."'";
            }else if($condition['operation'] == "eq"){
                return "='".$condition['value']."'";
            }else if($condition['operation'] == "gt"){
                return ">'".$condition['value']."'";
            }else if($condition['operation'] == "le"){
                return "<='".$condition['value']."'";
            }else if($condition['operation'] == "ge"){
                return ">='".$condition['value']."'";
            }else if($condition['operation'] == "neq"){
                return "<>'".$condition['value']."'";
            }else if($condition['operation'] == "like"){
                return "like'%".$condition['value']."%'";
            }else if($condition['operation'] == "in"){
                if(is_array($condition['value'])){
                    return " in(".implode(',',$condition['value']).")";
                }else{
                    return " in('".$condition['value']."')";
                }
            }
        }else {
            return "='".$condition."'";
        }
    }
    #endregion


    /**
     * 功能:转换编码格式
     * 作者: 赵铭哲
     * 日期: 2016-06-06
     */
    function ConvertEncode($value,$old_Encode,$new_Encode){
        header("content-Type: text/html; charset=Utf-8");
        $result = mb_convert_encoding($value, $old_Encode, $new_Encode);
        return $result;
    }

    /**
     * 功能:组合传入的字符为数据库插入时候的值
     * 作者: 赵铭哲
     * 日期: 2016-06-14
     * @param $value
     * @return string
     */
    function CombinationInsert($value){
        $result = '';
        if(strstr($value,',') > -1){
            $valTemp = explode(',',$value);
            foreach($valTemp as $k => $v){
                $result .= "'".$v."',";
            }
            $result = substr($result,0,strlen($result)-1);
        }else{
            $this->message['resmsg'] = "在传入的参数中未找到相关字符";
        }
        return $result;
    }

}