PHP Mysqli Class收集

轉自:http://www.aplweb.co.uk/blog/php/mysqli-wrapper-class/

    /**
    * Make an array of references to the values of another array
    * Note: useful when references rather than values are required
    * @param {array} array of values 
    * @return {array} references array
    */
    function makeRefArr(&$arr) {
        $refs = array();
 
        foreach($arr as $key => &$val) {
            $refs[$key] = &$val;
        }
 
        return $refs;
    }
 
    /**
    * Make a recursive copy of an array
    * @param {array} original array
    * @param {boolean} should the values to be cloned too?
    * @return {array} copy of source array
    */
    function array_copy($arr, $deep= true) {
        $newArr = array();
 
        if ($deep) {
            foreach ($arr as $key=>$val) {
                if (is_object($val)) {
                    $newArr[$key] = clone($val);
                } else if (is_array($val)) {
                    $newArr[$key] = array_copy($val);
                } else {
                    $newArr[$key] = $val;
                }
            }
        } else {
            foreach ($arr as $key=>$val) {
                $newArr[$key] = $val;
            }
        }
 
        return $newArr;
    }
 
    /**
    * A mysqli wrapper class
    *
    * @author Andrew Lowndes (APL Web)
    * @date 20/11/2010
    */
    class db {
        public static $db = null;
 
        //connect to the database
        public static function connect() {
            self::$db = new mysqli('localhost', 'username' ,'password', 'database');
 
            if (mysqli_connect_errno()) {
                throw new Exception('Connection failed: ' . mysqli_connect_error());
            }
 
            self::$db->set_charset("utf8");
        }
 
        //close the connection
        public static function close() {
            if (self::$db) {
                self::$db->close();
            }
        }
 
        /**
        * Run a query and return the result
        * @param {string} query to run (with '?' for values)
        * @param {array} values to execute in prepared statement (optional)
        * @return {resource} result
        */
        public static function query($query, $objs = array()) {
            if (!self::$db) self::connect();
 
            $objs = (array)$objs; //automagically cast single values into an array
 
            $statement = self::$db->prepare($query);
 
            if (!$statement) {
                throw new Exception('Query failed: ' . self::$db->error);
            }
 
            //go through all of the provided objects and bind them
            $types = array();
            $values = array();
 
            if (count($objs)>0) {
                foreach ($objs as $obj) {
                    //get the object type and translate it ready for bind parameter
                    $type = gettype($obj);
 
                    switch ($type) {
                        case 'boolean': case 'integer':
                            $types[] = 'i';
                            $values[] = intval($obj);
                            break;
                        case 'double':
                            $types[] = 'd';
                            $values[] = doubleval($obj);
                            break;
                        case 'string':
                            $types[] = 's';
                            $values[] = (string)$obj;
                            break;
                        case 'array': case 'object':
                            $paramTypes[] = 's';
                            $values[] = json_encode($obj);
                            break;
                        case 'resource': case 'null': case 'unknown type': default:
                            throw new Exception('Unsupported object passed through as query prepared object!');
                    }
                }
 
                $params = makeRefArr($values);
                array_unshift($params, implode('', $types));
                call_user_func_array(array($statement, 'bind_param'), $params);
            }
 
            if (!$statement->execute()) {
                return null;
            } else {
                $statement->store_result();
                return $statement;
            }
        }
 
        /**
        * Determine if an object exists
        * @param {string} query to run
        * @param {array} objects to use in prepare query (optional)
        * @return {boolean} object exists in database
        */
        public static function objectExists($query, $objs = array()) {
            $statement = self::query($query, $objs);
 
            return (is_object($statement) && $statement->num_rows>0);
        }
 
        /**
        * Make an associative array of field names from a statement
        * @param {resource} mysqli statement
        * @return {array} field names array
        */
        private static function getFieldNames($statement) {
            $result = $statement->result_metadata();
            $fields = $result->fetch_fields();
 
            $fieldNames = array();
            foreach($fields as $field) {
                $fieldNames[$field->name] = null;
            }
 
            return $fieldNames;
        }
 
        /**
        * Get an object from a query
        * @param {string} query to execute
        * @param {array} objects to use as the values (optional) 
        * @return {assoc} sinulatobject
        */
        public static function getObject($query, $objs = array()) {
            $statement = self::query($query, $objs);
 
            if (!is_object($statement) || $statement->num_rows<1) {
                return null;
            }
 
            $fieldNames = self::getFieldNames($statement);
            call_user_func_array(array($statement, 'bind_result'), makeRefArr($fieldNames));
 
            $statement->fetch();
            $statement->close();
 
            return $fieldNames;
        }
 
        /**
        * Get a list of objects from the database
        * @param {string} query
        * @return {array} objects
        */
        public static function getObjects($query, $objs = array()) {
            $statement = self::query($query, $objs);
 
            if (!is_object($statement) || $statement->num_rows<1) {
                return array();
            }
 
            $fieldNames = self::getFieldNames($statement);
            call_user_func_array(array($statement, 'bind_result'), makeRefArr($fieldNames));
 
            $results = array();
            while ($statement->fetch()) {
                $results[] = array_copy($fieldNames);
            }
 
            $statement->close();
 
            return $results;
        }
 
        /**
        * Get all of the data from a table
        * @param {string} table name
        * @return {array} table data
        */
        public static function getTable($tableName) {
            if (!self::$db) self::connect();
 
            $tableName = self::$db->escape_string($tableName);
 
            return self::getObjects('SELECT * FROM `' . $tableName . '`;');
        }
 
        /**
        * Get a field from a table based on a field having a specific value
        * @param {string} table name
        * @param {string} field name
        * @param {mixed} field value
        * @return {array} table row data
        */
        public static function getTableRow($tableName, $field, $value) {
            if (!self::$db) self::connect();
 
            $tableName = self::$db->escape_string($tableName);
            $field = self::$db->escape_string($field);
 
            return self::getObject('SELECT * FROM `' . $tableName . '` WHERE `' . $field . '` = ? LIMIT 1;', $value);
        }
 
        /**
        * Get all related rows from a table based on a field having a specific value
        * @param {string} table name
        * @param {string} field name
        * @param {mixed} field value
        * @return {array} table row data
        */
        public static function getTableRows($tableName, $field, $value, $sortField = null, $sortDesc = false) {
            if (!self::$db) self::connect();
 
            $tableName = self::$db->escape_string($tableName);
            $field = self::$db->escape_string($field);
 
            if ($sortField == null) {
                $sortField = $field;
            } else {
                $sortField = self::$db->escape_string($sortField);
            }
 
            return self::getObjects('SELECT * FROM `' . $tableName . '` WHERE `' . $field . '` = ? ORDER BY `' . $sortField . '` ' . ($sortDesc ? 'DESC' : 'ASC') . ';', $value);
        }
    }

