时间：2021-07-01 10:21:17 帮助过：8人阅读
/** * 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 = <<
?自: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'],
&