DEDECMS数据库操作类简化
发布日期:2018-03-31 16:15:12 点击:2这是一个将DEDECMS数据库操作类简化的类
使用方法
require_once (dirname(__FILE__) . "/include/common.inc.php"); require_once (dirname(__FILE__) . "/_kupe/db.class.php");
#返回DEDE数据操作实例
$d = new kupedb('admin');
取得一条记录:
$rs = $d->find(); 取得所有记录: $rs = $d->findAll();对admin表 添加,编辑,删除
$d->value(数组)->add();
$d->where("条件")->value(数组)->update();
$d->where("条件")->delete();
以下为类代码, 为本人原创 http://www.kupebank.com
<?php
/*
$this->phpmyadminToArray($path)
增加解析PHPMYADMIN导出SQL文件,将SQL文件解析到数组
sqlite 压缩命令: VACUUM
增加了缓存功能
//根据匹配度显示
//SELECT * FROM `ic_news` WHERE 1 order by length(title)-length(replace(title,'美','')) DESC
2013年6月6日 允许多个where 用and连起来的
#事务处理
$db->begin();
echo $db->pk(30)->set('porder='.rand(2,100))->update();
$db->commit();
#优化库所有表
p($db->opt());
function setEngine($table = null,$engine = 'MYISAM')
function setEngines($engine = 'MYISAM')
function tables()
max , sum, count,
*/
/*
**************************************
* Copyright (c) 2007 - 2018 边缘狂人 [PHP软件工作室]
* URL:http://www.kupebank.com
* E-mail:kupebank@163.com
* QQ:124181646
* filename:Mysql.class.php
* date:Tue Mar 16 02:38:06 CST 2010
* write by: 陈阳昇
* 工作室旗下致力于开发优秀的网络产品
* description:
*
**************************************
*/
class kupedb
{
public $table; //操作表
public $prefix; //操作表
public $doTable; //当前操作的全表名
public $pk; //主键
public $lastSql;
public $sqls;
#protected 子类可使用参数, private 子类不可用
protected $option = array(); //连惯操作时,记录参数
protected $_db; //数据库链接实例
protected $config; //
public $server_id = 0; // 用于链接多个数据库
function __construct($table = '',$server_id = 0)
{
global $db;
$this->db = $db;
$this->table = $table;
if ($this->table)
{
$this->prefix = '#@__';
$this->doTable = $this->prefix . $this->table;
#设置一个默认主键
if (!isset($this->pk))
{
$this->pk = 'id';
}
}
}
#查询SQL
function query($sql)
{
$this->sqls[] = $this->lastSql = $sql;
$this->APP_RUN_SQL($sql);
return $this->db->query($sql);
}
#执行SQL
function exec($sql)
{
$this->sqls[] = $this->lastSql = $sql;
$this->APP_RUN_SQL($sql);
//return $this->_db[$this->server_id]->exec($sql);
return $this->db->Execute('me', $sql);
/*
if(!$q = $this->_db[$this->server_id]->exec($sql))
{
$this->showError($this->_db[$this->server_id]->errorInfo());
}
else
{
return $q;
}
*/
}
#取得一个记录
function fetchArray(&$query)
{
#0,1,2,3
return $query->fetch(1);
}
#影响记录数
/*
function affectedRows()
{
return $this->_db[$this->server_id]->lastInsertId();
}
*/
/*
做记录
*/
function APP_RUN_SQL($sql)
{
//$this->APP_RUN_SQL++;
//$this->APP_RUN_SQLS[] = $sql;
}
#取得最近一次ID
function insertId()
{
$this->APP_RUN_SQL("insertId");
return $this->_db[$this->server_id]->lastInsertId();
}
#关闭
function close()
{
//mysql_close();
}
#事务支持
function begin()
{
// 开始执行事务
//mysql_query("begin");
// 设置自动事务处理为0,1是提交即执行
//mysql_query("set autocommit=0");
return $this->_db[$this->server_id]->beginTransaction();
}
//别名
function start()
{
return $this->begin();
}
function commit()
{
// 提交执行
//mysql_query("commit");
//mysql_query("set autocommit=1");
return $this->_db[$this->server_id]->commit();
}
function end()
{
return $this->commit();
}
/*
回滚操作
*/
function rollback()
{
//mysql_query("rollback");
//mysql_query("set autocommit=1");
$this->_db[$this->server_id]->rollBack();
}
#对SQL的值 转义
function escape($value)
{
//return $value; mysql_escape_string
return addslashes($value);
}
#####################################
function lastSql()
{
return $this->lastSql;
}
function allSql()
{
return $this->sqls;
}
/*
支持连惯操作
*/
function __call($key, $value)
{
if (false !== strpos('|field|table|order|limit|group|values|set|as|having|', "|{$key}|"))
{
if (isset($value[0]) && $value[0])
{
$this->option[$key] = $value[0];
}
return $this;
}
}
/*
处理 left|right|inner
*/
final function LeftJoin($table, $on)
{
$this->option['join'][] = array('table' => ' Left Join ' . $this->prefix . $table, 'on' => $on);
return $this;
}
final function RightJoin($table, $on)
{
$this->option['join'][] = array('table' => " Right Join " . $this->prefix . $table, 'on' => $on);
return $this;
}
final function InnerJoin($table, $on)
{
$this->option['join'][] = array('table' => " INNER Join " . $this->prefix . $table, 'on' => $on);
return $this;
}
final function SetCache($sec, $prefix = '')
{
$this->option['cache'] = $sec;
$this->option['cachePrefix'] = $prefix;
return $this;
}
#与setcache一样
final function Cache($sec, $prefix = '')
{
$this->option['cache'] = $sec;
$this->option['cachePrefix'] = $prefix;
return $this;
}
function where($wstr)
{
$this->option['where'] = isset($this->option['where']) && $this->option['where'] ? "{$this->option['where']} AND {$wstr}" : $wstr;
return $this;
}
function set($k,$v = null)
{
if($v == null)
{
$this->option['set'] = $k;
}
else
{
$this->option['set'] = array($k,$v);
}
return $this;
}
final function pk($id)
{
$this->option['where'] = isset($this->option['where']) && $this->option['where'] ? " AND {$this->pk}='{$id}'" : " {$this->pk}='{$id}'";
return $this;
}
/*
增加LIKE 查询,可以多个
*/
final function Like($field, $value)
{
$this->option['like'][] = array($field, $value);
}
#取得一个记录
final function find($option = '')
{
$this->limit(1);
$rs = $this->findAll($option);
return reset($rs);
}
#insert 的别名
final function add($option = '', $action='INSERT')
{
return $this->insert($option, $action);
}
#insert 的别名
final function create($option = '')
{
return $this->insert($option);
}
#修改表名
final function setTable($table, $pk = false)
{
$this->table = $table;
if (!$pk)
{
$this->pk = $pk;
}
return $this->doTable = $this->prefix . $this->table;
}
/*
手动执行一个SQL
$sql
#__TABLE__ 当前类全表名
#__PRE__ 表的前缀
*/
final function sql($sql)
{
$sql = str_replace(array('#__TABLE__', '#__PRE__', '{PREFIX}'), array($this->doTable, $this->prefix, $this->prefix), $sql);
if (strtoupper(substr($sql, 0, 6)) == 'SELECT')
{
$query = &$this->query($sql);
$rs = array();
while ($rt = $this->fetchArray($query))
{
$rs[] = $rt;
}
return $rs;
}
else
{
return $this->query($sql);
}
}
/* 通过SQL查记录*/
function findSql($sql)
{
$query = $this->query($sql);
$rs = array();
while($rt = $this->fetchArray($query))
{
$rs[] = $rt;
}
return $rs;
}
#delete from ic_t;
#update sqlite_sequence SET seq = 0 where name ='ic_t';
//清空表
function TRUNCATE($option = '')
{
if (!is_array($option))
{
$option = $this->option;
$this->option = array();
}
$table = isset($option['table']) ? $this->prefix . $option['table'] : $this->doTable;
if($this->config['db_type'] == 'sqlite')
{
$this->query("delete from $table ");
$this->query("update sqlite_sequence SET seq = 0 where name ='{$table}' ");
return true;
}
else
{
return $this->query("TRUNCATE TABLE $table ");
}
}
//别名
function clear($option = '')
{
$this->TRUNCATE($option);
}
function config()
{
return $this->config;
}
function _query($sql)
{
$sql = str_replace(array('#__TABLE__', '#__PRE__', '{PREFIX}'), array($this->doTable, $this->prefix, $this->prefix), $sql);
return parent::query($sql);
}
/*
统一功能SQL现实
findAll
delete
insert
update
*/
/*
函数说明: 取得数据记录
参数:
$option = array('field' => '*',
'where' => '条件',
'limit' => '10',
'table' => '表',
'order' => '',
'group' => '',
)
*/
function findAll($option = '')
{
if (!is_array($option))
{
$option = $this->option;
$this->option = array();
}
//P($option);
//extract($option);
$field = isset($option['field']) ? $option['field'] : '*';
$table = isset($option['table']) ? $this->prefix . $option['table'] : $this->doTable;
$sql = "SELECT {$field} FROM {$table} ";
if (isset($option['as']))
{
$sql .= ' as ' . $option['as'];
}
if (isset($option['join']))
{
foreach ($option['join'] as $k => $v)
{
$sql .= " {$v['table']} ON {$v['on']}";
}
}
//p($sql);
if (isset($option['where']) && $option['where'])
{
$sql .= " WHERE {$option['where']}";
}
if (isset($option['group']))
{
$sql .= " GROUP BY {$option['group']}";
}
if (isset($option['having']))
{
$sql .= " HAVING {$option['having']}";
}
if (isset($option['order']))
{
$sql .= " ORDER BY {$option['order']}";
}
//limit 开始值, pagesize
if (isset($option['limit']))
{
$sql .= " LIMIT {$option['limit']}";
}
$rs = array();
//SQLITE返回的是对象
$sql = str_replace(array('#__TABLE__', '{PREFIX}'), array($this->doTable, $this->prefix), $sql);
$this->sqls[] = $this->lastSql = $sql;
// load::$APP_RUN_SQL++;
$this->APP_RUN_SQL($sql);
//$this->db->CheckSql($sql);
/*
if(!$prep = $this->_db[$this->server_id]->prepare($sql))
{
$this->showError($this->_db[$this->server_id]->errorInfo());
}
if(!$prep->execute())
{
$this->showError($prep->errorInfo());
}
$rs = $prep->fetchAll(2);
*/
$this->db->Execute('me', $sql);
$rs = array();
while($rt = $this->db->GetArray('me'))
{
$rs[] = $rt;
}
return $rs;
}
function showError($errorInfo = array())
{
$msg = "<div class='error'><div class='header'>执行SQL错误,错误信息如下:</div>";
$msg .= "<style>
.error{
border-bottom:1px solid #ccc;
border-right:1px solid #ccc;
line-height:30px;
margin-left:300px;
margin-right:300px;
margin-top:50px;
}
.error .footer{
text-align:right;
}
.error div{
border-top:1px solid #ccc;
border-left:1px solid #ccc;
padding:10px;
}
.error .header{
font-weight:bolder;
background:#ddd;
}
</style>";
echo $msg;
foreach($errorInfo as $k => $v)
{
echo "<div>{$k}.{$v}</div>\n";
}
if(1)
{
echo "<div>SQL:".$this->lastSql."</div>\n";
}
echo "\n<div class='footer'><a href='http://www.kupebank.com'>来自:Kupe框架</a></div></div>";
exit;
}
/*
函数功能说明: 删除
*/
function delete($option = '')
{
if (!is_array($option))
{
$option = $this->option;
$this->option = array();
}
$table = isset($option['table']) ? $this->prefix . $option['table'] : $this->doTable;
$sql = "DELETE FROM {$table} ";
if (isset($option['where']))
{
$sql .= " WHERE {$option['where']}";
}
if (isset($option['limit']))
{
$sql .= " LIMIT {$option['limit']}";
}
return $this->db->ExecuteNoneQuery2($sql);
//return $this->_db[$this->server_id]->exec($sql);
}
function remove($option = '')
{
return $this->delete($option);
}
/*
函数说明: 插入记录
$option.value => 可以是一维分组, 也可以是二维数组. 二维分组,表示插入多条记录.
$option.table => 要插入到其它表
返回: 最近插入的ID值
*/
function insert($option = '', $action='INSERT')
{
if (!is_array($option))
{
$option = $this->option;
$this->option = array();
}
$keys = array_keys($option['values']);
//一维数组
$table = isset($option['table']) ? $this->prefix . $option['table'] : $this->doTable;
$sql = "{$action} INTO {$table} (`" . implode('`,`', $keys) . "`) VALUES ('" . implode('\',\'', $option['values']) . "')";
/*
$sql = "{$action} INTO {$table} (`" . implode('`,`', $keys) . "`) VALUES (:" . implode(',:', $keys) . ")";
$prep = $this->_db[$this->server_id]->prepare($sql);
$value_arr = array();
foreach($option['values'] as $k => $v)
{
//$values_arr[":{$k}"] = $this->escape($v);
$values_arr[":{$k}"] = $v;
}
*/
//echo $sql;exit;
/*
if(!$prep->execute($values_arr))
{
$this->showError($prep->errorInfo());
}*/
$this->sqls[] = $this->lastSql = $sql;
$this->db->ExecuteNoneQuery2($sql);
return $this->db->GetLastID();
}
/*
功能函数:更新数据库
$option 为数组
$option.value 一组数组
$option.table
$option.pk
*/
function update($option = '')
{
if (!is_array($option))
{
$option = $this->option;
$this->option = array();
}
$set = '';
$set_arr = array();
if (isset($option['values']))
{
foreach ($option['values'] as $k => $v)
{
if ($k != $this->pk)
{
$set .= ",`{$k}`='".$this->escape($v)."' ";
$set_arr[":".$k] = $v;
}
}
}
$table = isset($option['table']) ? $this->prefix . $option['table'] : $this->doTable;
$sql = "UPDATE {$table} SET " . substr($set, 1);
if (isset($option['set']))
{
if(is_array($option['set']))
{
/*$sql .= "`{$option['set'][0]}`=:{$option['set'][0]}";
$set_arr[":".$option['set'][0]] = $option['set'][1];
$set .= ",`{$k}`='".$this->escape($v)."' ";*/
$sql .= "`{$option['set'][0]}`='".$this->escape($option['set'][1])."'";
}
else
{
$sql .= $option['set'];
}
}
if (isset($option['where']))
{
$sql .= " WHERE {$option['where']}";
if (isset($option['values'][$this->pk]))
{
$sql .= " AND `{$this->pk}`='{$option['values'][$this->pk]}'";
}
}
else
{
if (isset($option['values'][$this->pk]))
{
$sql .= " WHERE `{$this->pk}`='{$option['values'][$this->pk]}'";
}
}
if (isset($option['limit']))
{
$sql .= " LIMIT {$option['limit']}";
}
/*if(!$prep = $this->_db[$this->server_id]->prepare($sql))
{
$this->showError($this->_db[$this->server_id]->errorInfo());
}
if(!$q = $prep->execute($set_arr))
{
$this->showError($prep->errorInfo());
}*/
$this->sqls[] = $this->lastSql = $sql;
$this->APP_RUN_SQL($sql);
$this->db->ExecuteNoneQuery2($sql);
return $this->db->GetLastID();
}
#
#简化操作
#
function total($key = '*')
{
$rs = $this->field("COUNT({$key}) AS total")->find();
return $rs['total'];
}
function count($key = '*')
{
$rs = $this->field("COUNT({$key}) AS total")->find();
return $rs['total'];
}
function sum($key)
{
$rs = $this->field("SUM({$key}) AS total")->find();
return $rs['total'];
}
function max($key = '*')
{
$rs = $this->field("max({$key}) AS total")->find();
return $rs['total'];
}
function min($key = '*')
{
$rs = $this->field("min({$key}) AS total")->find();
return $rs['total'];
}
/*
显示本库所有列表
*/
function tables()
{
/*
[queryString] => SHOW TABLE STATUS
[Name] =>
[Engine] =>
[Version] =>
[Row_format] =>
[Rows] =>
[Avg_row_length] =>
[Data_length] =>
[Max_data_length] =>
[Index_length] =>
[Data_free] =>
[Auto_increment] =>
[Create_time] =>
[Update_time] =>
[Check_time] =>
[Collation] =>
[Checksum] =>
[Create_options] =>
[Comment] =>
*/
$sql = "SHOW TABLE STATUS";
$query = $this->query($sql);
$rs = array();
while($rt = $this->fetchArray($query))
{
$rs[] = array(
'Name' => $rt->Name,
'Engine' => $rt->Engine,
'Version' => $rt->Version,
'Row_format' => $rt->Row_format,
'Rows' => $rt->Rows,
'Avg_row_length' => $rt->Avg_row_length,
'Data_length' => $rt->Data_length,
'Max_data_length' => $rt->Max_data_length,
'Index_length' => $rt->Index_length,
'Data_free' => $rt->Data_free,
'Auto_increment' => $rt->Auto_increment,
'Create_time' => $rt->Create_time,
'Update_time' => $rt->Update_time,
'Check_time' => $rt->Check_time,
'Collation' => $rt->Collation,
'Checksum' => $rt->Checksum,
'Create_options' => $rt->Create_options,
'Comment' => $rt->Comment,
);
}
return $rs;
}
#########################常用工具
/*
设置表引擎
INNODB
MYISAM
*/
function setEngine($table = null,$engine = 'MYISAM')
{
$table = $table ? $this->prefix . $table : $this->doTable;
$sql = "ALTER TABLE `{$table}` ENGINE = {$engine};";
return $this->exec($sql);
}
function setEngines($engine = 'MYISAM')
{
$engine || $engine = 'MYISAM';
$table = $this->tables();
foreach ($table as $v)
{
$sql_arr[] = $sql = "ALTER TABLE `{$v['Name']}` ENGINE = {$engine};";
$this->exec($sql);
}
return true;
}
/*
优化数据库
$table 可以是数组, 单个表名, 或为 空 , 为空时,会优化整个数据库
*/
function OPTIMIZE($table = '')
{
if(is_array($table))
{
}
elseif($table)
{
$table = array($table);
}
else
{
$table = $this->tables();
}
$sql = "OPTIMIZE TABLE ";
foreach ($table as $v)
{
if(is_array($v))
{
$sql_arr[] = "`{$v['Name']}`";
}
else
{
$sql_arr[] = "`{$v}`";
}
}
$sql .= implode(',', $sql_arr);
return $this->query($sql);
}
/**
自动将当前数据库修复
参数 $table可以是 单个表名,也可以是 多个表的数组,如果为空,则自动获取当前库的所有表
*/
function REPAIR($table = '')
{
if(is_array($table))
{
}
elseif($table)
{
$table = array($table);
}
else
{
$table = $this->tables();
}
$table = $this->tables();
$sql = "REPAIR TABLE ";
foreach ($table as $v)
{
$sql_arr[] = "`{$v['Name']}`";
}
$sql .= implode(',', $sql_arr);
return $this->query($sql);
}
/**
别名 REPAIR
*/
function opt($table = '')
{
return $this->OPTIMIZE($table);
}
/**
创建一个数据库
*/
function createDB($name)
{
$sql = "CREATE DATABASE `{$name}` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;";
return $this->exec($sql);
}
function phpmyadminToArray($path)
{
$data = file_get_contents($path);
$data = preg_replace('/--.+\n/isU','',$data);
$data = preg_replace('/\/\*.+\*\/;/isU','',$data);
$rs = explode(";\n",trim($data));
$sqls = array();
foreach($rs as $k => $v)
{
$v = trim($v);
if(!$v) continue;
$sqls[] = trim($v).'';
}
//echo $data; p($sqls);
return $sqls;
}
/*
核心操作方法
$this->input->xxxx();
$this->output->xxxx();
$this->db->xxxx();
*/
public function __get($name)
{
return load::loadClass($name,null,null);
}
#释放资源
/*function __destruct()
{
$this->close();
}*/
}
function m($table)
{
static $kupedb;
$kupedb = new kupedb($table);
return $kupedb;
} 本文关键字:外贸新闻
本文来源:
上一篇: 2018中国外贸政策新趋势
