mypdo.class.php,自己做工具--封裝PHP的PDO類--單例模式

/**

* DAOPDO

* @author houzhyan

* @license http://www.descartes.top/

* @version >5.0 utf8

*/

class DAOPDO

{

protected static $_instance = null;

protected $dbName = '';

protected $dsn;

protected $dbh;

/**

* 構造

*

* @return MyPDO

*/

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() {}

/**

* Singleton instance

*

* @return Object

*/

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;

}

/**

* Query 查詢

*

* @param String $strSql SQL語句

* @param String $queryMode 查詢方式(All or Row)

* @param Boolean $debug

* @return Array

*/

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;

}

/**

* Update 更新

*

* @param String $table 表名

* @param Array $arrayDataValue 字段與值

* @param String $where 條件

* @param Boolean $debug

* @return Int

*/

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;

}

/**

* Insert 插入

*

* @param String $table 表名

* @param Array $arrayDataValue 字段與值

* @param Boolean $debug

* @return Int

*/

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);

$result = $this->dbh->exec($strSql);

$this->getPDOError();

return $result;

}

/**

* Replace 覆蓋方式插入

*

* @param String $table 表名

* @param Array $arrayDataValue 字段與值

* @param Boolean $debug

* @return Int

*/

public function replace($table, $arrayDataValue, $debug = false)

{

$this->checkFields($table, $arrayDataValue);

$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;

}

/**

* Delete 刪除

*

* @param String $table 表名

* @param String $where 條件

* @param Boolean $debug

* @return Int

*/

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;

}

}

/**

* execSql 執行SQL語句

*

* @param String $strSql

* @param Boolean $debug

* @return Int

*/

public function execSql($strSql, $debug = false)

{

if ($debug === true) $this->debug($strSql);

$result = $this->dbh->exec($strSql);

$this->getPDOError();

return $result;

}

/**

* 獲取字段最大值

*

* @param string $table 表名

* @param string $field_name 字段名

* @param string $where 條件

*/

public function getMaxValue($table, $field_name, $where = '', $debug = false)

{

$strSql = "SELECT MAX(".$field_name.") AS MAX_VALUE FROM $table";

if ($where != '') $strSql .= " WHERE $where";

if ($debug === true) $this->debug($strSql);

$arrTemp = $this->query($strSql, 'Row');

$maxValue = $arrTemp["MAX_VALUE"];

if ($maxValue == "" || $maxValue == null) {

$maxValue = 0;

}

return $maxValue;

}

/**

* 獲取指定列的數量

*

* @param string $table

* @param string $field_name

* @param string $where

* @param bool $debug

* @return int

*/

public function getCount($table, $field_name, $where = '', $debug = false)

{

$strSql = "SELECT COUNT($field_name) AS NUM FROM $table";

if ($where != '') $strSql .= " WHERE $where";

if ($debug === true) $this->debug($strSql);

$arrTemp = $this->query($strSql, 'Row');

return $arrTemp['NUM'];

}

/**

* 獲取表引擎

*

* @param String $dbName 庫名

* @param String $tableName 表名

* @param Boolean $debug

* @return String

*/

public function getTableEngine($dbName, $tableName)

{

$strSql = "SHOW TABLE STATUS FROM $dbName WHERE Name='".$tableName."'";

$arrayTableInfo = $this->query($strSql);

$this->getPDOError();

return $arrayTableInfo[0]['Engine'];

}

//預處理執行

public function prepareSql($sql=''){

return $this->dbh->prepare($sql);

}

//執行預處理

public function execute($presql){

return $this->dbh->execute($presql);

}

/**

* pdo屬性設置

*/

public function setAttribute($p,$d){

$this->dbh->setAttribute($p,$d);

}

/**

* beginTransaction 事務開始

*/

public function beginTransaction()

{

$this->dbh->beginTransaction();

}

/**

* commit 事務提交

*/

public function commit()

{

$this->dbh->commit();

}

/**

* rollback 事務回滾

*/

public function rollback()

{

$this->dbh->rollback();

}

/**

* transaction 通過事務處理多條SQL語句

* 調用前需通過getTableEngine判斷表引擎是否支持事務

*

* @param array $arraySql

* @return Boolean

*/

public function execTransaction($arraySql)

{

$retval = 1;

$this->beginTransaction();

foreach ($arraySql as $strSql) {

if ($this->execSql($strSql) == 0) $retval = 0;

}

if ($retval == 0) {

$this->rollback();

return false;

} else {

$this->commit();

return true;

}

}

/**

* checkFields 檢查指定字段是否在指定數據表中存在

*

* @param String $table

* @param array $arrayField

*/

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.");

}

}

}

/**

* getFields 獲取指定數據表中的全部字段名

*

* @param String $table 表名

* @return array

*/

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;

}

/**

* getPDOError 捕獲PDO錯誤信息

*/

private function getPDOError()

{

if ($this->dbh->errorCode() != '00000') {

$arrayError = $this->dbh->errorInfo();

$this->outputError($arrayError[2]);

}

}

/**

* debug

*

* @param mixed $debuginfo

*/

private function debug($debuginfo)

{

var_dump($debuginfo);

exit();

}

/**

* 輸出錯誤信息

*

* @param String $strErrMsg

*/

private function outputError($strErrMsg)

{

throw new Exception('MySQL Error: '.$strErrMsg);

}

/**

* destruct 關閉數據庫連接

*/

public function destruct()

{

$this->dbh = null;

}//PDO執行sql語句,返回改變的條數    public function exec($sql=''){        return $this->dbh->exec($sql);    }}?>