<?php
/**
* PDO数据库操作类
*/
class PDODataHandle {
//数据库链接对象
private $connect;
//数据库配置
private $dbconfig;
//最后的SQL语句
private $sql;
//最后绑定的参数
private $param;
//对象
private static $_instance;
//私有构造方法
private function __construct($dbconfig){
$host = $dbconfig['hostname'];
$dbname = $dbconfig['database'];
$username = $dbconfig['username'];
$password = $dbconfig['password'];
$dsn = "mysql:host={$host};dbname={$dbname}";
try {
$this->connect = new PDO($dsn, $username, $password);
//设置错误处理模式
$this->connect->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
//设置字符集
$this->connect->query("set names utf8");
}catch (PDOException $ex){
echo '无法链接数据库。<br />';
echo '数据库主机：' . $host . '<br />';
echo "数据库：$dbname<br/>";
echo '请认真检查数据库配置...<br />';
exit();
}
}
//私有clone
private function __clone() {
}
/**
* 单列
* @param 数据库配置 $dbconfig
*/
public static function getInstance($dbconfig){
if(!empty($dbconfig['hostname']) && !empty($dbconfig['database']) && !empty($dbconfig['username']) && !empty($dbconfig['password'])){
//检测类是否被实例化
if(!(self::$_instance instanceof self)){
self::$_instance=new PDODataHandle($dbconfig);
}
return self::$_instance;
}
}
/**
* 插入数据
* @param string $tbname 表名
* @param array $data 插入数据
*/
public function insert($tbname,$data){
$param = array();
$fragment = array();
foreach ($data as $key=>$value){
$replaceValue = $this->replaceValue($key, $value, $param);
$fragment [] = $key . ' = ' . $replaceValue;
}
$sql = "INSERT INTO `{$tbname}` SET " . implode(',', $fragment);
try {
$prepare = $this->connect->prepare($sql);
$this->sql = $sql;
$this->param = $param;
return $prepare->execute($param);
} catch (PDOException $ex) {
// exit($ex->getMessage());
exit;
}
}
/**
* 更新数据
* @param string $tbname 表名
* @param array $data 数据
* @param array $where 条件
* @return boolean 执行成功与否
*/
public function update($tbname,$data,$where){
$param = array();
$fragment = array();
foreach ($data as $key=>$value){
$replaceValue = $this->replaceValue($key, $value, $param);
$fragment [] = $key . ' = ' . $replaceValue;
}
$result = $this->compileWhere($where);
$where = $result['sql'];
$param = array_merge($param,$result['param']);
$sql = "UPDATE `{$tbname}` SET " .implode(',', $fragment). ' ' . $where;
try {
$prepare = $this->connect->prepare($sql);
$this->sql = $sql;
$this->param = $param;
return $prepare->execute($param);
} catch (PDOException $ex) {
// exit($ex->getMessage());
exit;
}
}
/**
* 删除数据
* @param string $tbname 表名
* @param array $where 条件
*/
public function delete($tbname,$where){
$data = $this->compileWhere($where);
$where = $data['sql'];
$param = $data['param'];
$sql = "DELETE FROM `{$tbname}` {$where}";
try {
$prepare = $this->connect->prepare($sql);
$this->sql = $sql;
$this->param = $param;
return $prepare->execute($param);
} catch (PDOException $ex) {
// exit($ex->getMessage());
exit;
}
}
/**
* 查询分页数据
* @param string $tbname 表名
* @param array $field 字段
* @param array $where 查询条件
* @param string $order 排序
* @param int $pageIndex 当前页
* @param int $pageSize 页数据量
*/
public function listinfo($tbname,$field,$where,$order,$pageIndex,$pageSize){
$pageParam = $this->getPageParam($pageIndex, $pageSize);
if(is_string($where) && !empty($where)){
$field = $this->formatField($field);
$order = $this->formatOrder($order);
$sql = "SELECT {$field} FROM `{$tbname}` WHERE {$where} {$order} LIMIT {$pageParam['offset']},{$pageParam['limit']}";
$list = $this->noCompileQuery($sql);
$sql = "SELECT count(*) as count FROM `{$tbname}` WHERE {$where}";
$count = intval($this->getValue(array_shift($this->noCompileQuery($sql)), 'count'));
}else{
$list = $this->query($tbname,$field,$where,$order,$pageParam['offset'],$pageParam['limit']);
$count = $this->queryCount($tbname, $where);
}
return array('data'=>$list,'count'=>$count);
}
/**
* 执行分组查询，底层仍调用query方法，此方法为简化操作所用
* @param string $tbname 表名
* @param array $field 字段
* @param array $where 条件
* @param string $group 分组
* @param string $order 排序
* @param int $offset offset
* @param int $limit limit
* @return array 返回结果集
*/
public function queryGroup($tbname,$field,$where,$group,$order = '',$offset = '',$limit = ''){
return $this->query($tbname,$field,$where,$order,$offset,$limit,$group);
}
/**
* 执行查询
* @param string $tbname 表名
* @param array $field 字段
* @param array $where 条件
* @param string $order 排序
* @param int $offset offset
* @param int $limit limit
* @return array 返回结果集
*/
public function query($tbname,$field = array('*'),$where = '',$order = '',$offset = '',$limit = '',$group = ''){
//设置表名
$field = $this->formatField($field);
//设置查询条件和绑定参数
$data = !empty($where) ? $this->compileWhere($where) : $where;
if(!empty($data)){
$where = $data['sql'];
$param = $data['param'];
}else{
$where = '';
$param = '';
}
//设置排序
$order = !empty($order) ? "ORDER BY {$order}" : $order;
//设置limit
$limit = $this->formatLimit($offset, $limit);
//设置分组
$group = !empty($group) ? "GROUP BY {$group}" : $group ;
//拼接SQL
$sql = <<< SQL
SELECT {$field} FROM {$tbname} {$where} {$group} {$order} {$limit}
SQL;
//执行查询
try {
$prepare = $this->connect->prepare($sql,array (PDO :: ATTR_CURSOR => PDO :: CURSOR_FWDONLY));
if(!empty($param)){
//绑定参数
$ss = $prepare->execute($param);
}else{
$ss = $prepare->execute();
}
//设置获取数据模式
$prepare->setFetchMode(PDO::FETCH_ASSOC);
$result = $prepare->fetchAll();
} catch (PDOException $ex) {
// exit($ex->getMessage());
exit;
}
$this->sql = $sql;
$this->param = $param;
return $result;
}
/**
* 获取一条记录
* @param string $tbname 表名
* @param array $field 字段
* @param array $where 条件
*/
public function queryOne($tbname,$field,$where){
return array_shift($this->query($tbname,$field,$where,'','',''));
}
/**
* 获取指定条件查询出的记录数
* @param string $tbname 表名
* @param array $where 条件
*/
public function queryCount($tbname,$where){
return $this->getValue($this->queryOne($tbname, array('count(*) as count'), $where), 'count');
}
/**
* 不编译执行查询
* @param string $sql
* @return array result
*/
public function noCompileQuery($sql){
try {
$rs = $this->connect->query($sql);
$rs->setFetchMode(PDO::FETCH_ASSOC);
$this->sql = $sql;
$this->param = array();
return $rs->fetchAll();
} catch (PDOException $ex) {
// exit($ex->getMessage());
exit;
}
}
/**
* 获取执行的最后一条sql语句和绑定的参数
*/
public function getLastSql(){
return array('sql'=>$this->sql,'param'=>$this->param);
}
/**
* 计算分页参数
* @param int $pageIndex
* @param int $pageSize
*/
private function getPageParam($pageIndex,$pageSize){
$pageIndex = intval($pageIndex);
$pageSize = intval($pageSize);
if(empty($pageIndex) || empty($pageSize)){
//如果分页参数有误,则只返回一条数据
$offset = 0;
$limit = 1;
}else{
$offset = $pageSize * ($pageIndex - 1);
$limit = $pageSize;
}
return array('offset' => $offset,'limit' => $limit);
}
/**
* 解析where数组，生成sql语句
* @param array $where
* @return string sql 语句
*/
private function compileWhere($where){
if(empty($where)){return '';}
$sql = '';
$sqlFragment = array();
$param = array();
if(isset($where['connector'])){
$connector = $where['connector'];
unset($where['connector']);
}else{
$connector = null;
}
//处理自定义约束条件
$this->handleConstraint($where, $sqlFragment, $param);
//处理自定义连接符
$sql = $this->handleConnector($sqlFragment, $param,$connector);
return array(
'sql' => $sql,
'param' => $param
);
}
/**
* 将参数值替换为绑定参数
* @param string $key key
* @param string $join 连接符
* @param string $value value
* @param array $param 绑定参数数组,引用传递
* @return string 替换后的参数
*/
private function replaceValue($key,$value,&$param){
if(is_array($value)){
$n =1;
$keys = array();
foreach ($value as $v){
$keys[] = ':'.$key.$n;
$param[':'.$key.$n] = $v;
$n++;
}
return $keys;
}else{
$param[':'.$key] = $value;
return ':'.$key;
}
}
/**
* 生成where语句后跟的sql条件判断
* @param string $key key
* @param string $join 连接符
* @param string $value value
* @return string sql语句
*/
private function createConstraintSql($key,$join,$value){
switch ($join){
case 'in':return $key.' '.$join.' '.'('.implode(",", $value).')';
case 'between and':return $key .' BETWEEN '. array_shift($value) . ' AND ' . array_shift($value);
default:return $key.' '.$join.' '.$value;
}
}
/**
* 处理自定义约束条件
* @param array $where 输入的where数组
* @param array $sql sql片段数组，用于后续操作，引用传递
* @param array $param 绑定参数数组，用于后续操作，引用传递
*/
private function handleConstraint($where,&$sql,&$param){
foreach ($where as $key=>$v){
if(is_array($v)){
//限定约束条件
$join = $v['join'];
$value = $v['value'];
$sql[] = $this->createConstraintSql($key, $join, $this->replaceValue($key, $value,$param));
}else{
//未限定约束条件，默认为'='
$join = '=';
$value = $v;
$sql[] = $this->createConstraintSql($key, $join, $this->replaceValue($key, $value,$param));
}
}
}
/**
* 处理自定义连接符
* @param array $where 输入的where数组
* @param array $sql sql片段数组，用于后续操作，引用传递
* @param array $return 返回数组
*/
private function handleConnector(&$sql,&$param,$connector){
if(count($sql) >1){
if(!empty($connector)){
//手动设置连接符
foreach ($sql as $section){
$con = array_shift($connector);
if(!empty($con)){
$return .= ' '.$section .' '. $con . ' ';
}else{
$return .= ' '.$section.' ';
}
}
}else{
//未手动设置，则默认为AND
$con = 'AND';
$return = ' '.implode(" {$con} ", $sql).' ';
}
return ' WHERE ' . $return.' ';
}else{
return " WHERE ".array_shift($sql)." ";
}
}
/**
* 格式化field
* @param array $field
* @return string field
*/
private function formatField($field){
if($field =='*'){
return '*';
}else{
return implode(',', $field);
}
}
/**
* 格式化limit
* @param int $offset
* @param int $limit
*/
private function formatLimit($offset,$limit){
if(is_numeric($offset)&&is_numeric($limit)){
return "LIMIT {$offset},{$limit}";
}else{
return '';
}
}
/**
* 格式化order
* @param string $order 排序
*/
private function formatOrder($order){
if(isset($order) && !empty($order)){
return 'ORDER BY '.$order;
}else{
return '';
}
}
/**
* 获取数组键值
* @param array $array
* @param string|int $key
*/
private function getValue($array,$key){
return $array[$key];
}
}
