[PHP] 繼承改寫 PDO

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

物件宣告：

原始碼

1. <?php 
2.  
3. /*改寫資料連結物件*/ 
4. class DB extends PDO { 
5.  
6.    /* 預設的資料取得格式 */ 
7.    protected static $_fetch_mode = self :: FETCH_ASSOC; 
8.  
9.    /** 
10.     * @param array $conf 
11.     * [host]     = "localhost" 
12.     * [username] = "root" 
13.     * [password] = "1111" 
14.     * [dbname]   = "test" 
15.     * [init] = "SET NAMES 'utf8'; SET group_concat_max_len=65536;" 
16.     *  
17.     * @return class 
18.     */ 
19.    public function __construct(array $conf){ 
20.        parent :: __construct( 
21.            'mysql:host='.$conf['host'].';dbname='.$conf['dbname'],  
22.            $conf['username'],  
23.            $conf['password'] 
24.        ); 
25.  
26.        /* MySQL 前置設定 */ 
27.        foreach($conf['init'] as $query){ 
28.            parent :: exec($query); 
29.        } 
30.  
31.        /*以例外方式處理錯誤*/ 
32.        $this->setAttribute( 
33.            self :: ATTR_ERRMODE,  
34.            self :: ERRMODE_EXCEPTION 
35.        ); 
36.  
37.        /*改寫資料流物件*/ 
38.        $this->setAttribute( 
39.            self :: ATTR_STATEMENT_CLASS,  
40.            array ('DBStatement',array ( 
41.                $this 
42.            ) 
43.        )); 
44.    } 
45.  
46.  
47.    /**===============================================================**/ 
48.    /* 將字串加入引號及反斜線  */ 
49.    public function quote($var = null){ 
50.        if(is_array($var)){ 
51.            foreach ($var as $key => $value) { 
52.                $var[$key] = parent :: quote($value); 
53.            } 
54.            return join(',', $var); 
55.  
56.        }elseif($var === null){ 
57.            return 'NULL'; 
58.        }else{ 
59.            return parent :: quote($var); 
60.        } 
61.    } 
62.  
63.    /* 將 $query 字串與陣列做變數合併  */ 
64.    public function bind($query, array $bind = array ()){ 
65.        foreach($bind as $key => $value){ 
66.            if ($key[0] == '/' && $key[1] == '*') { 
67.                continue; 
68.            } 
69.  
70.            $bind[$key] = $this->quote($value); 
71.        } 
72.  
73.        return strtr($query, $bind); 
74.    } 
75.  
76.  
77.    /**===============================================================**/ 
78.    /*重新封裝 exec*/ 
79.    public function exec($query, array $bind = array ()){ 
80.        $query = $this->bind($query, $bind); 
81.  
82.        $stmt = parent :: query($query); 
83.        $rowCount = $stmt->rowCount(); 
84.        $stmt->closeCursor(); 
85.        $stmt = null; 
86.        return $rowCount; 
87.    } 
88.  
89.    /*重新封裝 query*/ 
90.    public function query($query, array $bind = array ()){ 
91.        $query = $this->bind($query, $bind); 
92.  
93.        $stmt = parent :: query($query); 
94.        $stmt->setFetchMode(self :: $_fetch_mode); 
95.        return $stmt; 
96.    } 
97.  
98.    /*針對僅取得筆數時的便利函數*/ 
99.    public function queryCount($query, array $bind = array ()){ 
100.        $query = $this->bind($query, $bind); 
101.        $query = sprintf('SELECT COUNT(*) FROM (%s)AS QUERYCOUNT', $query); 
102.  
103.        $stmt = parent :: query($query); 
104.        $rowCount = $stmt->fetchColumn(); 
105.        $stmt->closeCursor(); 
106.        $stmt = null; 
107.        return $rowCount; 
108.  
109.    } 
110.  
111. } 
112.  
113.  
114. /*改寫資料流物件*/ 
115. class DBStatement extends PDOStatement{ 
116.    public $dbh; 
117.  
118.    protected function __construct($dbh){ 
119.        $this->dbh = $dbh; 
120.    } 
121. } 

<?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

使用用範例：

原始碼

1. <?php 
2. /* 資料庫連結參數 */ 
3. $conf = array( 
4.    'host'     => "localhost", 
5.    'username' => "root", 
6.    'password' => "1111", 
7.    'dbname'   => "test_db", 
8.    'init' => "SET NAMES 'utf8'; SET group_concat_max_len=65536;" 
9. ); 
10.  
11. /* 初始化資料庫連結介面 */ 
12. $dbAdapter = new DB($conf);  
13.  
14.  
15.  
16.  
17. /*exec 操作*/ 
18. $bind = array(); 
19. $bind['/*prefix*/']   = 'mw_';/*資料表前綴*/ 
20. $bind[':title']   = '文章標題'; 
21. $bind[':content'] = '文章內容'; 
22.  
23. try { 
24.    $dbAdapter->exec(" 
25.        INSERT INTO `/*prefix*/article` 
26.        SET `Title` = :title, 
27.            `Content` = :content, 
28.            `ModifyTime` = NOW()  
29.    ", $bind); 
30.  
31.    echo "成功新增";     
32.  
33. /*錯誤處理*/     
34. } catch (Exception $e) { 
35.    switch ($e->getCode()) { 
36.        case 23000:  
37.            echo "主鍵重複"; break;     
38.  
39.        default: 
40.            echo "新增失敗"; break;     
41.    }     
42.  
43. } 
44.  
45.  
46. /*query 操作*/ 
47. $bind = array(); 
48. $bind['/*prefix*/']   = 'mw_';/*資料表前綴*/ 
49. $bind[':id']   = array('1','2','4'); 
50.  
51. $data = $dbAdapter->query(" 
52.    SELECT `Id`, `Title`, `Content` 
53.    FROM `/*prefix*/article`  
54.    WHERE `Id` IN(:id) 
55. ", $bind)->fetchAll(); 
56.  
57. var_dump($data); 
58.  
59.  
60. /*queryCount 單只要取得查詢的筆數時*/ 
61. $bind = array(); 
62. $bind['/*prefix*/']   = 'mw_';/*資料表前綴*/ 
63. $bind[':id']   = array('1','2','4'); 
64.  
65. $count = $dbAdapter->queryCount(" 
66.    SELECT `Id`, `Title`, `Content` 
67.    FROM `/*prefix*/article`  
68.    WHERE `Id` IN(:id) 
69. ", $bind); 
70.  
71. echo $count; 
72.  
73.  
74. /*bind 取得查詢的 Query 字串*/ 
75. $bind = array(); 
76. $bind['/*prefix*/']   = 'mw_';/*資料表前綴*/ 
77. $bind[':id']   = array('1','2','4'); 
78.  
79. $query_string = $dbAdapter->bind(" 
80.    SELECT `Id`, `Title`, `Content` 
81.    FROM `/*prefix*/article`  
82.    WHERE `Id` IN(:id) 
83. ", $bind); 
84.  
85. echo $query_string; 

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