phpcms mysql版本_phpcmsv9 数据库操作类mysqli支持php7版本

/**

* 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['autoconnect'] == 1) {

$this->connect();

}

}

/**

* 真正开启数据库连接

*

* @return void

*/

public function connect() {

//var_dump($this->config);

if(!$this->link = @new mysqli($this->config['hostname'], $this->config['username'],$this->config['password'], $this->config['database'], $this->config['dbport'])){

$this->halt('Can not connect to MySQL server');

return false;

}

if ($this->link->connect_errno) {

$this->halt("Connect failed: %s\n", $this->link->connect_error);

exit();

}

switch ($this->config['charset']){

case 'utf8':

$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=''";

break;

case 'GBK':

$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=''";

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['database'];

return $this->link;

}

/**

* 数据库查询执行方法

* @param $sql 要执行的sql语句

* @return 查询资源句柄

*/

private function execute($sql) {

//echo "
".$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`='$name']

* @param $limit 返回结果范围[例：10或10,10 默认为空]

* @param $order 排序方式 [默认按数据库默认方式排序]

* @param $group 分组方式 [默认为空]

* @param $key 返回数组按键名排序

* @return array 查询结果集数组

*/

public function select($data, $table, $where = '', $limit = '', $order = '', $group ='', $key = '') {

$where = $where == '' ? '' : ' WHERE '.$where;

$order = $order == '' ? '' : ' ORDER BY '.$order;

$group = $group == '' ? '' : ' GROUP BY '.$group;

$limit = $limit == '' ? '' : ' LIMIT '.$limit;

$field = explode(',', $data);

array_walk($field, array($this, 'add_special_char'));

$data = implode(',', $field);

$sql = 'SELECT '.$data.' FROM `'.$this->config['database'].'`.`'.$table.'`'.$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 = '', $order = '', $group = '') {

$where = $where == '' ? '' : ' WHERE '.$where;

$order = $order == '' ? '' : ' ORDER BY '.$order;

$group = $group == '' ? '' : ' GROUP BY '.$group;

$limit = ' LIMIT 1';

$field = explode( ',', $data);

array_walk($field, array($this, 'add_special_char'));

$data = implode(',', $field);

$sql = 'SELECT '.$data.' FROM `'.$this->config['database'].'`.`'.$table.'`'.$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 "
".$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 == '' || count($data) == 0) {

return false;

}

$fielddata = array_keys($data);

$valuedata = array_values($data);

array_walk($fielddata, array($this, 'add_special_char'));

array_walk($valuedata, array($this, 'escape_string'));

$field = implode (',', $fielddata);

$value = implode (',', $valuedata);

$cmd = $replace ? 'REPLACE INTO' : 'INSERT INTO';

$sql = $cmd.' `'.$this->config['database'].'`.`'.$table.'`('.$field.') VALUES ('.$value.')';

$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`='phpcms',`hits`=`hits`+1]。

* 为数组时[例: array('name'=>'phpcms','password'=>'123456')]

* 数组可使用array('name'=>'+=1', 'base'=>'-=1');程序会自动解析为`name` = `name` + 1, `base` = `base` - 1

* @param $table 数据表

* @param $where 更新数据时的条件

* @return boolean

*/

public function update($data, $table, $where = '') {

if($table == '' or $where == '') {

return false;

}

$where = ' WHERE '.$where;

$field = '';

if(is_string($data) && $data != '') {

$field = $data;

} elseif (is_array($data) && count($data) > 0) {

$fields = array();

foreach($data as $k=>$v) {

switch (substr($v, 0, 2)) {

case '+=':

$v = substr($v,2);

if (is_numeric($v)) {

$fields[] = $this->add_special_char($k).'='.$this->add_special_char($k).'+'.$this->escape_string($v, '', false);

} else {

continue;

}

break;

case '-=':

$v = substr($v,2);

if (is_numeric($v)) {

$fields[] = $this->add_special_char($k).'='.$this->add_special_char($k).'-'.$this->escape_string($v, '', false);

} else {

continue;

}

break;

default:

$fields[] = $this->add_special_char($k).'='.$this->escape_string($v);

}

}

$field = implode(',', $fields);

} else {

return false;

}

$sql = 'UPDATE `'.$this->config['database'].'`.`'.$table.'` SET '.$field.$where;

return $this->execute($sql);

}

/**

* 执行删除记录操作

* @param $table 数据表

* @param $where 删除数据条件,不充许为空。

* 如果要清空表，使用empty方法

* @return boolean

*/

public function delete($table, $where) {

if ($table == '' || $where == '') {

return false;

}

$where = ' WHERE '.$where;

$sql = 'DELETE FROM `'.$this->config['database'].'`.`'.$table.'`'.$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['Key'] == 'PRI') break;

}

return $r['Field'];

}

/**

* 获取表字段

* @param $table 数据表

* @return array

*/

public function get_fields($table) {

$fields = array();

$this->execute("SHOW COLUMNS FROM $table");

while($r = $this->fetch_next()) {

$fields[$r['Field']] = $r['Type'];

}

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['Tables_in_'.$this->config['database']];

}

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 = '', $sql = '') {

if($this->config['debug']) {

$this->errormsg = "MySQL Query : $sql MySQL Error : ".$this->error()." MySQL Errno : ".$this->errno()." Message : $message Need Help?";

$msg = $this->errormsg;

echo '

'.$msg.'

';

exit;

} else {

return false;

}

}

/**

* 对字段两边加反引号，以保证数据库安全

* @param $value 数组值

*/

public function add_special_char(&$value) {

if('*' == $value || false !== strpos($value, '(') || false !== strpos($value, '.') || false !== strpos ( $value, '`')) {

//不处理包含* 或者 使用了sql方法。

} else {

$value = '`'.trim($value).'`';

}

if (preg_match("/\b(select|insert|update|delete)\b/i", $value)) {

$value = preg_replace("/\b(select|insert|update|delete)\b/i", '', $value);

}

return $value;

}

/**

* 对字段值两边加引号，以保证数据库安全

* @param $value 数组值

* @param $key 数组key

* @param $quotation

*/

public function escape_string(&$value, $key='', $quotation = 1) {

if ($quotation) {

$q = '\'';

} else {

$q = '';

}

$value = $q.$value.$q;

return $value;

}

}

// here's a rough replacement using mysqli:

// 错略的使用mysqli替换

if(!function_exists('mysql_result')) {

function mysql_result($result, $number, $field=0) {

mysqli_data_seek($result, $number);

$row = mysqli_fetch_array($result);

return $row[$field];

}

}

?>

本文原创发布php中文网，转载请注明出处，感谢您的尊重！