<?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['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 "<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`='$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 "<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 == '' || 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 = "<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>errno()."&msg=".urlencode($this->error())."' target='_blank' style='color:red'>Need Help?</a>";
$msg = $this->errormsg;
echo '<div><span>'.$msg.'</span></div>';
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免费学习视频：立即使用
踏上前端学习之旅，开启通往精通之路！从前端基础到项目实战，循序渐进，一步一个脚印，迈向巅峰！