轉自:http://www.nngcl.com/a/mysqlli/2011/0404/404.html

 

/**********************************
*     作者：streen003
*    来自：www.bc263.com
*    邮箱：streen003@gmail.com
*
********************************/

class Db_mysql {
        
        //定义变量
        private $host;
        private $user;
        private $pwd;
        private $dbname;
        protected $charset;
        
        public $db_link;
        public $result;
        public $rows;
        public $myrow;

        public $sql_version; //mysql 版本
        
        //构造函数，用来初始化Mysql Server 连接。
        public function __construct($params) {
               
                $this->host = $params['host'];
                $this->user = $params['username'];
                $this->pwd = $params['password'];
                $this->dbname = $params['dbname'];
                $this->charset = $params['charset'];
               
                if (is_array($params)) {
                        
                        if ($this->db_connect($this->host,$this->user,$this->pwd)) {
                                
                                if ($this->db_select($this->dbname)) {
                                        $this->query("SET NAMES {$this->charset}");
                                        $this->get_sever_info();
                                       
                                        if (version_compare($this->sql_version,'5.0.2','>=')) {
                                                $this->query("SET SESSION SQL_MODE=''");
                                        }
                                }
                                else {
                                       

                                      $this->halt('Connet databa
'.$params['dbname'].' unsuccess or database is not exists');
                                }
                        }
                        else {
                                
                                $this->halt('Connet Mysql server unsuccess please check your config args');
                        }
                }
                else {
                        
                        $this->halt('Class Db_mysql() args is error');
                }
        }
        
        //连接Mysql Server.
        public function db_connect($host,$user,$pwd) {
               
                if($this->db_link) {
                        
                        return false; //当Mysql server已连接时，程序不执行，返回false.
                }
                else {
                        
                        $this->db_link = mysqli_connect($host,$user,$pwd);
                        return $this->db_link;
                }
        }
        
        //连接数据库
        public function db_select($dbname) {
               
                return mysqli_select_db($this->db_link,$dbname);
        }
        
        //执行SQL语句
        public function query($sql,$info=false) {
               
                //当SQL参数不为空且Mysql Server连接成功时执行SQL语句.
                if (!empty($sql)&&isset($this->db_link)){
                        
                        //当$this->result 存在时，应清除,以免影响后面赋值.
                        if ($this->result) {
                                unset($this->result);
                        }
                        

                      $this->result = ($info==true) ?
mysqli_unbuffered_query($this->db_link,$sql) :
mysqli_query($this->db_link,$sql);
                        
                        if ($this->result){
                                
                                return $this->result;
                        }
                        else {
                                $this->halt('SQL query unsuccess');
                                return false;
                        }
                }
                else {
                        return false;
                }
        }
        
        //获取Mysql Server信息
        public function get_sever_info() {
               
                if ($this->db_link) {
                        $this->sql_version = mysqli_get_server_info($this->db_link);
                        return $this->sql_version;
                }
                else {
                        return false;
                }
        }
        
        //获取Mysql 错误信息.
        public function sql_error() {
               
                //当Mysql Server连接不成功时.
                if (!$this->db_link) {
                        return array(
                        'message'=>mysqli_connect_error(),
                        'code'=>mysqli_connect_errno(),
                        );
                }
               
                return array(
                'message'=>@mysqli_error(),
                'code'=>@mysqli_error(),
                );
               
        }
        
        //从执行结果中获取一行信息，字段型的.
        public function fetch_row($sql) {
               
                if ($this->rows) {
                        
                        unset($this->rows);
                }
               
                $this->query($sql);
               
                $this->rows = mysqli_fetch_assoc($this->result);
               
                return $this->rows;
        }
        
        //从执行结果中获取一行信息，字段型的,数字索引全有.
        public function fetch_array($sql) {
               
                if ($this->rows) {
                        
                        unset($this->rows);
                }
               
                $this->query($sql);
               
                $this->rows = mysqli_fetch_array($this->result);
               
                return $this->rows;
        }
        
        //从执行结果获取全部信息，字段型的，输出类型为数组.
        public function get_array($sql) {
               
                if ($this->myrow) {
                        
                        unset($this->myrow);
                }
               
                $this->query($sql);
               
                $myrow = array();
                while ($row=mysqli_fetch_row($this->result)) {
                        
                        $myrow[] = $row;
                }
                $this->free($this->result);
               
                $this->myrow = $myrow;
               
                return $this->myrow;
        }
        
        //从执行结果获取全部信息，字段型的，数字型的全有，输出类型为数组.
        public function get_all($sql) {
               
                if ($this->myrow) {
                        
                        unset($this->myrow);
                }
               
                $this->query($sql);
               
                $myrow = array();
                while ($row=mysqli_fetch_array($this->result)) {
                        
                        $myrow[] = $row;
                }
                $this->free($this->result);
               
                $this->myrow = $myrow;
               
                return $this->myrow;
        }
        
        //释放内存
        public function free($result) {
               
                return mysqli_free_result($result);
        }
        
        //获取执行结果的总行数.
        public function num_rows ($result) {
               
                return mysqli_num_rows($result);
        }
        
        //关闭Mysql Server
        public function db_close($db_link) {
               
                if ($db_link) {
                        return mysqli_close($db_link);
                }
                else {
                        return false;
                }
        }
        
        //错误信息提示页面
        public function halt($message) {
               
                //错误页面TOP
                $page_top = <<<EOT
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Mysql_error</title>
</head>

<body>
<div id="div">
<div id="div02">
<div id="div03"> Mysql error</div>
<div id="div04">
EOT;

                //错误页面底部
                $page_bottom = <<<EOT
</div>
<div id="div05"></div>
<div id="div06"> © 版权所有 2009 www.bc263.com. All rights reserved.</div>
</div>
</div>
</body>
</html>
EOT;
        $error_msg = $this->sql_error();


              $page_content = 'Mysql Error:
'.$message.'<br/><br/>Mysql error description :
'.$error_msg['message'].'<br/>Mysql error code:
'.$error_msg['code'].'<br/><br/>age url: http://'.$_SERVER['SERVER_NAME'].$_SERVER['SCRIPT_NAME'];
               
                echo $page_top.$page_content.$page_bottom;
               
        }
        
        //析构函数，用来完成Db_mysql Class执行后的清理战场工作.
        public function __destruct(){
               
                $this->db_close($this->db_link);
               
        }
        
        //__Call()函数，用于解决类外调用不存在的方法时的说明
        public function __call($method, array $args) {
               
                $msg ='Db_mysql Class has not method '.$method;
               
                $this->halt($msg);
        }
}

轉自:http://www.admpub.com/post-79.html

class db {
    /**
     * *错误编号
     */
    public static $is_error = false;
    /**
     * *当执行出错时是否中断
     */
    public static $OnErrorStop = false;
    /**
     * *当执行出错时是否提示错误信息
     */
    public static $OnErrorShow = true;
    /**
     * *当前查询SQL语句
     */
    protected static $sql = '';
    /**
     * *mysqli 对象
     */
    protected static $mysqli = null;
    /**
     * *当前结果集
     */
    protected static $result = false;
    /**
     * *查询统计次数
     */
    protected static $query_count = 0;
    /**
     * *当前查询是否开户了事物处理
     */
    protected static $is_commit = false;
 
