php mysql pdo 封装类_php pdo封装类

classMYPDO

{protected static $_instance = null;protected $dbname = '';protected $dsn;protected $dbh;//链接标识

/**

* @param $dbhost

* ip地址 如localhost；127.0.0.1

* @param $dbuser

* mysql用户 如'root';

* @param $dbpasswd

* mysql登录密码 如'root'

* @param $dbname

* 数据库名字 自行查看数据库

* @param $dbcharset

* 写入编码格式 如：utf-8

* character_set_connection character_set_cliect 标注查询返回字符编码，不包含列

* 查询会被服务器从character_set_client转换到character_set_connection

* character_set_results 标注结果返回字符编码，包含列*/

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('character_set_connection='.$dbcharset.'character_set_results='.$dbcharset.'character_set_cliect=binary');

}catch(PDOException $e)

{$this->outputError($e->getMessage());

}

}/**

* __clone 是对象进行浅复制(只被复制引用)

* 在方法中再次进行clone 就是深复制(其中一个修改另外一个也会被修改)*/

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'];

}/**

* beginTransaction 事务开始*/

private functionbeginTransaction()

{$this->dbh->beginTransaction();

}/**

* commit 事务提交*/

private functioncommit()

{$this->dbh->commit();

}/**

* rollback 事务回滚*/

private functionrollback()

{$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 functiongetPDOError()

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

{$this->dbh = null;

}

}

$db = MyPDO::getInstance('localhost','root','root','renshi','utf8');$sql = "select *from renshi_admin";$result = $db->query($sql);echo "

";var_dump($result);//do something...

$db->destruct();