08 | 3 月 | 2009 | ℃冻番茄's Blog

本数据访问类是之前自己写的一个框架里的，修改了一下，所以先放在博客里

下载：

db.rar

主要几个功能

第一个是数据统计
count($table,$where=”,$order=”,$group=”);

第二个是查找第一条数据
find($table,$where=”,$field=’*’,$order=”,$limit=”,$group=”)

第三个是查找一组数据
findAll($table,$where=”,$field=’*’,$order=”,$limit=”,$group=”)

第四个是全文件检索，需mysql类型的词库一份，本博里有，如有需要请在博客里搜索全文检索
fullText($table,$key,$str,$where=”,$field=’*’,$limit=”,$group=”)

第五个是数据的缓存，目前只支持find findAll两种方法
dbCache($time,$func,$table,$where=”,$field=’*’,$order=”,$limit=”,$group=”)

第六个是插入数据，只需指定表名和要插入的数组即可，可以直接用$_POST 会自动寻找与表字段相匹配的数据进入插入，插入的数据自己做防注入处理
insert($table,$data)

第七个是删除数据，只需指定表名和主键，或用数组方式指出删除条件
delete($table,$keyarr)

第八个是更新数据，指定表名，数据，主键或是数组形式的更新条件
update($table,$data, $keyarr)

db.class.php

PHP代码