    /**
     * *执行查询
     * @param  $sql [string] :SQL查询语句
     * @return 成功赋值并返回self::$result; 失败返回 false 如果有事务则回滚
     */
    public static function query($sql) {
        self :: connect();
        self :: $sql = $sql;
        self :: $result = self :: $mysqli -> query($sql);
        if (self :: $mysqli -> error) {
            $error = sprintf("SQL Query Error: %s\r\n", self :: $mysqli -> error);
            self :: $is_error = true;
            self :: log($error);
            if (self :: $OnErrorStop) exit;
            return false;
        } else {
            self :: $query_count++;
        } 
        return self :: $result;
    } 
    /**
     * *查询指定SQl 第一行，第一列 值
     * @param  $sql [string] :SQL查询语句
     * @return 失败返回 false
     */
    public static function data_scalar($sql) {
        if (self :: $result = self :: query($sql)) {
            return self :: fetch_scalar();
        } else {
            return false;
        } 
    } 
    /**
     * *查询指定SQl 第一行记录
     * @param  $sql [string] :SQL查询语句
     * @param  $assoc [bool] :true 返回数组; false 返回stdClass对象;默认 false
     * @return 失败返回 false
     */
    public static function data_row($sql, $assoc = false) {
        if (self :: $result = self :: query($sql)) {
            return self :: fetch_row(self :: $result, $assoc);
        } else {
            return false;
        } 
    } 
    /**
     * *查询指定SQl 所有记录
     * @param  $sql [string] :SQL查询语句
     * @param  $key_field [string] :指定记录结果键值使用哪个字段,默认为 false 使用 regI{0...count}
     * @param  $assoc [bool] :true 返回数组; false 返回stdClass对象;默认 false
     * @return 失败返回 false
     */
    public static function data_table($sql, $key_field = false, $assoc = false) {
        if (self :: $result = self :: query($sql)) {
            return self :: fetch_all($key_field, $assoc);
        } else {
            return false;
        } 
    } 
    /**
     * *取结果(self::$result)中第一行，第一列值
     * @return 没有结果返回 false
     */
    public static function fetch_scalar() {
        if (!empty(self :: $result)) {
            $row = self :: $result -> fetch_array();
            return $row[0];
        } else {
            return false;
        } 
    } 
    /**
     * *取结果$result中第一行记录
     * @param  $result [object] :查询结果数据集
     * @param  $assoc [bool] :true 返回数组; false 返回stdClass对象;默认 false
     * @return 没有结果返回 false
     */
    public static function fetch_row($result = null , $assoc = false) {
        if ($result == null) $result = self :: $result;
        if (empty($result)) {
            return false;
        } 
        if ($assoc) {
            return $result -> fetch_assoc();
        } else {
            return $result -> fetch_object();
        } 
    } 
    /**
     * *取结果(self::$result)中所有记录
     * @param  $key_field [string] :指定记录结果键值使用哪个字段,默认为 false 则使用 regI{0...count}
     * @param  $assoc [bool] :true 返回数组; false 返回stdClass对象;默认 false
     * @return 没有结果返回 false
     */
    public static function fetch_all($key_field = false, $assoc = false) {
        $rows = ($assoc) ? array() : new stdClass;
        $regI = -1;
        while ($row = self :: fetch_row(self :: $result, $assoc)) {
            if ($key_field != false) {
                $regI = ($assoc) ? $row[$key_field] : $row -> $key_field;
            } else {
                $regI++;
            } 
            if ($assoc) {
                $rows[$regI] = $row;
            } else {
                $rows -> {
                    $regI} = $row;
            } 
        } 
        self :: free_result();
        return ($regI > -1) ? $rows : false;
    } 
    /**
     * 执行更新数据操作
     * @param  $table [string] 数据库表名称
     * @param  $data [array|stdClass] 待更新的数据
     * @param  $where [string] 更新条件
     * @return 成功 true; 失败 false
     */
    public static function update($table, $data, $where) {
        $set = '';
        if (is_object($data) || is_array($data)) {
            foreach ($data as $k => $v) {
                self :: format_value($v);
                $set .= empty($set) ? ("`{$k}` = {$v}") : (", `{$k}` = {$v}");
            } 
        } else {
            $set = $data;
        } 
        return self :: query("UPDATE `{$table}` SET {$set} WHERE {$where}");
    } 
    /**
     * 执行插入数据操作
     * @param  $table [string] 数据库表名称
     * @param  $data [array|stdClass] 待更新的数据
     * @param  $fields [string] 数据库字段，默认为 null。 为空时取 $data的 keys
     * @return 成功 true; 失败 false
     */
    public static function insert($table, $data, $fields = null) {
        if ($fields == null) {
            foreach($data as $v) {
                if (is_array($v)) {
                    $fields = array_keys($v);
                } elseif (is_object($v)) {
                    foreach($v as $k2 => $v2) {
                        $fields[] = $k2;
                    } 
                } elseif (is_array($data)) {
                    $fields = array_keys($data);
                } elseif (is_object($data)) {
                    foreach($data as $k2 => $v2) {
                        $fields[] = $k2;
                    } 
                } 
                break;
            } 
        } 
        $_fields = '`' . implode('`, `', $fields) . '`';
        $_data = self :: format_insert_data($data);
        return self :: query("INSERT INTO `{$table}` ({$_fields}) VALUES {$_data}");
    } 
    /**
     * *格式化插入数据
     * @param  $data [array|stdClass] 待格式化的插入数据
     * @return insert 中 values 后的 SQL格式
     */
    protected static function format_insert_data($data) {
        $output = '';
        $is_list = false;
        foreach ($data as $value) {
            if (is_object($value) || is_array($value)) {
                $is_list = true;
                $tmp = '';
                foreach ($value as $v) {
                    self :: format_value($v);
                    $tmp .= !empty($tmp) ? ", {$v}" : $v;
                } 
                $tmp = "(" . $tmp . ")";
                $output .= !empty($output) ? ", {$tmp}" : $tmp;
                unset($tmp);
            } else {
                self :: format_value($value);
                $output .= !empty($output) ? ", {$value}" : $value;
            } 
        } 
        if (!$is_list) $output = '(' . $output . ')';
        return $output;
    } 
    /**
     * *格式化值
     * @param  $ &$value [string] 待格式化的字符串,格式成可被数据库接受的格式
     */
    protected static function format_value(&$value) {
        $value = trim($value);
        if ($value === null || $value == '') {
            $value = 'NULL';
        } elseif (preg_match('/\[\w+\]\.\(.*?\)/', $value)) { // mysql函数 格式:[UNHEX].(参数);
            $value = preg_replace('/\[(\w+)\]\.\((.*?)\)/', "$1($2)", $value);
        } else {
            // $value = "'" . addslashes(stripslashes($value)) ."'";strip
            $value = "'" . addslashes(stripslashes($value)) . "'";
        } 
    } 
    /**
     * *返回最后一次插入的ID
     */
    public static function insert_id() {
        return self :: $mysqli -> insert_id;
    } 
    /**
     * *返回结果集数量
     * @param  $result [数据集]
     */
    public static function num_rows($result = null) {
        if (is_null($result)) $result = self :: $result;
        return mysqli_num_rows($result);
    } 
    /**
     * *统计表记录
     * @param  $table [string] 数据库表名称
     * @param  $where [string] SQL统计条件,默认为 1 查询整个表
     */
    public static function total($table, $where = '1') {
        $sql = "SELECT count(*) FROM {$table} WHERE {$where}";
        self :: query($sql);
        return self :: fetch_scalar();
    } 
    /**
     * *返回当前查询SQl语句
     */
    public static function get_sql() {
        return self :: $sql;
    } 
    /**
     * *返回当前查询影响的记录数
     */
    public static function get_nums() {
        return self :: $result -> num_rows;
    } 
    /**
     * *开始事物处理,关闭MYSQL的自动提交模式
     */
    public static function commit_begin() {
        self :: connect();
        self :: $is_error = false;
        self :: $mysqli -> autocommit(false); //使用事物处理,不自动提交
        self :: $is_commit = true;
    } 
    /**
     * *提交事物处理
     */
    public static function commit_end() {
        if (self :: $is_commit) {
            self :: $mysqli -> commit();
        } 
        self :: $mysqli -> autocommit(true); //不使用事物处理,开启MYSQL的自动提交模式
        self :: $is_commit = false;
        self :: $is_error = false;
    } 
    /**
     * *回滚事物处理
     */
    public static function rollback() {
        self :: $mysqli -> rollback();
    } 
    /**
     * *释放数据集
     */
    public static function free_result($result = null) {
        if (is_null($result)) $result = self :: $result;
        @mysqli_free_result($result);
    } 
    /**
     * *选择数据库
     * @param  $dbname [string] 数据库名称
     */
    public static function select_db($dbname) {
        self :: connect();
        return self :: $mysqli -> select_db($dbname);
    } 
    /**
     * *连接Mysql
     */
    protected static function connect() {
        if (is_null(self :: $mysqli)) {
            self :: $mysqli = new mysqli($GLOBALS['database']['db_host'],
                $GLOBALS['database']['db_user'],
                $GLOBALS['database']['db_pass'],
                $GLOBALS['database']['db_name'],
                $GLOBALS['database']['db_port']);
            if (mysqli_connect_errno()) {
                $error = sprintf("Database Connect failed: %s\r\n", mysqli_connect_error());
                self :: log($error);
                exit;
            } else {
                self :: $mysqli -> query("SET character_set_connection=" . $GLOBALS['database']['db_charset'] . ", character_set_results=" . $GLOBALS['database']['db_charset'] . ", character_set_client=binary");
            } 
        } 
    } 
    /**
     * *日志处理
     * @param  $message [string] 产生的日志消息
     */
    protected static function log($message) {
        if (self :: $OnErrorShow) {
            echo sys :: error_format($message . '<div style="color:#000;background-color: #ffffe1;padding:4px;">' . self :: $sql . '</div>', -1);
        } else {
            sys :: log($message . '<div style="color:#000;background-color: #ffffe1;padding:4px;">' . self :: $sql . '</div>', -1, __FILE__);
        } 
        if(self :: $OnErrorStop) {
            exit;
        }
    } 
} 
class sys {
    public static error_format($error_info, $jump_url = 0) {
        return $error_info;
    } 
    public static log($error_info, $jump_url = 0,$file=null) {
        echo $error_info,' file:',$file;
    } 
}