phpcmsv9 数据库操作类mysqli支持php7版本

<?php
/**
 *  mysql.class.php 数据库实现类
 *
 * @copyright           (C) 2005-2010 PHPCMS
 * @license             http://www.phpcms.cn/license/
 * @lastmodify          2010-6-1
 */

final class Database {

    /**
     * 数据库配置信息
     */
    private $config = null;

    /**
     * 数据库连接资源句柄
     */
    public $link = null;

    /**
     * 最近一次查询资源句柄
     */
    public $lastqueryid = null;

    /**
     *  统计数据库查询次数
     */
    public $querycount = 0;

    public function __construct() {

    }

    /**
     * 打开数据库连接,有可能不真实连接数据库
     * @param $config   数据库连接参数
     *
     * @return void
     */
    public function open($config) {
        $this->config = $config;
        if($config[&#39;autoconnect&#39;] == 1) {
            $this->connect();
        }
    }

    /**
     * 真正开启数据库连接
     *
     * @return void
     */
    public function connect() {
        //var_dump($this->config);
        if(!$this->link = @new mysqli($this->config[&#39;hostname&#39;], $this->config[&#39;username&#39;],$this->config[&#39;password&#39;], $this->config[&#39;database&#39;], $this->config[&#39;dbport&#39;])){
            $this->halt(&#39;Can not connect to MySQL server&#39;);
            return false;
        }
        if ($this->link->connect_errno) {
            $this->halt("Connect failed: %s\n", $this->link->connect_error);
            exit();
        }

        switch ($this->config[&#39;charset&#39;]){
            case &#39;utf8&#39;:
                $query_string = "
                         SET CHARACTER_SET_CLIENT = utf8,
                         CHARACTER_SET_CONNECTION = utf8,
                         CHARACTER_SET_DATABASE = utf8,
                         CHARACTER_SET_RESULTS = utf8,
                         CHARACTER_SET_SERVER = utf8,
                         COLLATION_CONNECTION = utf8_general_ci,
                         COLLATION_DATABASE = utf8_general_ci,
                         COLLATION_SERVER = utf8_general_ci,
                         sql_mode=&#39;&#39;";
                break;
            case &#39;GBK&#39;:
                $query_string = "
                        SET CHARACTER_SET_CLIENT = gbk,
                         CHARACTER_SET_CONNECTION = gbk,
                         CHARACTER_SET_DATABASE = gbk,
                         CHARACTER_SET_RESULTS = gbk,
                         CHARACTER_SET_SERVER = gbk,
                         COLLATION_CONNECTION = gbk_chinese_ci,
                         COLLATION_DATABASE = gbk_chinese_ci,
                         COLLATION_SERVER = gbk_chinese_ci,
                         sql_mode=&#39;&#39;";
                break;
            default:
                $error = "Db Error: charset is Invalid";
                $this->halt($error);
        }
        //进行编码声明
        if (!$this->link->query($query_string)){
            $this->halt("Db Error: ".mysqli_error($this->link));
        }


        $this->database = $this->config[&#39;database&#39;];
        return $this->link;
    }

    /**
     * 数据库查询执行方法
     * @param $sql 要执行的sql语句
     * @return 查询资源句柄
     */
    private function execute($sql) {
        //echo "<br>".$sql;
        if(!is_resource($this->link)) {
            $this->connect();
        }

        $this->lastqueryid = $this->link->query($sql);
        // $this->halt(mysqli_error($this->link), $sql);

        $this->querycount++;
        return $this->lastqueryid;
    }

    /**
     * 执行sql查询
     * @param $data         需要查询的字段值[例`name`,`gender`,`birthday`]
     * @param $table        数据表
     * @param $where        查询条件[例`name`=&#39;$name&#39;]
     * @param $limit        返回结果范围[例：10或10,10 默认为空]
     * @param $order        排序方式    [默认按数据库默认方式排序]
     * @param $group        分组方式    [默认为空]
     * @param $key          返回数组按键名排序
     * @return array        查询结果集数组
     */
    public function select($data, $table, $where = &#39;&#39;, $limit = &#39;&#39;, $order = &#39;&#39;, $group =&#39;&#39;, $key = &#39;&#39;) {
        $where = $where == &#39;&#39; ? &#39;&#39; : &#39; WHERE &#39;.$where;
        $order = $order == &#39;&#39; ? &#39;&#39; : &#39; ORDER BY &#39;.$order;
        $group = $group == &#39;&#39; ? &#39;&#39; : &#39; GROUP BY &#39;.$group;
        $limit = $limit == &#39;&#39; ? &#39;&#39; : &#39; LIMIT &#39;.$limit;
        $field = explode(&#39;,&#39;, $data);
        array_walk($field, array($this, &#39;add_special_char&#39;));
        $data = implode(&#39;,&#39;, $field);

        $sql = &#39;SELECT &#39;.$data.&#39; FROM `&#39;.$this->config[&#39;database&#39;].&#39;`.`&#39;.$table.&#39;`&#39;.$where.$group.$order.$limit;
        $this->execute($sql);
        if(!$this->lastqueryid) {
            return $this->lastqueryid;
        }

        $datalist = array();
        while(($rs = $this->fetch_next()) != false) {
            if($key) {
                $datalist[$rs[$key]] = $rs;
            } else {
                $datalist[] = $rs;
            }
        }
        $this->free_result();
        return $datalist;
    }

    /**
     * 获取单条记录查询
     * @param $data         需要查询的字段值[例`name`,`gender`,`birthday`]
     * @param $table        数据表
     * @param $where        查询条件
     * @param $order        排序方式    [默认按数据库默认方式排序]
     * @param $group        分组方式    [默认为空]
     * @return array/null   数据查询结果集,如果不存在，则返回空
     */
    public function get_one($data, $table, $where = &#39;&#39;, $order = &#39;&#39;, $group = &#39;&#39;) {
        $where = $where == &#39;&#39; ? &#39;&#39; : &#39; WHERE &#39;.$where;
        $order = $order == &#39;&#39; ? &#39;&#39; : &#39; ORDER BY &#39;.$order;
        $group = $group == &#39;&#39; ? &#39;&#39; : &#39; GROUP BY &#39;.$group;
        $limit = &#39; LIMIT 1&#39;;
        $field = explode( &#39;,&#39;, $data);
        array_walk($field, array($this, &#39;add_special_char&#39;));
        $data = implode(&#39;,&#39;, $field);

        $sql = &#39;SELECT &#39;.$data.&#39; FROM `&#39;.$this->config[&#39;database&#39;].&#39;`.`&#39;.$table.&#39;`&#39;.$where.$group.$order.$limit;
        $this->execute($sql);
        $res = $this->fetch_next();
        $this->free_result();
        return $res;
    }

    /**
     * 遍历查询结果集
     * @param $type     返回结果集类型
     *                  MYSQLI_ASSOC，MYSQL_NUM 和 MYSQL_BOTH
     * @return array
     */
    public function fetch_next($type=MYSQLI_ASSOC) {
        $res = mysqli_fetch_array($this->lastqueryid, $type);
        if(!$res) {
            $this->free_result();
        }
        return $res;
    }

    /**
     * 释放查询资源
     * @return void
     */
    public function free_result() {
        if(is_resource($this->lastqueryid)) {
            mysqli_free_result($this->lastqueryid);
            $this->lastqueryid = null;
        }
    }

    /**
     * 直接执行sql查询
     * @param $sql                          查询sql语句
     * @return  boolean/query resource      如果为查询语句，返回资源句柄，否则返回true/false
     */
    public function query($sql) {
        //echo "<Br>".$sql;
        return $this->execute($sql);
    }

    /**
     * 执行添加记录操作
     * @param $data         要增加的数据，参数为数组。数组key为字段值，数组值为数据取值
     * @param $table        数据表
     * @return boolean
     */
    public function insert($data, $table, $return_insert_id = false, $replace = false) {
        if(!is_array( $data ) || $table == &#39;&#39; || count($data) == 0) {
            return false;
        }

        $fielddata = array_keys($data);
        $valuedata = array_values($data);
        array_walk($fielddata, array($this, &#39;add_special_char&#39;));
        array_walk($valuedata, array($this, &#39;escape_string&#39;));

        $field = implode (&#39;,&#39;, $fielddata);
        $value = implode (&#39;,&#39;, $valuedata);

        $cmd = $replace ? &#39;REPLACE INTO&#39; : &#39;INSERT INTO&#39;;
        $sql = $cmd.&#39; `&#39;.$this->config[&#39;database&#39;].&#39;`.`&#39;.$table.&#39;`(&#39;.$field.&#39;) VALUES (&#39;.$value.&#39;)&#39;;
        $return = $this->execute($sql);
        return $return_insert_id ? $this->insert_id() : $return;
    }

    /**
     * 获取最后一次添加记录的主键号
     * @return int
     */
    public function insert_id() {
        return mysqli_insert_id($this->link);
    }

    /**
     * 执行更新记录操作
     * @param $data         要更新的数据内容，参数可以为数组也可以为字符串，建议数组。
     *                      为数组时数组key为字段值，数组值为数据取值
     *                      为字符串时[例：`name`=&#39;phpcms&#39;,`hits`=`hits`+1]。
     *                      为数组时[例: array(&#39;name&#39;=>&#39;phpcms&#39;,&#39;password&#39;=>&#39;123456&#39;)]
     *                      数组可使用array(&#39;name&#39;=>&#39;+=1&#39;, &#39;base&#39;=>&#39;-=1&#39;);程序会自动解析为`name` = `name` + 1, `base` = `base` - 1
     * @param $table        数据表
     * @param $where        更新数据时的条件
     * @return boolean
     */
    public function update($data, $table, $where = &#39;&#39;) {
        if($table == &#39;&#39; or $where == &#39;&#39;) {
            return false;
        }

        $where = &#39; WHERE &#39;.$where;
        $field = &#39;&#39;;
        if(is_string($data) && $data != &#39;&#39;) {
            $field = $data;
        } elseif (is_array($data) && count($data) > 0) {
            $fields = array();
            foreach($data as $k=>$v) {
                switch (substr($v, 0, 2)) {
                    case &#39;+=&#39;:
                        $v = substr($v,2);
                        if (is_numeric($v)) {
                            $fields[] = $this->add_special_char($k).&#39;=&#39;.$this->add_special_char($k).&#39;+&#39;.$this->escape_string($v, &#39;&#39;, false);
                        } else {
                            continue;
                        }

                        break;
                    case &#39;-=&#39;:
                        $v = substr($v,2);
                        if (is_numeric($v)) {
                            $fields[] = $this->add_special_char($k).&#39;=&#39;.$this->add_special_char($k).&#39;-&#39;.$this->escape_string($v, &#39;&#39;, false);
                        } else {
                            continue;
                        }
                        break;
                    default:
                        $fields[] = $this->add_special_char($k).&#39;=&#39;.$this->escape_string($v);
                }
            }
            $field = implode(&#39;,&#39;, $fields);
        } else {
            return false;
        }

        $sql = &#39;UPDATE `&#39;.$this->config[&#39;database&#39;].&#39;`.`&#39;.$table.&#39;` SET &#39;.$field.$where;
        return $this->execute($sql);
    }

    /**
     * 执行删除记录操作
     * @param $table        数据表
     * @param $where        删除数据条件,不充许为空。
     *                      如果要清空表，使用empty方法
     * @return boolean
     */
    public function delete($table, $where) {
        if ($table == &#39;&#39; || $where == &#39;&#39;) {
            return false;
        }
        $where = &#39; WHERE &#39;.$where;
        $sql = &#39;DELETE FROM `&#39;.$this->config[&#39;database&#39;].&#39;`.`&#39;.$table.&#39;`&#39;.$where;
        return $this->execute($sql);
    }

    /**
     * 获取最后数据库操作影响到的条数
     * @return int
     */
    public function affected_rows() {
        return mysql_affected_rows($this->link);
    }

    /**
     * 获取数据表主键
     * @param $table        数据表
     * @return array
     */
    public function get_primary($table) {
        $this->execute("SHOW COLUMNS FROM $table");
        while($r = $this->fetch_next()) {
            if($r[&#39;Key&#39;] == &#39;PRI&#39;) break;
        }
        return $r[&#39;Field&#39;];
    }

    /**
     * 获取表字段
     * @param $table        数据表
     * @return array
     */
    public function get_fields($table) {
        $fields = array();
        $this->execute("SHOW COLUMNS FROM $table");
        while($r = $this->fetch_next()) {
            $fields[$r[&#39;Field&#39;]] = $r[&#39;Type&#39;];
        }
        return $fields;
    }

    /**
     * 检查不存在的字段
     * @param $table 表名
     * @return array
     */
    public function check_fields($table, $array) {
        $fields = $this->get_fields($table);
        $nofields = array();
        foreach($array as $v) {
            if(!array_key_exists($v, $fields)) {
                $nofields[] = $v;
            }
        }
        return $nofields;
    }

    /**
     * 检查表是否存在
     * @param $table 表名
     * @return boolean
     */
    public function table_exists($table) {
        $tables = $this->list_tables();
        return in_array($table, $tables) ? 1 : 0;
    }

    public function list_tables() {
        $tables = array();
        $this->execute("SHOW TABLES");
        while($r = $this->fetch_next()) {
            $tables[] = $r[&#39;Tables_in_&#39;.$this->config[&#39;database&#39;]];
        }
        return $tables;
    }

    /**
     * 检查字段是否存在
     * @param $table 表名
     * @return boolean
     */
    public function field_exists($table, $field) {
        $fields = $this->get_fields($table);
        return array_key_exists($field, $fields);
    }

    public function num_rows($sql) {
        $this->lastqueryid = $this->execute($sql);
        return mysqli_num_rows($this->lastqueryid);
    }

    public function num_fields($sql) {
        $this->lastqueryid = $this->execute($sql);
        return mysqli_num_fields($this->lastqueryid);
    }

    public function result($sql, $row) {
        $this->lastqueryid = $this->execute($sql);
        return @mysql_result($this->lastqueryid, $row);
    }

    public function error() {
        return @mysqli_error($this->link);
    }

    public function errno() {
        return intval(@mysqli_errno($this->link)) ;
    }

    public function version() {
        if(!is_resource($this->link)) {
            $this->connect();
        }
        return mysqli_get_server_info($this->link);
    }

    public function close() {
        if (is_resource($this->link)) {
            @mysqli_close($this->link);
        }
    }

    public function halt($message = &#39;&#39;, $sql = &#39;&#39;) {
        if($this->config[&#39;debug&#39;]) {
            $this->errormsg = "<b>MySQL Query : </b> $sql <br /><b> MySQL Error : </b>".$this->error()." <br /> <b>MySQL Errno : </b>".$this->errno()." <br /><b> Message : </b> $message <br /><a href=&#39;http://faq.phpcms.cn/?errno=".$this->errno()."&msg=".urlencode($this->error())."&#39; target=&#39;_blank&#39; style=&#39;color:red&#39;>Need Help?</a>";
            $msg = $this->errormsg;
            echo &#39;<div style="font-size:12px;text-align:left; border:1px solid #9cc9e0; padding:1px 4px;color:#000000;font-family:Arial, Helvetica,sans-serif;"><span>&#39;.$msg.&#39;</span></div>&#39;;
            exit;
        } else {
            return false;
        }
    }

    /**
     * 对字段两边加反引号，以保证数据库安全
     * @param $value 数组值
     */
    public function add_special_char(&$value) {
        if(&#39;*&#39; == $value || false !== strpos($value, &#39;(&#39;) || false !== strpos($value, &#39;.&#39;) || false !== strpos ( $value, &#39;`&#39;)) {
            //不处理包含* 或者 使用了sql方法。
        } else {
            $value = &#39;`&#39;.trim($value).&#39;`&#39;;
        }
        if (preg_match("/\b(select|insert|update|delete)\b/i", $value)) {
            $value = preg_replace("/\b(select|insert|update|delete)\b/i", &#39;&#39;, $value);
        }
        return $value;
    }

    /**
     * 对字段值两边加引号，以保证数据库安全
     * @param $value 数组值
     * @param $key 数组key
     * @param $quotation
     */
    public function escape_string(&$value, $key=&#39;&#39;, $quotation = 1) {
        if ($quotation) {
            $q = &#39;\&#39;&#39;;
        } else {
            $q = &#39;&#39;;
        }
        $value = $q.$value.$q;
        return $value;
    }
}
// here&#39;s a rough replacement using mysqli:
// 错略的使用mysqli替换
if(!function_exists(&#39;mysql_result&#39;)) {
    function mysql_result($result, $number, $field=0) {
        mysqli_data_seek($result, $number);
        $row = mysqli_fetch_array($result);
        return $row[$field];
    }
}
?>