<?php
/**
* 数据库连接类,继承自PDO
*/
class DataBase extends PDO {
private $dsn = "mysql:host=127.0.0.1;dbname=test";
private $username = "root";
private $password = "123456";
/**
* 构造函数
*
*/
public function __construct() {
PDO::__construct($this->dsn, $this->username, $this->password, array(PDO::ATTR_PERSISTENT => true));
parent::exec('set names utf8');
parent::setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
}
/**
* 析构函数
*
*/
public function __destruct() {
}
/**
* 执行单一的SELECT语句
*
* @param string $sql 待执行的SQL语句
* @param string $fetch_style 以哪种形式返回数据 PDO::FETCH_ASSOC PDO::FETCH_NUM PDO::FETCH_BOTH 等
* @return array
*/
function aQuery($sql,$fetch_style=PDO::FETCH_ASSOC) {
try {
$stmt = $this->query($sql);
return $stmt->fetchAll($fetch_style);
} catch (Exception $e) {
try {
return $this->exec($sql);
} catch (Exception $e) {
die($e->getMessage());
}
}
}
/**
* 执行带有绑定参数的SQL语句
*
* @param string $sql 要执行的SQL语句
* @param array $array 绑定的参数
* @param string $fetch_style 以哪种形式返回数据 PDO::FETCH_ASSOC PDO::FETCH_NUM PDO::FETCH_BOTH 等
* @return array or bool
*/
function moreExec($sql,$array=array(),$fetch_style=PDO::FETCH_ASSOC) {
try {
$stmt = $this->prepare($sql);
if(count($array) > 0) {
foreach ($array as $k => $v) {
$stmt->bindValue($k,$v);
}
}
if(substr(trim($sql),0,6) == 'select') {
$stmt->execute();
return $stmt->fetchAll($fetch_style);
} else {
return $stmt->execute();
}
} catch (Exception $e) {
die($e->getMessage());
}
}
/**
* 执行带参数的语句,并返回总数 主要用于带分页的SELECT语句
*
* @param string or array $sql 要执行的SQL语句
* @param array $array 绑定的参数
* @param string $fetch_style 以哪种形式返回数据 PDO::FETCH_ASSOC PDO::FETCH_NUM PDO::FETCH_BOTH 等
* @return array
*/
function moreExecCount($sql,$array=array(),$fetch_style=PDO::FETCH_ASSOC) {
$patterns[0] = "/select .*? from/i";
$patterns[1] = "/from (\w+) (.*?) where/i";
$patterns[2] = "/group by (.*?) limit/i";
$patterns[3] = "/having (.*?) limit/i";
$patterns[4] = "/order by (.*?) limit/i";
$patterns[5] = "/limit (\d+),(\d+)/i";
$patterns[6] = "/limit (\d+)/i";
$replacements[0] = 'select count(*) as sum from';
$replacements[1] = "from \$1 where";
$replacements[2] = 'limit';
$replacements[3] = 'limit';
$replacements[4] = 'limit';
$replacements[5] = '';
$replacements[6] = '';
$sqlCount = preg_replace($patterns, $replacements, $sql);
$sum = $this->moreExec($sqlCount,$array);
return array($this->moreExec($sql,$array,$fetch_style), 'sum'=>$sum[0]['sum']);
}
/**
* 执行一个事务
*
* @param array $sql_array 一些需要同时执行的SQL语句
* @return bool
*/
function Transaction($sql_array) {
try {
$this->beginTransaction();
foreach ($sql_array as $sql) {
$s = trim($sql);
if(substr($s,0,6) == 'select') {
$this->aQuery($s);
} else {
$this->exec($s);
}
}
$this->commit();
return true;
} catch (Exception $e) {
$this->rollBack();
return false;
}
}
}
?>
联系客服