封装PDO连接数据库代码

废话不说直接上代码：

<?php
class DB
{
    protected static $_connect;
    protected $dsn, $pdo;
    protected $_data, $_count, $_lastInsertId;
  
    /**
     * 构造函数
     * 
     * @return DB
     */
    private function __construct($dbHost, $dbUser, $dbPasswd, $dbName, $dbCharset, $dbms)
    {
        try {
            $this->dsn = $dbms.':host='.$dbHost.';dbname='.$dbName;
            $this->pdo = new PDO($this->dsn, $dbUser, $dbPasswd);
            $this->pdo->exec('SET character_set_connection='.$dbCharset.', character_set_results='.$dbCharset.', character_set_client=binary');
        } catch (PDOException $e) {
            $this->outputError($e->getMessage());
        }
    }

    /**
     * 获取返回结果、行数及最新插入数据的id
     * 
     */
    public function data(){
        return $this->_data;
    }
    public function count(){
        return $this->_count;
    }
    public function lastInsertId(){
        return $this->_lastInsertId;
    }

    /**
     * 防止克隆
     * 
     */
    private function __clone() {}

    /**
     * Singleton instance
     * 
     * @return Object
     */
    public static function connect($dbHost, $dbUser, $dbPasswd, $dbName, $dbCharset="utf8", $dbms="mysql")
    {
        if (self::$_connect === null) {
            self::$_connect = new self($dbHost, $dbUser, $dbPasswd, $dbName, $dbCharset, $dbms);
        }
        return self::$_connect;
    }


    /**
     * Go 增删改查覆盖
     * 
     * @param String $strSql SQL语句
     * @param Array $arrayInputValue 替换占位符的值
     * @return Object
     */
    public function go($strSql, $arrayInputValue){
        $handle=strtolower(preg_split("/\s+/",$strSql)[0]);//----获取sql语句第一个词（可能是select、update、delete、insert或replace）
        if(strpos("/insert/delete/update/select/replace",$handle)>0){
            $stmt = $this->pdo->prepare($strSql);
            $stmt->execute($arrayInputValue);
            $this->getPDOError();
            $this->_data = $stmt->fetchAll(PDO::FETCH_ASSOC);
            $this->_count=$stmt->rowCount();// ----如果是覆盖旧数据，rowCount不准确
            $this->_lastInsertId=$this->pdo->lastInsertId();
            return $this;
        }else{
            echo "<br>请检查sql语句是否有误！<br>";
        }
    }

    /**
     * execSql
     *
     * @param String $strSql
     * @return Int
     */
    public function execSql($strSql)
    {
        $result = $this->pdo->exec($strSql);
        $this->getPDOError();
        return $result;
    }

    /**
     * 获取字段最大值
     * 
     * @param string $table 表名
     * @param string $field_name 字段名
     * @param string $where 条件
     */
    public function getMaxValue($table, $field_name, $where = '')
    {
        $strSql = "SELECT MAX(".$field_name.") AS MAX_VALUE FROM $table";
        if ($where != '') $strSql .= " WHERE $where";
        $stmt = $this->pdo->prepare($strSql);
        $stmt->execute();
        $data = $stmt->fetchAll(PDO::FETCH_ASSOC);
        $maxValue = $data[0]["MAX_VALUE"];
        if ($maxValue == "" || $maxValue == null) {
            $maxValue = 0;
        }
        return $maxValue;
    }

    /**
     * 获取指定列的数量
     * 
     * @param string $table
     * @param string $field_name
     * @param string $where
     * @return int
     */
    public function getCount($table, $field_name, $where = '')
    {
        $strSql = "SELECT COUNT($field_name) AS NUM FROM $table";
        if ($where != '') $strSql .= " WHERE $where";
        $stmt = $this->pdo->prepare($strSql);
        $stmt->execute();
        $data = $stmt->fetchAll(PDO::FETCH_ASSOC);
        return $data[0]["NUM"];
    }

    /**
     * 获取指定列的总和
     * 
     * @param string $table
     * @param string $field_name
     * @param string $where
     * @return int
     */
    public function getSum($table, $field_name, $where = '')
    {
        $strSql = "SELECT SUM($field_name) AS SN FROM $table";
        if ($where != '') $strSql .= " WHERE $where";
        $stmt = $this->pdo->prepare($strSql);
        $stmt->execute();
        $data = $stmt->fetchAll(PDO::FETCH_ASSOC);
        return $data[0]["SN"];
    }

    /**
   * 预处理执行
   */
    public function prepareSql($sql=''){
        return $this->pdo->prepare($sql);
  }
  
    /**
   * 执行预处理
   */
    public function execute($presql){
        return $this->pdo->execute($presql);
    }

    /**
     * pdo属性设置
     */
    public function setAttribute($p,$d){
        $this->pdo->setAttribute($p,$d);
    }

    /**
     * beginTransaction 事务开始
     */
    public function beginTransaction()
    {
        $this->pdo->beginTransaction();
    }

    /**
     * commit 事务提交
     */
    public function commit()
    {
        $this->pdo->commit();
    }

    /**
     * rollback 事务回滚
     */
    public function rollback()
    {
        $this->pdo->rollback();
    }

    /**
     * getPDOError 捕获PDO错误信息
     */
    private function getPDOError()
    {
        if ($this->pdo->errorCode() != '00000') {
            $arrayError = $this->pdo->errorInfo();
            $this->outputError($arrayError[2]);
        }
    }

    /**
     * 输出错误信息
     * 
     * @param String $strErrMsg
     */
    private function outputError($strErrMsg)
    {
        throw new Exception('MySQL Error: '.$strErrMsg);
    }

    /**
     * 关闭数据库连接
     */
    public function close()
    {
        $this->pdo = null;
    }
}
?>

 

如何使用：

 

<?php
require "db.class.php";//----引入文件
$pdo=DB::connect("localhost","root","[密码]","[数据库名]");//--创建PDO并连接数据库
$sql="……";//----注：这里sql语句占位符必须用“?”表示
$pdo->go($sql, array("[value1]","[value2]",...));//---执行sql语句(所有sql语句均可用go函数执行)

var_dump($pdo->result());//----打印结果集（如果是select语句返回的是代表结果集的数组，如果是其他语句返回的是空数组）
echo $pdo->count();//-----如果是select打印的是结果集包含的数据数量，如果是delete或update打印的是删除或更改的数据数量
echo $pdo->lastInsertId();//------如果是insert语句，$pdo->lastInsertId()表示最新插入数据的id

$pdo->close();//----关闭连接
?>