<?php
/*
# 数据模型
# PDO方式访问Mysql数据库，主要方法：利数组方式快速插入（insert）利用数组做连表查询（findAll find）
# 作者：℃冻番茄 qq:7279915
# web@ye55.com www.ye55.com (蓝叶工作室)
*/
class db{
public $db;
public $queryNum=0;
public $querySql=array();
public $error;
public $cacheNum=0;
function __construct()
{
if(!$this->db= @new PDO(conf(‘dbType’).‘:host=’.conf(‘dbHost’).‘;dbname=’.conf(‘dbName’), conf(‘dbUser’), conf(‘dbPass’))){
$this->error=‘数据库连接信息出错！’;
}
$this->db->exec(‘SET NAMES ‘.conf(‘charset’));
}
function query($sql)
{
$rs = $this->db->query($sql);
$this->queryNum++;
$this->querySql[]=$sql;
return $rs;
}
function beginTransaction()
{
return $this->db->beginTransaction();
}
function commit()
{
return $this->db->commit();
}
function rollBack()
{
return $this->db->rollBack();
}
function exec($sql)
{
$this->queryNum++;
$this->querySql[]=$sql;
return $this->db->exec($sql);
}
function lastInsertId()
{
return $this->db->lastInsertId();
}
function setAttribute($attr, $value)
{
return $this->db->setAttribute($attr, $value);
}
function insert($table,$data)
{
$table=conf(‘dbprefix’).$table;
$fields = "";
$values = "";
$tableCache=$this->tableCache($table);
$tableCache=$tableCache[$table];
foreach($data as $field=>$value)
{
if(in_array($field,$tableCache[‘field’])){
$fields .= "`$field`, ";
$values .= "’".mysql_escape_string($value)."’, ";
}
}
$fields = substr_replace($fields, "", -2, 1);
$values = substr_replace($values, "", -2, 1);
$sql = "insert into `$table` ($fields) values ($values)";
//echo $sql;
if($rs = $this->query($sql)){
return $this->lastInsertId();
}else{
$this->error=‘插入数据出错！’;
return false;
}
}
function delete($table,$keyarr){
$table=conf(‘dbprefix’).$table;
if(is_array($keyarr)){
$where = " where ";
foreach($keyarr as $key=>$key_value)
{
$where .= " `$key`=’".mysql_escape_string($key_value)."’ and";
}
$where=substr($where,0,-3);
}else{
$tableCache=$this->tableCache($table);
$where =" where `".$tableCache[$table][‘key’]."`=’".mysql_escape_string($keyarr)."’";
unset($tableCache);
}
$sql = "delete from `$table` $where";
//echo $sql;
if(!$rs =$this->query($sql)){
$this->error=‘插入数据出错！’;
}
return $rs;
}
function update($table,$data, $keyarr)
{
$table=conf(‘dbprefix’).$table;
$set = "";
$tableCache=$this->tableCache($table);
$tableCache=$tableCache[$table];
//dump($data);
foreach($data as $field=>$value)
{
if(in_array($field,$tableCache[‘field’])){
$set .= "`$field`=’".mysql_escape_string($value)."’, ";
}
}
$set = substr_replace($set, "", -2, 1);
if(is_array($keyarr)){
$where = " where ";
foreach($keyarr as $key=>$key_value)
{
$where .= "`$key`=’$key_value’";
}
}else{
$where =" where `".$tableCache[‘key’]."`=’".mysql_escape_string($keyarr)."’";
}
unset($tableCache);
$sql = "update `$table` set $set $where";
//echo $sql;
if(!$rs =$this->query($sql)){
$this->error=‘更新数据出错！’;
}
return $rs;
}
function find($table,$where=”,$field=‘*’,$order=”,$limit=”,$group=”)
{
$sql=$this->_map($table,$where,$field,$order,$limit,$group);
return $this->_find($sql);
}
function findAll($table,$where=”,$field=‘*’,$order=”,$limit=”,$group=”)
{
$sql=$this->_map($table,$where,$field,$order,$limit,$group);
//echo $sql;
return $this->_findAll($sql);
}
function fullText($table,$key,$str,$where=”,$field=‘*’,$limit=”,$group=”)
{
$order=emptyempty($order)?”:‘order by ‘.$order;
$limit=emptyempty($limit)?”:‘limit ‘.mysql_escape_string($limit);
$group=emptyempty($group)?”:‘group by ‘.$group;
$field=emptyempty($field)?‘*’:$field;
$w="where MATCH ($key) against (‘$str’) ".$where;
if(is_array($table)){
$table1=conf(‘dbprefix’).$table[0];
//echo $table1;
$count=count($table);
$joinleft=”;
array_shift($table);
foreach($table as $v){
foreach($v as $k2=>$v2){
$k2=conf(‘dbprefix’).$k2;
foreach($v2 as $k3=>$v3){
$l="$table1.$k3=$k2.$v3";
}
$joinleft.="left join $k2 on $l ";
}
}
$sql="select $field from $table1 $joinleft $w $order $group $limit";
}else{
$table=conf(‘dbprefix’).$table;
$sql="select $field from $table $w $order $group $limit";
}
//echo $sql;
$rs=$this->query($sql);
$row=$rs->fetchall(PDO::FETCH_ASSOC);
return $row;
}
function dbCache($time,$func,$table,$where=”,$field=‘*’,$order=”,$limit=”,$group=”)
{
if($func!=‘find’ && $func!=‘findAll’) return false;
if(emptyempty($time)){
return $this->$func($table,$where,$field,$order,$limit,$group);
}
$tableStr=is_array($table)?serialize($table):$table;
$whereStr=is_array($where)?serialize($where):$where;
$file=conf(‘cacheDir’).‘/’.$func.‘.’.md5($tableStr.$whereStr.$field.$order.$limit.$group).‘.php’;
if(file_exists($file)){
if(time()-filemtime($file)<$time){
$content=file_get_contents($file);
$arr=unserialize(substr($content,13));
$this->cacheNum++;
return $arr;
}else{
return $this->_dbWriteCache($file,$func,$table,$where,$field,$order,$limit,$group);
}
}else{
return $this->_dbWriteCache($file,$func,$table,$where,$field,$order,$limit,$group);
}
}
private function _dbWriteCache($file,$func,$table,$where,$field,$order,$limit,$group)
{
fclose(fopen($file, "w"));
$arr=$this->$func($table,$where,$field,$order,$limit,$group);
$content=‘<?php exit;?>’.serialize($arr);
if(file_put_contents($file,$content)){
return $arr;
}else{
$this->error=‘缓存写入出错！’;
return false;
}
}
function count($table,$where=”,$order=”,$group=”)
{
$tableCache=$this->tableCache($table);
$tableCache=$tableCache[$table];
$sql=$this->_map($table,$where,‘count(‘.conf(‘dbprefix’).$table.‘.’.$tableCache[‘key’].‘)’,$order,”,$group);
//echo $sql;
return $this->_count($sql);
}
private function _count($sql)
{
$rs=$this->query($sql);
$count=$rs->fetchColumn();
$count=emptyempty($count)?0:$count;
return $count;
}
private function _find($sql)
{
$rs=$this->query($sql);
$row=$rs->fetch(PDO::FETCH_ASSOC);
return $row;
}
private function _findAll($sql)
{
$rs=$this->query($sql);
//echo $sql;
$row=$rs->fetchall(PDO::FETCH_ASSOC);
return $row;
}
/**
+———————————————————-
* 指定表名，条件，字段，排序，个数等组合sql
+———————————————————-
* @return string
+———————————————————-
*/
private function _map($table,$where=”,$field=‘*’,$order=”,$limit=”,$group=”)
{
$order=emptyempty($order)?”:‘order by ‘.$order;
$limit=emptyempty($limit)?”:‘limit ‘.mysql_escape_string($limit);
$group=emptyempty($group)?”:‘group by ‘.$group;
$field=emptyempty($field)?‘*’:$field;
$w=”;
if(!emptyempty($where)){
if(is_array($where)){
$w.=‘where’;
foreach($where as $k=>$v){
$w.=" $k=’$v’ and";
}
$w=substr($w,0,strlen($w)-3);
}elseif(!preg_match ("/where/i",$where) && !is_array($table)){
$tableCache=$this->tableCache($table);
$w=" where `".$tableCache[$table][‘key’]."`=’".mysql_escape_string($where)."’";
unset($tableCache);
}else{
$w=$where;
}
}
if(is_array($table)){
$table1=conf(‘dbprefix’).$table[0];
//echo $table1;
$count=count($table);
$joinleft=”;
array_shift($table);
foreach($table as $v){
foreach($v as $k2=>$v2){
$k2=conf(‘dbprefix’).$k2;
foreach($v2 as $k3=>$v3){
$l="$table1.$k3=$k2.$v3";
}
$joinleft.="left join $k2 on $l ";
}
}
$sql="select $field from $table1 $joinleft $w $order $group $limit";
}else{
$table=conf(‘dbprefix’).$table;
$sql="select $field from $table $w $order $group $limit";
}
//echo $sql;
return $sql;
}
/**
+———————————————————-
* 读取指定表的主键及字段，如缓存文件里不存在指定表的信息，则写入
+———————————————————-
* @param string $table 表名
+———————————————————-
* @return array
+———————————————————-
*/
private function tableCache($table)
{
$this->tableCacheFile=conf(‘cacheDir’).‘/table.inc.php’;
if(file_exists($this->tableCacheFile)){
$tableCache=@file_get_contents($this->tableCacheFile);
$tableCache=unserialize(str_replace(‘<?php exit;?>’,”,$tableCache));
if(!isset($tableCache[$table])){
$arr=$this->_tableCache($table);
$tableCache=@array_merge($tableCache,$arr);
//dump($tableCache);
if(!$this->_writeTableCache($tableCache)){
$this->error=‘数据表缓存写入出错 TABLE:’.$table;
}
}
}else{
$tableCache=$this->_tableCache($table);
if(!$this->_writeTableCache($tableCache)){
$this->error=‘数据表缓存写入出错 TABLE:’.$table;
}
}
//dump($tableCache);
return $tableCache;
}
/**
+———————————————————-
* 通过表名，获取些表的主键及所有字段
+———————————————————-
* @param string $table 表名
+———————————————————-
* @return array
+———————————————————-
*/
private function _tableCache($table)
{
$rs=$this->query("describe $table");
$row=$rs->fetchall(PDO::FETCH_ASSOC);
$tmp=array();
foreach($row as $v){
//echo $v[‘Field’];
if($v[‘Key’]==‘PRI’)$key=$v[‘Field’];
$tmp[]=$v[‘Field’];
}
$cache=array($table=>array(‘key’=>$key,‘field’=>$tmp));
return $cache;
}
/**
+———————————————————-
* 把数据表的字段及主键序列化写入缓存文件
+———————————————————-
* @param array $arr 数据表字段组成的数组
+———————————————————-
* @return bool
+———————————————————-
*/
private function _writeTableCache($arr)
{
$content=‘<?php exit;?>’.serialize($arr);
fclose(fopen($this->tableCacheFile,‘w’));
if(file_put_contents($this->tableCacheFile,$content)){
return true;
}else{
return false;
}
}
/**
+———————————————————-
* 析构函数回调
+———————————————————-
*/
function __destruct()
{
$this->db=null;
define(‘QUERYNUM’,$this->queryNum);
if(conf(‘debug’)){
foreach ($this->querySql as $v){
$sql.=$v.‘<br />’;
}
define(‘QUERYSQL’,$sql);
define(‘CACHENUM’,$this->cacheNum);
}
}
}
//fetch(‘PDO_FETCH_ASSOC’)
?>

