<?php
/**
* Mysql数据库操作类 v2.0
* 2009.1.22 by Aboc QQ:9986584
* 增加文件缓存
*
*/
class DbMysql {
/**
* 在数据库操作中,只对数据库操作有影响的字符做转义
* 当此类正常后,所有数据操作 @
*/
/*
* 数据库连接句柄
*/
private $_Db = NULL;
/*
* 是否持续连接 0.1
*/
private $_pconnect = 0;
/*
* 编码
*/
private $_charset = 'gbk';
/*
*最后一次插入的ID
*/
private $_lastId = 0;
/*
* 默认数据库配置
*/
private $_config = array ('dbhost' => 'localhost', 'dbuser' => 'root', 'dbpass' => 'root', 'dbname' => 'test');
/**
* 缓存路径
*/
private $_cachePath = '';
/**
* sql语句
*
* @var unknown_type
*/
private $_sql = '';
/**
* 初始连接数据库
*/
function __construct($config,$pconnect=0,$cachepath='cache') {
if (empty($config)) $config = array();
$this->checkConfig ( $config );
$this->_pconnect = $pconnect;
$this->connect ();
$this->query ( 'set names ' . $this->_charset ); //设置编码
$this->_cachePath = $cachepath;
}
/**
* 判断config变量
*
* @param unknown_type $config
*/
private function checkConfig($config) {
foreach ( $config as $key => $value ) {
$this->_config [$key] = empty ( $value ) ? $this->_config [$key] : $value;
}
//return $this->_config;
}
/*
* 连接数据库
*/
private function connect() {
// print_r($this->_config);
if ($this->_pconnect) {
$this->_Db = mysql_pconnect ( $this->_config ['dbhost'], $this->_config ['dbuser'], $this->_config ['dbpass'] ) or die ( '数据库连接失败' . mysql_errno () );
} else {
$this->_Db = mysql_connect ( $this->_config ['dbhost'], $this->_config ['dbuser'], $this->_config ['dbpass'] ) or die ( '数据库连接失败' . mysql_errno () );
}
if ($this->_Db != NULL) {
mysql_select_db ( $this->_config ['dbname'], $this->_Db ) or die ( '数据库' . $this->_config ['dbname'] . '不存在' );
}
}
/**
* 将变量的单引号或双引号转义
*
* @param unknown_type $string
*/
private function strtag($string1) {
if (is_array ( $string1 )) {
foreach ( $string1 as $key => $value ) {
$stringnew [$this->strtag ( $key )] = $this->strtag ( $value );
}
} else {
//在此做转义,对单引号
//TODO 好像 %也要转义吧?
//$string = iconv("gbk","gbk",$string);
$stringnew = mysql_real_escape_string ( $string1 );
// $stringnew = get_magic_quotes_gpc()?$string:addslashes ( $string1 );
// $stringnew=str_replace(array("'",'"'),array("\'",'\"'),$string1);
}
return $stringnew;
}
/**
* 将数组转化为SQL接受的条件样式
*
* @param unknown_type $array
*/
private function chageArray($array) {
//MYSQL支持insert into joincart set session_id = 'dddd',product_id='44',number='7',jointime='456465'
//所以更新和插入可以使用同一组数据
$array = $this->strtag ( $array ); //转义
$str = '';
foreach ( $array as $key => $value ) {
$str .= empty ( $str ) ? "`" . $key . "`='" . $value."'" : ", `" . $key . "`='" . $value."'";
}
return $str;
}
/**
* 执行查询语句
* @return bool
*/
public function query($sql) {
//echo $sql.'<br>';
$this->_sql = $sql;
if (! $result = mysql_query ( $sql, $this->_Db)) {
if(UC_DBUSER == 'root'){
echo $sql.'<br>'.mysql_error().'<br>';
//$this->createErrorLog($sql);
die ( '数据库出错' );
} else {
$subject = date("Y-m-d H:i:s")."数据库查询出错";
$thisurl = str::getThisUrl();
$error = mysql_error();
$content = <<<EOT
数据库查询出错,详细如下:<br />
$thisurl <br />
$error <br />
$sql
EOT;
$username = '风子';
if (function_exists('sendMail')){
sendMail('9986584@qq.com',$username,$subject,$content);
die(':( 数据库查询出错,已经通知管理员,请稍后重试');
}
}
} else {
return $result;
}
}
/**
* 插入记录
*
*/
public function insert($table, $array) {
if(!is_array($array))return false;
$array = $this->strtag ( $array ); //转义
$str = '';
$val = '';
foreach ($array as $key=>$value){
$str .= ($str != '')?",`$key`":"`$key`";
$val .= ($val != '')?",'$value'":"'$value'";
}
$sql = 'insert into `' . $table . '` ('.$str. ') values('.$val.')';
//echo $sql;exit;
if ($this->query ( $sql )) {
$this->lastId();
return $this->_lastId?$this->_lastId:true;
} else {
return false;
}
}
/**
* 替换并插入
* @param unknown_type $table
* @param unknown_type $array
*/
public function replaceInsert($table, $array) {
if(!is_array($array))return false;
$array = $this->strtag ( $array ); //转义
$str = '';
$val = '';
foreach ($array as $key=>$value){
$str .= ($str != '')?",`$key`":"`$key`";
$val .= ($val != '')?",'$value'":"'$value'";
}
$sql = 'replace into `' . $table . '` ('.$str. ') values('.$val.')';
if ($this->query ( $sql )) {
$this->lastId();
return $this->_lastId?$this->_lastId:true;
} else {
return false;
}
}
/**
* 批量插入记录
*
* @param $table 表名
* @param $batchArray 批量数据 ,二维数组,健名必需相同,否则不能插入
*/
public function insertBatch($table,$batchArray){
if(!is_array($batchArray))return false;
$str = '';
$val = '';
$vals = array();
foreach ($batchArray as $keys=>$row){
if(!is_array($row))return false;
foreach ($row as $key=>$value){
if($keys == 0)$str .= ($str != '')?",`$key`":"`$key`";
$val .= ($val != '')?",'$value'":"'$value'";
}
$vals[$keys] = '('.$val.')';
$val = '';
}
$vals = implode(',',$vals);
$sql = 'insert into `' . $table . '` ('.$str. ') values '.$vals;
if ($this->query ( $sql )) {
$this->lastId();
return $this->_lastId?$this->_lastId:true;
} else {
return false;
}
}
/**
* 更新记录
*
*/
public function update($table, $array, $where = NULL) {
if ($where == NULL) {
$sql = 'update `' . $table . '` set ' . $this->chageArray ( $array );
} else {
$sql = 'update `' . $table . '` set ' . $this->chageArray ( $array ) . ' where ' . $where;
}
if ($res = $this->query ( $sql )) {
return $res;
} else {
return false;
}
}
/**
* 删除记录
*
*/
public function delete($table, $where = NULL) {
if ($where == NULL) {
$sql = 'delete from `' . $table . '`';
} else {
$sql = 'delete from `' . $table . '` where ' . $where;
}
if ($this->query ( $sql )) {
return true;
} else {
return false;
}
}
/**
* 获取一条记录
*
*/
public function fetchRow($sql,$cacheTime=0,$cacheId='') {
if($content = $this->checkCache($sql,$cacheTime,$cacheId)){
return $content;
} else{
$reult = $this->query ( $sql );
$row = mysql_fetch_assoc ( $reult );
if(!empty($row)){
foreach ($row as $key=>$value){
$row[$key] = stripslashes($value);
}
}
if($cacheTime)$this->createCache($sql,$row,$cacheId);
return $row;
}
}
/**
* 获取所有记录/用的mysql_fetch_assoc循环
*
*/
public function fetchAll($sql,$cacheTime=0,$cacheId='') {
if($content = $this->checkCache($sql,$cacheTime,$cacheId)){
return $content;
} else{
$result = $this->query ( $sql );
if ($result !== false) {
$arr = array ();
while ( $row = mysql_fetch_assoc ( $result ) ) {
if(!empty($row)){
foreach ($row as $key=>$value){
$row[$key] = stripslashes($value);
}
}
$arr [] = $row;
}
if($cacheTime)$this->createCache($sql,$arr,$cacheId);
return $arr;
} else {
return array();
}
}
}
/**
* 获取最后一次影响的Id
*
*/
public function lastId() {
$this->_lastId = mysql_insert_id ( $this->_Db );
return $this->_lastId;
}
/**
* 获取符合条件的记录数
*
*/
public function fetchNum($sql) {
$reult = $this->query ( $sql );
$num = mysql_num_rows ( $reult );
return $num;
}
/**
* 输出适合的where语句
*/
public function quoteInto($string,$value ) {
$value = $this->strtag($value);
if(is_numeric($value)){
$string = str_replace('?',$value,$string);
}else{
$string = str_replace('?',"'".$value."'",$string);
}
return $string;
}
/**
* 数据数据库所用大小
*
* @param unknown_type $dbname
* @return unknown
*/
public function getSqlSize($dbname){
$sql = "SHOW TABLE STATUS from $dbname";
$rows = $this->fetchAll($sql);
$total = 0;
foreach ($rows as $row){
$total += $row['Data_length'];
$total += $row['Index_length'];
}
return round($total/(1024*1024),2);
}
/**
* 判断缓存文件是否有效,如果有效，则返回缓存内容
*/
private function checkCache($sql,$cacheTime = 0,$cacheId=''){
//不缓存，直接返回
return false;
if($cacheTime == 0){
return false;
} else {
$tmp = $this->createFilename($sql,$cacheId);
if(file_exists($tmp['path'].$tmp['filename'])&&(filemtime($tmp['path'].$tmp['filename'])+$cacheTime)>time()){
$content = file_get_contents($tmp['path'].$tmp['filename']);
return !empty($content)?unserialize($content):array();
} else{
return false;
}
}
}
/**
* 生成缓存
*/
private function createCache($sql,$data,$cacheId=''){
return;
$tmp = $this->createFilename($sql,$cacheId);
if(!is_dir($tmp['path']))@mkdir($tmp['path'],0777,true);
@file_put_contents($tmp['path'].$tmp['filename'],serialize($data));
}
/**
* 根据sql语句生成文件名及路径
*/
private function createFilename($sql,$cacheId=''){
if(!empty($cacheId))$sql = $cacheId;
$data = array(
'path' => $this->_cachePath.'sql/',
'filename'=> ''
);
if(empty($sql)) return $data;
$tmpName = md5($sql);
$data = array(
'path' => $this->_cachePath.'sql/'.substr($tmpName,0,2).'/'.substr($tmpName,2,2).'/',
'filename'=> substr($tmpName,3).'.tmp'
);
return $data;
}
/**
* 清除缓存
*
* 条件为空则清除所有缓存
*
* @return DbMysql
*/
public function clearCache($sql='',$cacheId=''){
$data = $this->createFilename($sql,$cacheId);
$times = time();
if(!empty($sql) || !empty($cacheId)){
if(!empty($data['filename'])){
$path1= $data['path'].$data['filename'];
if(file_exists($path1) && filemtime($path1)<$times)@unlink($path1);
}
}
//清除所有缓存
else{
$this->clearFile($this->_cachePath,$times);
}
return true;
}
/**
* 遍历删除文件及目录
*/
private function clearFile($cachePath,$times){
$list = scandir($cachePath);
foreach ($list as $key1=>$row1){
if($key1<=1)continue;
$path1 = $cachePath.'/'.$row1;
if(is_dir($path1)){
$this->clearFile($path1,$times);
//rmdir($path1);
} else {
if(file_exists($path1) && filemtime($path1)<$times)@unlink($path1);
}
}
}
/**
* 写错误日志
*
* @param unknown_type $log
*/
private function createErrorLog($sql){
$log = array(
date("Y-m-d H:i:s"),
str::getThisUrl(),
$sql,
mysql_error ()
);
$log = implode(' - ',$log)."\r\n";
$filename = $this->_cachePath.'error/'.date("Y-m").'.txt';
if(!$fp = fopen($filename,'a+')){
echo '错误日志打开失败,请联络QQ:9986584';
}
if( fwrite($fp,$log) === FALSE ){
echo '错误日志写入失败,请联络QQ:9986584';
}
fclose($fp);
}
/**
* 获取最后一次执行的sql语句
*
*/
public function getLastSql(){
return $this->_sql;
}
/**
* 获取数据库中所有的表
* @param type $dbname
* @return type
*/
function fetchAllTable($dbname=''){
$dbname = !empty($dbname)?$dbname:$this->_config['dbname'];
$list = array();
$result = mysql_list_tables($dbname);
if($result){
while ($row = mysql_fetch_row($result)){
$list[] = $row[0];
}
return $list;
}
else
return array();
}
/**
* 获取最后一次影响的记录数
* @return type
*/
function fetchChangeRow(){
return mysql_affected_rows();
}
/**
* 释放查询结果
*/
private function free() {
mysql_free_result($this->_Db);
}
/**
*
*/
function __destruct() {
// $this->free();
}
}
?>