PHP数据库操作类mySQLHelper升级版SEVSQLHelper,含事务回滚机制。
老版本链接：http://luyugao.com/article/626.html。
完整PHP类SEVSQLHelper代码：
<?php
class SEVSQLHelper{
//数据库连接信息
private $host ='127.0.0.1'; //数据库地址
private $dbname ='test'; //数据库名
private $uid ='root'; //数据库用户名
private $pwd ='root'; //数据库密码
private $prefix ='tb_'; //表前缀
private $charset='utf8'; //数据编码
private $con;
private $sql;
//构造函数
function __construct(){
try{
$this->con = new PDO(
"mysql:host={$this->host};dbname={$this->dbname}",
$this->uid,
$this->pwd,
array(PDO::MYSQL_ATTR_INIT_COMMAND => "set names '{$this->charset}';")
);
}catch(Exception $e){
die('MySQL error');
}
}
//初始化
private function init(){
$this->sql = array(
'table' =>'',
'field' =>'',
'join' =>'',
'where' =>'',
'order' =>'',
'limit' =>'',
'sql_key' =>array(),
'sql_value' =>array(),
'set_key' =>array(),//用于更新
);
}
//选择表
public function table($tbname){
$this->init();
if(strpos($tbname,"#__")===false){
$this->sql['table'] = " #__{$tbname} ";
}else{
$this->sql['table'] = " {$tbname} ";
}
return $this;
}
//查询字段
public function field($fd="*"){
$this->sql['field']=" {$fd} ";
return $this;
}
//连接表
public function join($j,$type="LEFT"){
$str="";
$type = strtoupper($type)." JOIN";
$str = "{$type} {$j}";
$this->sql['join'] .= " {$str} ";
return $this;
}
//查询条件
public function where($where){
/*
$where = array(
'id' => array('EQ',6),//eq neq gt ...
'gid' => array("eq",array(60,63)),
'order_id' => 6,
'_logic' => 'or',//连接符，可选
);
$where = "order_id=6 or order_id=7";
*/
$_logic = " and ";//连接符号 and or
if(is_array($where)){
foreach($where as $k=>$v){
if($k=="_logic"){
$_logic = $v;continue;
}
if(is_array($v)){
switch(strtoupper($v[0])){
case "EQ":
if(is_array($v[1])){
$tmp = array();
foreach($v[1] as $o){
$tmp[] = "$k=?";
}
$tmp = implode(" or ",$tmp);
$this->sql['sql_key'][] = "($tmp)";
$this->sql['sql_value'] = array_merge($this->sql['sql_value'],$v[1]);
}else{
$this->sql['sql_key'][]="($k=?)";
$this->sql['sql_value'][]=$v[1];
}
break;
case "NEQ":
$this->sql['sql_key'][]="($k<>?)";
$this->sql['sql_value'][]=$v[1];
break;
case "GT":
$this->sql['sql_key'][]="($k>?)";
$this->sql['sql_value'][]=$v[1];
break;
case "EGT":
$this->sql['sql_key'][]="($k>=?)";
$this->sql['sql_value'][]=$v[1];
break;
case "LT":
$this->sql['sql_key'][]="($k<?)";
$this->sql['sql_value'][]=$v[1];
break;
case "ELT":
$this->sql['sql_key'][]="($k<=?)";
$this->sql['sql_value'][]=$v[1];
break;
case "LIKE":
$this->sql['sql_key'][]="($k like ?)";
$this->sql['sql_value'][]=$v[1];
break;
default:;
}
}else{
$this->sql['sql_key'][]="$k=?";
$this->sql['sql_value'][]=$v;
}
}
$this->sql['sql_key'] = implode(" {$_logic} ",$this->sql['sql_key']);
}else{
$this->sql['sql_key']=$where;
}
$this->sql['where'] = " WHERE {$this->sql['sql_key']} ";
return $this;
}
//排序
public function order($od){
$this->sql['order']=" ORDER BY {$od} ";
return $this;
}
//条数
public function limit($lt){
$this->sql['limit']=" LIMIT {$lt} ";
return $this;
}
//事务开始
public function begin(){
$this->con->beginTransaction();
}
//事务回滚
public function back(){
$this->con->rollBack();
}
//事务提交
public function done(){
$this->con->commit();
}
//查询所有记录
public function select(){
$field = $this->sql['field']==''?'*':$this->sql['field'];
$sql = "SELECT {$field} FROM {$this->sql['table']} {$this->sql['join']} {$this->sql['where']} {$this->sql['order']} {$this->sql['limit']}";
$sql = $this->sql_replace($sql);
$dp = $this->con->prepare($sql);
if(is_array($this->sql['sql_value'])){
for($i=1;$i<=count($this->sql['sql_value']);$i++){
$dp->bindParam($i,$this->sql['sql_value'][$i-1]);
}
}
$dp->execute();
if($dp){
return $dp->fetchAll(PDO::FETCH_ASSOC);
}else{
return null;
}
}
//查询一条记录
public function find(){
$items = $this->select();
if($items && count($items)>0){
return $items[0];
}else{
return null;
}
}
//添加
public function add($data=array()){
$fields = array();
$wen = "";//问号 ?
foreach($data as $k=>$v){
$fields[] = $k;
$sql->sql['sql_value'][] = $v;
$wen.=$wen==''?'?':',?';
}
$fields = implode(',',$fields);
$sql = "insert into {$this->sql['table']}({$fields}) values({$wen})";
$sql = $this->sql_replace($sql);
$dp=$this->con->prepare($sql);
if(is_array($sql->sql['sql_value'])){
for($i=1;$i<=count($sql->sql['sql_value']);$i++){
$dp->bindParam($i,$sql->sql['sql_value'][$i-1]);
}
}
$dp->execute();
return $this->con->lastInsertId();
}
//更新
public function set($newdata=array()){
if(count($newdata)<1){return 0;}//没有修改的内容
if($this->sql['where']==''){
return false;//为了安全
}
$values = array();
//新数据
foreach($newdata as $k=>$v){
$this->sql['set_key'][] = "$k=?";
$values[] = $v;
}
$this->sql['set_key'] = implode(' , ',$this->sql['set_key']);
$newarr = array_merge($values,$this->sql['sql_value']);//倒转数组
$sql = "UPDATE {$this->sql['table']} SET {$this->sql['set_key']} {$this->sql['where']}";
$sql = $this->sql_replace($sql);
$dp=$this->con->prepare($sql);
for($i=1;$i<=count($newarr);$i++){
$dp->bindParam($i,$newarr[$i-1]);
}
$dp->execute();
return $dp->rowCount();
}
//字段递减
public function setDec($field,$count=1){
if($this->sql['where']==''){
return false;
}
$sql="UPDATE {$this->sql['table']} SET {$field}={$field}-{$count} {$this->sql['where']}";
$sql = $this->sql_replace($sql);
$dp=$this->con->prepare($sql);
for($i=1;$i<=count($this->sql['sql_value']);$i++){
$dp->bindParam($i,$this->sql['sql_value'][$i-1]);
}
$dp->execute();
return $dp->rowCount();
}
//字段递增
public function setInc($field,$count=1){
if($this->sql['where']==''){
return false;
}
$sql="UPDATE {$this->sql['table']} SET {$field}={$field}+{$count} {$this->sql['where']}";
$sql = $this->sql_replace($sql);
$dp=$this->con->prepare($sql);
for($i=1;$i<=count($this->sql['sql_value']);$i++){
$dp->bindParam($i,$this->sql['sql_value'][$i-1]);
}
$dp->execute();
return $dp->rowCount();
}
//删除
public function delete(){
if($this->sql['where']==''){
return false;
}
$sql="DELETE FROM {$this->sql['table']} {$this->sql['where']}";
$sql = $this->sql_replace($sql);
$dp=$this->con->prepare($sql);
if(is_array($this->sql['sql_value'])){
for($i=1;$i<=count($this->sql['sql_value']);$i++){
$dp->bindParam($i,$this->sql['sql_value'][$i-1]);
}
}
return $this->con->exec($sql);
}
//统计
public function count(){
$sql="SELECT count(*) FROM {$this->sql['table']} {$this->sql['where']}";
$sql=$this->sql_replace($sql);
$dp=$this->con->prepare($sql);
if(is_array($this->sql['sql_value'])){
for($i=1;$i<=count($this->sql['sql_value']);$i++){
$dp->bindParam($i,$this->sql['sql_value'][$i-1]);
}
}
$dp->execute();
if($dp){
$x = $dp->fetchColumn();
return intval($x);
}
return 0;
}
public function MyQuery($sql){
$sql = $this->sql_replace($sql);
$data=$this->con->query($sql);
if($data)
return $data->fetchAll(PDO::FETCH_ASSOC);
else
return null;
}
public function Excute($sql){
$sql = $this->sql_replace($sql);
$rows=$this->con->exec($sql);
return $rows;
}
//替换表前缀
private function sql_replace($sql){
return str_replace('#__',"{$this->dbname}.{$this->prefix}",$sql);
}
}
$con = new SEVSQLHelper;
function M($table){
global $con;
$con->table($table);
return $con;
}
?>数据库设计截图
使用方法实例
require_once('mysql.php');
//增
/*
$data = array(
'title'=>'feaefe',
'gid'=>rand(1,100),
);
$aok = M("test")->add($data);
echo $aok;
*/
//删
/*
$eok = M("test")->where("id=1")->delete();
echo $eok;
*/
//改
/*
$eok = M("test")->where(array('gid'=>10))->set(array('title'=>'nnnnnnn'));
$eok = M("test")->where("gid=83")->set(array('title'=>'nnnnnnn'));
echo $eok;
*/
//自增自减
/*
自增：setInc,自减：setDec
$count = M("test")->where("gid=-14")->setInc("gid",20);
echo $count;
*/
//查
/*
$v = array(
'_logic'=>'or',
"gid" =>array("eq",array(60,63)),
"id" =>10,
);
$data = M("test")->where($v)->select();
var_dump($data);
*/
//join
$data = M("test")
->join("#__goods on #__goods.gid=#__test.gid","left")
//->join() //可以写多个join
->where("#__goods.gid = 63")
->select();
var_dump($data);
//统计
/*
$count = M("test")->count();
echo $count;
*/SEVSQLHelper为博主原创，不限制传播使用，但转载必须说明出处，
本文地址：http://luyugao.com/article/647.html