<?php
class mysqlPDO
{
protected static $_instance = null;
protected $dbName = '';
protected $dsn;
protected $dbh;
//构造
private function __construct($dbHost, $dbUser, $dbPasswd, $dbName, $dbCharset)
{
try {
$this->dsn = 'mysql:host='.$dbHost.';dbname='.$dbName;
$this->dbh = new PDO($this->dsn, $dbUser, $dbPasswd);
$this->dbh->exec('SET character_set_connection='.$dbCharset.', character_set_results='.$dbCharset.', character_set_client=binary');
} catch (PDOException $e) {
$this->outputError($e->getMessage());
}
}
//防止克隆
private function __clone() {}
public static function getInstance($dbHost, $dbUser, $dbPasswd, $dbName, $dbCharset)
{
if (self::$_instance === null) {
self::$_instance = new self($dbHost, $dbUser, $dbPasswd, $dbName, $dbCharset);
}
return self::$_instance;
}
//执行
public function query($strSql, $queryMode = 'All', $debug = false)
{
if ($debug === true) $this->debug($strSql);
$recordset = $this->dbh->query($strSql);
$this->getPDOError();
if ($recordset) {
$recordset->setFetchMode(PDO::FETCH_ASSOC);
if ($queryMode == 'All') {
$result = $recordset->fetchAll();
} elseif ($queryMode == 'Row') {
$result = $recordset->fetch();
}
} else {
$result = null;
}
return $result;
}
//修改
public function update($table, $arrayDataValue, $where = '', $debug = false)
{
$this->checkFields($table, $arrayDataValue);
if ($where) {
$strSql = '';
foreach ($arrayDataValue as $key => $value) {
$strSql .= ", `$key`='$value'";
}
$strSql = substr($strSql, 1);
$strSql = "UPDATE `$table` SET $strSql WHERE $where";
} else {
$strSql = "REPLACE INTO `$table` (`".implode('`,`', array_keys($arrayDataValue))."`) VALUES ('".implode("','", $arrayDataValue)."')";
}
if ($debug === true) $this->debug($strSql);
$result = $this->dbh->exec($strSql);
$this->getPDOError();
return $result;
}
//批量插入
public function batchInsert($table,$arrayDatakay, $arrayDataValue, $debug = false)
{
$this->checkFields($table, $arrayDatakay);
$arrayDataValue = array_values($arrayDataValue);
$sql_val = '';
foreach ($arrayDataValue as $key=>$val){
if($key!=0 && !empty($val))
{
$sql_val .=',';
}
$sql_val .= '("'.implode('","', $val).'")';
}
$strSql = "INSERT INTO `$table` (`".implode('`,`', array_keys($arrayDatakay))."`) VALUES $sql_val";
if ($debug === true) $this->debug($strSql);
$result = $this->dbh->exec($strSql);
$this->getPDOError();
return $result;
}
//插入
public function insert($table, $arrayDataValue, $debug = false)
{
$this->checkFields($table, $arrayDataValue);
$strSql = "INSERT INTO `$table` (`".implode('`,`', array_keys($arrayDataValue))."`) VALUES ('".implode("','", $arrayDataValue)."')";
if ($debug === true) $this->debug($strSql);
$this->dbh->exec($strSql);
$this->getPDOError();
$result = $this->dbh->lastInsertId();
return $result;
}
//删除
public function delete($table, $where = '', $debug = false)
{
if ($where == '') {
$this->outputError("'WHERE' is Null");
} else {
$strSql = "DELETE FROM `$table` WHERE $where";
if ($debug === true) $this->debug($strSql);
$result = $this->dbh->exec($strSql);
$this->getPDOError();
return $result;
}
}
//执行sql语句
public function execSql($strSql, $debug = false)
{
if ($debug === true) $this->debug($strSql);
$result = $this->dbh->exec($strSql);
$this->getPDOError();
return $result;
}
//预处理执行
public function prepareSql($sql=''){
return $this->dbh->prepare($sql);
}
//执行预处理
public function execute($presql){
return $this->dbh->execute($presql);
}
//事务开始
public function beginTransaction()
{
$this->dbh->beginTransaction();
}
//事务提交
public function commit()
{
$this->dbh->commit();
}
//事务回滚
public function rollback()
{
$this->dbh->rollback();
}
//检查指定字段是否在指定数据表中存在
private function checkFields($table, $arrayFields)
{
$fields = $this->getFields($table);
foreach ($arrayFields as $key => $value) {
if (!in_array($key, $fields)) {
$this->outputError("Unknown column `$key` in field list.");
}
}
}
//获取指定数据表中的全部字段名
private function getFields($table)
{
$fields = array();
$recordset = $this->dbh->query("SHOW COLUMNS FROM $table");
$this->getPDOError();
$recordset->setFetchMode(PDO::FETCH_ASSOC);
$result = $recordset->fetchAll();
foreach ($result as $rows) {
$fields[] = $rows['Field'];
}
return $fields;
}
//捕获PDO错误信息
private function getPDOError()
{
if ($this->dbh->errorCode() != '00000') {
$arrayError = $this->dbh->errorInfo();
$this->outputError($arrayError[2]);
}
}
//debug
private function debug($debuginfo)
{
var_dump($debuginfo);
exit();
}
//输出错误信息
private function outputError($strErrMsg)
{
throw new Exception('MySQL Error: '.$strErrMsg);
}
//关闭数据库连接
public function destruct()
{
$this->dbh = null;
}
//PDO执行sql语句,返回改变的条数
public function exec($sql='')
{
return $this->dbh->exec($sql);
}
}