2009-11-01

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

0 回應: