PHP Mysqli Class收集

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

                  <p class="sycode">                  /** * 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); } }                  </p>

?自: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 = nbsp;html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

 Mysql error

EOT;

                //错误页面底部
                $page_bottom = 

 © 版权所有 2009 www.bc263.com. All rights reserved.

EOT;
        $error_msg = $this->sql_error();


              $page_content = 'Mysql Error:
'.$message.'

Mysql error description :
'.$error_msg['message'].'
Mysql error code:
'.$error_msg['code'].'

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'],
             &