本数据访问类是之前自己写的一个框架里的,修改了一下,所以先放在博客里
下载:
主要几个功能
第一个是数据统计
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>’;
- ?>