<?php
/**
-
Created by PhpStorm.
-
User: Administrator
-
Date: 2017/7/24
-
Time: 14:03 / /* * 数据库PDO操作 */ class DAO { private function Link(){ $config=Yaf_Application::app()->getConfig(); try{ $PDO=new PDO($config['mysql']['dsn'],$config['mysql']['user'],$config['mysql']['pass']); $PDO->query("set names utf8"); //数据库utf8 //关闭自动提交 $PDO->setAttribute(PDO::ATTR_AUTOCOMMIT,0); return $PDO; } catch (Exception $e){ echo $e->getMessage(); } } //查询单条数据 public function quOne($sql,$term=''){ $PDO=$this->Link(); try{ $stmt = $PDO->prepare($sql); if(!empty($term)){ foreach ($term as $key=>$value){ $stmt->bindParam($key, $term[$key], PDO::PARAM_STR); } } $stmt->execute(); // $PDO->commit(); return $stmt->fetch(PDO::FETCH_ASSOC); } // if there is a problem we can handle it here catch (Exception $e) { echo $e->getMessage(); } } //查询所有数据 public function quAll($sql,$term=''){ $PDO=$this->Link(); try{ $stmt = $PDO->prepare($sql); if(!empty($term)) { foreach ($term as $key => $value) { $stmt->bindParam($key, $term[$key], PDO::PARAM_STR); } } $stmt->execute(); // $PDO->commit(); return $stmt->fetchAll(PDO::FETCH_ASSOC); } // if there is a problem we can handle it here catch (Exception $e) { echo $e->getMessage(); } } //插入单条数据 //$name 为表名 //$data添加的数据为一个数组 public function inOne($name,$data,$id="",$statc=true){ if(!$statc){ return false; } if(!is_array($data)){ return false; } $PDO=$this->Link(); foreach ($data as $key=>$value){ $keys[]=$key; $values[]="'".$value."'"; } $sql="insert into ".$name." ("; $sql.=implode(",",$keys); $sql.=",_str"; $sql.=") values ("; $sql.=implode(",",$values); $_str=time().$this->getrandstr(); $sql.=",'$_str'"; $sql.=" )"; try{ $PDO->beginTransaction(); $stmt = $PDO->prepare($sql); $state=$stmt->execute(); if(empty($state)){ return false; } if(empty($id)){ $PDO->commit(); return true; } $insql="select $id from $name where _str = '$_str'"; $InsertId = $PDO->prepare($insql); $InsertId->execute(); $PDO->commit(); return $InsertId->fetch(PDO::FETCH_ASSOC); } // if there is a problem we can handle it here catch (Exception $e) { $PDO->rollBack(); return false; } } //插入多条语句 public function inAll($name,$data,$id="",$statc=true){ if(!$statc){ return false; } if(!is_array($data)){ return false; } $PDO=$this->Link(); $valueStr=''; $i=0; foreach ($data as $key=>$value){ $i++; if(!is_array($value)){ return false; } $values=array(); $keys=array(); $valueStr.="("; foreach ($value as $k=>$v){ $values[]=$value[$k]; $keys[]=$k; } $_str=time().$this->getrandstr(); $_strs[]=$_str; $keys[]="_str"; $values[]="'$_str'"; $valueStr.=implode(",",$values).")"; if(count($data)==$i){ $valueStr .= ""; }else{ $valueStr .= ","; } } $sql="insert into ".$name." ("; $sql.=implode(",",$keys); $sql.=") values ".$valueStr; try{ $PDO->beginTransaction(); $stmt = $PDO->prepare($sql); $stmt->execute(); if(empty($id)){ $PDO->commit(); return true; } $_strTerm="'".implode("','",$_strs)."'"; $insql="select $id from $name where _str in ($_strTerm)"; $InsertId = $PDO->prepare($insql); $InsertId->execute(); $PDO->commit(); return $InsertId->fetchAll(PDO::FETCH_ASSOC); } // if there is a problem we can handle it here catch (Exception $e) { $PDO->rollBack(); return false; } }
//多表关联插入 //参数值 term y 是多条语句,不是 /*$table["table"]["a"]["a_id"=>"b_aid"]; $table["table"]["b"]; $table["table"]["c"]; */ // datas["tablename"][]["data"]; public function corr($tables='',$datas){ try{ $arryKey=[]; $PDO=$this->Link(); $PDO->beginTransaction(); foreach ($tables as $term=>$HasKey){ $data=$datas[$term]; //区分$data 是否是多条插入 if(count($data)==1){ /生成单条插入语句/ $keys=null; $values=null; foreach ($data[0] as $key=>$value){ if(!empty($arryKey[$key])){ $keys[]=$key; $values[]="'".$arryKey[$key]."'"; }else{ $keys[]=$key; $values[]="'".$value."'"; } } if(is_array($HasKey)){ $sql="insert into ".$term." ("; $sql.=implode(",",$keys); $sql.=",_str"; $sql.=") values ("; $sql.=implode(",",$values); $_str=time().$this->getrandstr(); $sql.=",'$_str'"; $sql.=" )"; $state[]= $PDO->exec($sql); $idSql="select ".key($HasKey)." from {$term} where _str='$_str'";
$InsertId=$PDO->prepare($idSql); $InsertId->execute(); $inner=$InsertId->fetch(PDO::FETCH_ASSOC); $arryKey[$HasKey[key($HasKey)]]=$inner[key($HasKey)]; }else{ $sql="insert into ".$term." ("; $sql.=implode(",",$keys); $sql.=",_str"; $sql.=") values ("; $sql.=implode(",",$values); $_str=time().$this->getrandstr(); $sql.=",'$_str'"; $sql.=" )"; $state[]=$PDO->exec($sql); } }else{ $valueStr=''; $i=0; foreach ($data as $key => $value) { $i++; $values = null; $keys = null; $valueStr .= "("; foreach ($value as $k => $v) { if(!empty($arryKey[$k])){ $keys[]=$k; $values[]="'".$arryKey[$k]."'"; }else{ $keys[]=$k; $values[]="'".$v."'"; } } $_str = "'".time() . $this->getrandstr()."'"; $_strs[] = $_str; $keys[] = "_str"; $values[] = $_str; if(count($data)==$i){ $valueStr .= implode(",", $values) . ")"; }else{ $valueStr .= implode(",", $values) . "),"; } } $sql = "insert into " . $term . " ("; $sql .= implode(",", $keys); $sql .= ") values " . $valueStr; $state[]=$PDO->exec($sql); } }
if(in_array(false,$state)){ throw new Exception("已回滚"); } $PDO->commit(); return $arryKey; } catch (Exception $e){ $PDO->rollBack(); return false; }
}
//删除
public function Del ($sql,$term,$statc=true){
if(!$statc){
return false;
}
$PDO=$this->Link();
try{
$PDO->beginTransaction();
$stmt = $PDO->prepare($sql);
if(!empty($term)){
foreach ($term as $key=>$value){
$stmt->bindParam($key, $value, PDO::PARAM_STR);
}
}
$stmt->execute();
$PDO->commit();
return true;
} // if there is a problem we can handle it here
catch (Exception $e) {
$PDO->rollBack();
return false;
}
}
//修改
public function Update ($sql,$statc=true){
if(!$statc){
return false;
}
$PDO=$this->Link();
try{
$PDO->beginTransaction();
$stmt = $PDO->prepare($sql);
if(!empty($term)){
foreach ($term as $key=>$value){
$stmt->bindParam($key, $term[$key], PDO::PARAM_STR);
}
}
$result=$stmt->execute();
$PDO->commit();
if($result){
return true;
}else{
return false;
}
} // if there is a problem we can handle it here
catch (Exception $e) {
$PDO->rollBack();
return false;
}
}
//获取随机字符
private function getrandstr(){ $str='ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890'; $randStr = str_shuffle($str);//打乱字符串 $rands= substr($randStr,0,6);//substr(string,start,length);返回字符串的一部分 return $rands; } }