test.php (演示)

PHP代码

<?php
require_once(‘db.class.php’);
if(!defined(‘APP_PATH’)) define(‘APP_PATH’, dirname(__FILE__));
$charset=conf(‘charset’)==‘utf8’?‘utf-8’:conf(‘charset’);
header("content-type:text/html; charset=$charset");
function config($configNew)
{
$config=array(
‘cacheDir’=>APP_PATH.‘/’.‘cache’,
‘charset’=>‘utf8’,
‘dbType’=>‘mysql’,
‘sessionType’=>‘data’,
‘tplFilename’=>‘.html’,
‘dbLink’=>‘pdo’,
‘dbHost’=>”,
‘dbName’=>”,
‘dbUser’=>”,
‘dbPass’=>”,
‘dbprefix’=>”,
);
$conf=array_merge($config,$configNew);
return $conf;
}
function conf($key)
{
global $config;
return $config[$key];
}
$config=config(array(‘dbHost’=>‘localhost’,‘dbName’=>‘rb’,‘dbUser’=>‘root’,‘dbPass’=>‘root’));
$db=new db();
$list=$db->dbCache(100,‘findAll’,‘xzy_article_title’,”,”,‘aid desc’,20);
if(emptyempty($list)) exit;
echo ‘<ul>’;
foreach($list as $v){
echo ‘<li>’.$v[‘title’].‘ [‘.date(‘Y-m-d’,$v[‘addtime’]).‘]</li>’;
}
echo ‘</ul>’;
?>

生成的数据缓存：

℃冻番茄's Blog

记录平时工作、学习…

日度归档：2009年3月8日

一个mysql数据库访问类，带简单的数据缓存功能

明天新公司正式开工了

2009 年 3 月
一	二	三	四	五	六	日
						1
2	3	4	5	6	7	8
9	10	11	12	13	14	15
16	17	18	19	20	21	22
23	24	25	26	27	28	29
30	31