[PHP] 繼承改寫 PDO

根據過去對 PDO 的使用習慣
我從新改寫跟簡化一些函數
基本上跟原本的沒有差太多

物件宣告：

<?php

/*改寫資料連結物件*/
class DB extends PDO {

    /* 預設的資料取得格式 */
    protected static $_fetch_mode = self :: FETCH_ASSOC;

    /**
     * @param array $conf
     * [host]     = "localhost"
     * [username] = "root"
     * [password] = "1111"
     * [dbname]   = "test"
     * [init] = "SET NAMES 'utf8'; SET group_concat_max_len=65536;"
     * 
     * @return class
     */
    public function __construct(array $conf){
        parent :: __construct(
            'mysql:host='.$conf['host'].';dbname='.$conf['dbname'], 
            $conf['username'], 
            $conf['password']
        );

        /* MySQL 前置設定 */
        foreach($conf['init'] as $query){
            parent :: exec($query);
        }

        /*以例外方式處理錯誤*/
        $this->setAttribute(
            self :: ATTR_ERRMODE, 
            self :: ERRMODE_EXCEPTION
        );
    
        /*改寫資料流物件*/
        $this->setAttribute(
            self :: ATTR_STATEMENT_CLASS, 
            array ('DBStatement',array (
                $this
            )
        ));
    }


    /**===============================================================**/
    /* 將字串加入引號及反斜線  */
    public function quote($var = null){
        if(is_array($var)){
            foreach ($var as $key => $value) {
                $var[$key] = parent :: quote($value);
            }
            return join(',', $var);

        }elseif($var === null){
            return 'NULL';
        }else{
            return parent :: quote($var);
        }
    }

    /* 將 $query 字串與陣列做變數合併  */
    public function bind($query, array $bind = array ()){
        foreach($bind as $key => $value){
            if ($key[0] == '/' && $key[1] == '*') {
                continue;
            }

            $bind[$key] = $this->quote($value);
        }

        return strtr($query, $bind);
    }


    /**===============================================================**/
    /*重新封裝 exec*/
    public function exec($query, array $bind = array ()){
        $query = $this->bind($query, $bind);

        $stmt = parent :: query($query);
        $rowCount = $stmt->rowCount();
        $stmt->closeCursor();
        $stmt = null;
        return $rowCount;
    }

    /*重新封裝 query*/
    public function query($query, array $bind = array ()){
        $query = $this->bind($query, $bind);

        $stmt = parent :: query($query);
        $stmt->setFetchMode(self :: $_fetch_mode);
        return $stmt;
    }

    /*針對僅取得筆數時的便利函數*/
    public function queryCount($query, array $bind = array ()){
        $query = $this->bind($query, $bind);
        $query = sprintf('SELECT COUNT(*) FROM (%s)AS QUERYCOUNT', $query);

        $stmt = parent :: query($query);
        $rowCount = $stmt->fetchColumn();
        $stmt->closeCursor();
        $stmt = null;
        return $rowCount;

    }

}


/*改寫資料流物件*/
class DBStatement extends PDOStatement{
    public $dbh;

    protected function __construct($dbh){
        $this->dbh = $dbh;
    }
}

對於資料的操作跟原本的事一樣的
但我習慣將 fetch_mode 設定為 FETCH_ASSOC

使用用範例：

<?php
/* 資料庫連結參數 */
$conf = array(
    'host'     => "localhost",
    'username' => "root",
    'password' => "1111",
    'dbname'   => "test_db",
    'init' => "SET NAMES 'utf8'; SET group_concat_max_len=65536;"
);

/* 初始化資料庫連結介面 */
$dbAdapter = new DB($conf); 




/*exec 操作*/
$bind = array();
$bind['/*prefix*/']   = 'mw_';/*資料表前綴*/
$bind[':title']   = '文章標題';
$bind[':content'] = '文章內容';

try {
    $dbAdapter->exec("
        INSERT INTO `/*prefix*/article`
        SET `Title` = :title,
            `Content` = :content,
            `ModifyTime` = NOW() 
    ", $bind);
    
    echo "成功新增";    
    
/*錯誤處理*/    
} catch (Exception $e) {
    switch ($e->getCode()) {
        case 23000: 
            echo "主鍵重複"; break;    
            
        default:
            echo "新增失敗"; break;    
    }    
    
}


/*query 操作*/
$bind = array();
$bind['/*prefix*/']   = 'mw_';/*資料表前綴*/
$bind[':id']   = array('1','2','4');

$data = $dbAdapter->query("
    SELECT `Id`, `Title`, `Content`
    FROM `/*prefix*/article` 
    WHERE `Id` IN(:id)
", $bind)->fetchAll();

var_dump($data);


/*queryCount 單只要取得查詢的筆數時*/
$bind = array();
$bind['/*prefix*/']   = 'mw_';/*資料表前綴*/
$bind[':id']   = array('1','2','4');

$count = $dbAdapter->queryCount("
    SELECT `Id`, `Title`, `Content`
    FROM `/*prefix*/article` 
    WHERE `Id` IN(:id)
", $bind);

echo $count;


/*bind 取得查詢的 Query 字串*/
$bind = array();
$bind['/*prefix*/']   = 'mw_';/*資料表前綴*/
$bind[':id']   = array('1','2','4');

$query_string = $dbAdapter->bind("
    SELECT `Id`, `Title`, `Content`
    FROM `/*prefix*/article` 
    WHERE `Id` IN(:id)
", $bind);

echo $query_string;