PDO fetch 模式的回傳結果

PDO 的 fetch 模式功能實在是太方便了，但每次要產生想要的結果都要試太麻煩了，這裡列出可能的組合。

<?php
$dbAdapter = new PDO("mysql:host=localhost;dbname=test", "root", "1234");
$dbAdapter->exec("SET NAMES 'utf8';");




$data = $dbAdapter->query("
    SELECT id, name, method FROM category
")->fetchAll(PDO::FETCH_ASSOC);

//var_dump($data);
/*
array(
    array(
        'id' => '1',
        'name' => 'HBO',
        'method' => 'service',
    ),
    array(
        'id' => '2',
        'name' => '本週新片',
        'method' => 'movie',
    ),
    array(
        'id' => '3',
        'name' => '熱映中',
        'method' => 'movie',
    ),
)
*/



$data = $dbAdapter->query("
    SELECT name, method FROM category
")->fetchAll(PDO::FETCH_COLUMN);

//var_dump($data);
/*
array(
    'HBO',
    '本週新片',
    '熱映中',
)
*/



$data = $dbAdapter->query("
    SELECT id, name, method FROM category
")->fetchAll(PDO::FETCH_UNIQUE | PDO::FETCH_ASSOC);

//var_dump($data);
/*
array(
    '1' => array(
        'name' => 'HBO',
        'method' => 'service',
    ),
    '2' => array(
        'name' => '本週新片',
        'method' => 'movie',
    ),
    '3' => array(
        'name' => '熱映中',
        'method' => 'movie',
    ),
)
*/



$data = $dbAdapter->query("
    SELECT method, id, name FROM category
")->fetchAll(PDO::FETCH_UNIQUE | PDO::FETCH_ASSOC);

//var_dump($data);
/*
array(
    'service' => array(
        'id' => '1',
        'name' => 'HBO',
    ),
    'movie' => array(
        'id' => '3',
        'name' => '熱映中',
    ),
)
*/



$data = $dbAdapter->query("
    SELECT id, name, method FROM category
")->fetchAll(PDO::FETCH_UNIQUE | PDO::FETCH_COLUMN);

//var_dump($data);
/*
array(
    '1' => 'HBO',
    '2' => '本週新片',
    '3' => '熱映中',
)
*/



$data = $dbAdapter->query("
    SELECT method, name, id FROM category
")->fetchAll(PDO::FETCH_UNIQUE | PDO::FETCH_COLUMN);

//var_dump($data);
/*
array(
    'service' => 'HBO',
    'movie' => '熱映中',
)
*/




$data = $dbAdapter->query("
    SELECT method, id, name FROM category
")->fetchAll( PDO::FETCH_ASSOC | PDO::FETCH_GROUP);

//var_dump($data);
/*
array(
    'service' => array(
        array(
            'id' => '1'
            'name' => 'HBO'
        ),
    )
    'movie' => array(
        array(
          'id' => '2'
          'name' => '本週新片'
        ),
        array(
          'id' => '3'
          'name' => '熱映中'
        ),
    )
)
*/




$data = $dbAdapter->query("
    SELECT method, name, id FROM category
")->fetchAll(PDO::FETCH_GROUP | PDO::FETCH_COLUMN);

//var_dump($data);
/*
array(
    'service' => array(
        'HBO'
    ),
    'movie' => array(
        '本週新片'
        '熱映中'
    ),
)
*/





$data = $dbAdapter->query("
    SELECT id, name, method FROM category
")->fetchAll(PDO::FETCH_OBJ);

//var_dump($data);
/*
array(
    stdClass{
        public $id = '1';
        public $name = 'HBO';
        public $method = 'service';
    },
    stdClass{
        public $id = '2';
        public $name = '本週新片';
        public $method = 'movie';
    },
    stdClass{
        public $id = '3';
        public $name = '熱映中';
        public $method = 'movie';
    },
)
*/







class Category_1 {}

$data = $dbAdapter->query("
    SELECT id, name, method FROM category
")->fetchAll(PDO::FETCH_CLASS | PDO::FETCH_PROPS_LATE, "Category_1");

//var_dump($data);
/*
array(
    Category_1{
        public $id = '1';
        public $name = 'HBO';
        public $method = 'service';
    },
    Category_1{
        public $id = '2';
        public $name = '本週新片';
        public $method = 'movie';
    },
    Category_1{
        public $id = '3';
        public $name = '熱映中';
        public $method = 'movie';
    },
),
*/





class Category_2 {
    public $name;
    public $method;

    public function __construct() {}
    public function __set($name, $value ){}
}

$data = $dbAdapter->query("
    SELECT id, name, method FROM category
")->fetchAll(PDO::FETCH_CLASS | PDO::FETCH_PROPS_LATE, "Category_2");

//var_dump($data);
/*
array(
    Category_2{
        public $name = 'HBO';
        public $method = 'service';
    },
    Category_2{
        public $name = '本週新片';
        public $method = 'movie';
    },
    Category_2{
        public $name = '熱映中';
        public $method = 'movie';
    },
)
*/

[Ubuntu] 安裝 Oracle Client 與 PDO_OCI

於 Oracle Database Instant Client 下載 Client/SDK
(Version 10.2.0.4 Instant Client Package - Basic, Instant Client Package - SDK)
oracle-instantclient-basic-10.2.0.4-1.i386.rpm
oracle-instantclient-devel-10.2.0.4-1.i386.rpm

# 安裝套件轉換器
apt-get install alien 

# 轉換 rpm 套件到 deb，並安裝
alien -i oracle-instantclient-basic*.rpm
alien -i oracle-instantclient-devel*.rpm

# 安裝 Apache2,PHP,MySQL
apt-get install apache2 php5 mysql-server php5-mysql libapache2-mod-php5

# 安裝 PEAR 
apt-get install php-pear php5-dev dh-make-php make re2c

# 下載 PDO_OCI 原始檔 
pecl download pdo
pecl download pdo_oci
tar zxvf PDO-1.0.3.tgz
tar zxvf PDO_OCI-1.0.tgz

mkdir -p PDO_OCI-1.0/include/php/ext/
mv PDO-1.0.3 PDO_OCI-1.0/include/php/ext/pdo
cd PDO_OCI-1.0/
phpize
./configure --with-pdo-oci=instantclient,/usr,10.2.0.4

make -j$(grep processor /proc/cpuinfo |wc -l)
make install  # /usr/lib/php5/20xxxxxx+lfs/pdo_oci.so

vim /etc/php5/conf.d/pdo_oci.ini # 建立 pdo_oci.ini, 內容如下:
extension=pdo_oci.so

vim /etc/apache2/envvars # 在最後面加入環境變數, 內容如下: 
export NLS_LANG="TRADITIONAL CHINESE_TAIWAN.UTF8"
export NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"

# 重新啟動 Apache 
service apache2 restart

參考來源：
Debian 安裝設定 PHP 連 Oracle extension 使用 PDO(PDO_OCI)
Oracle Instant Client

[Oracle] PDO-OCI 的 DSN 連接字串

最近重裝我的開發環境，結果連 oracle 一直連不上，出現了以下幾種訊息，最後是因為我的 TNS listener 沒設好，或是我的 DSN 錯誤，整理出我試過的幾個 DSN 連接字串：

ORA-12154: TNS:could not resolve service name
ORA-06401: NETCMN: invalid driver designator
ORA-12514: TNS:listener could not resolve SERVICE_NAME
ORA-12505: TNS:listener could not resolve SID given in connect descriptor

$DSN="oci:dbname=jaxdb;charset=utf-8";

$DSN="oci:dbname=127.0.0.1:1521/jaxdb;charset=utf-8";

$DSN = "oci:dbname=(
    DESCRIPTION = (
        ADDRESS_LIST = (
            ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521)
        )
    )(CONNECT_DATA=
        (SID = jaxdb)
    )
);charset=utf8";
    
$DSN = "oci:dbname=(
    DESCRIPTION = (
        ADDRESS_LIST = (
            ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521)
        )
    )(CONNECT_DATA=
        (SERVICE_NAME = jaxdb)
    )
);charset=utf8";

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

[PHP] PDO 資料庫操作方法(快速範例)

<?php

/*--[PDO::query]-----------------------------------*/
// 建立查詢連結
$stmt = $DB_Link->query(sprintf("
    SELECT id, colour FROM test_table WHERE name=%s
",$name));

// 設定查詢結果的資料格式，之後可以省去 fetch 時的格式設定
$stmt->->setFetchMode(PDO::FETCH_ASSOC);

// 取得查詢結果的列數
$count = $stmt->rowCount ();
/* PDOStatement::rowCount()主要是用於 PDO::query()和PDO::prepare()進行DELETE、INSERT、UPDATE操作影響的結果集，對PDO::exec()方法 和SELECT操作無效。 */

// 取得一列的查詢結果
$row = $stmt->fetch(PDO::FETCH_ASSOC);

// 利用迴圈對每一列做處理
$dataArray = array(); // 結果儲存陣列
while ($row = $stmt->fetch(PDO::FETCH_ASSOC) {
    // 資料處理
    $row['colour'] = $row['colour']? $row['colour'] : '#000';
    // 加儲存陣列
    array_push($dataArray,$row);
}

// 取得一列單一欄位的查詢結果
$column = $stmt->fetchColumn(); // id
$column = $stmt->fetchColumn(1); // colour
/* 當查詢結果之有一個時很好用(SELECT COUNT(*) FROM test_table) */

// 取得所有的查詢結果列
$dataArray = $stmt->fetchAll (PDO::FETCH_ASSOC);


/* PDO::fetch , PDO::fetchAll 選項參數
PDO::FETCH_LAZY
    將每一行結果作為一個對象返回
   
PDO::FETCH_ASSOC
    僅返回以鍵值作為下標的查詢的結果集，名稱相同的數據只返回一個

PDO::FETCH_NAMED
    僅返回以鍵值作為下標的查詢的結果集，名稱相同的數據以數組形式返回
    
PDO::FETCH_NUM
    僅返回以數字作為下標的查詢的結果集
    
PDO::FETCH_BOTH
    同時返回以鍵值和數字作為下標的查詢的結果集(預設)
    
PDO::FETCH_OBJ
    按照對象的形式，類似於以前的 mysql_fetch_object()
    
PDO::FETCH_BOUND
    將PDOStatement::bindParam()和PDOStatement::bindColumn()所綁定的值作為變量名賦值後返回
    
PDO::FETCH_COLUMN
    將返回結果每一列全部集中在一個欄位
    
PDO::FETCH_CLASS
    以 Class 的形式返回結果集

PDO::FETCH_INTO
    將數據合併入一個存在的類中進行返回
    
PDO::FETCH_FUNC
PDO::FETCH_GROUP
PDO::FETCH_UNIQUE
PDO::FETCH_KEY_PAIR
    以首個鍵值下表，後面數字下表的形式返回結果集

PDO::FETCH_CLASSTYPE
PDO::FETCH_SERIALIZE
    表示將數據合併入一個存在的類中并序列化返回

PDO::FETCH_PROPS_LATE
    Available since PHP 5.2.0
*/



/*--[PDO::exec]-----------------------------------*/
$colour="'red'";

// 建立新增請求
$count = $DB_Link->exec(sprintf("
    INSERT INTO test_table(colour) VALUES(%s)
",$colour));
// 取得上次 Insert 時產生的 AUTO_INCREMENT Id
$id = $DB_Link->lastInsertId();

// 建立更新請求
$count = $DB_Link->exec(sprintf("
    UPDATE test_table SET colour = %s WHERE id=1
",$colour));

// 建立刪除請求
$count = $DB_Link->exec(sprintf("
    DELETE FROM test_table WHERE colour = %s LIMIT 10
",$colour));
/*
  PDO::exec 會回傳所影響的列數
  但如果 DELETE 到資料表為空時將回傳 0，而不是所刪除的列數
*/

參考來源：
PHP: PDO::lastInsertId - Manual
PHP: PDOStatement->fetchColumn - Manual
PHP: PDO::exec - Manual
PHP: PDO::query - Manual

PDO (PHP Data Object) 簡易教學 @ wEd2.5
PHP的PDO类教程_PHP技巧
pdo使用简例 - 板子博客

[PHP] PDO 資料庫連接設定(快速範例)

<?php
$hostname = "localhost";
$database = "test_db";
$username = "root";
$password = "0000";


/* DSN :  
mysql:host=localhost;port=3307;dbname=testdb;unix_socket=/tmp/mysql.sock;

pgsql:host=localhost port=5432 dbname=testdb user=bruce password=mypass;

mssql:host=localhost;dbname=testdb
sybase:host=localhost;dbname=testdb
dblib:host=localhost;dbname=testdb

odbc:DRIVER={IBM DB2 ODBC DRIVER};HOSTNAME=localhost;PORT=50000;DATABASE=SAMPLE;PROTOCOL=TCPIP;UID=db2inst1;PWD=ibmdb2;
odbc:Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\\db.mdb;Uid=Admin;

ibm:DRIVER={IBM DB2 ODBC DRIVER};DATABASE=testdb;HOSTNAME=11.22.33.444;PORT=56789;PROTOCOL=TCPIP;

oci:dbname=192.168.10.145/orcl;charset=CL8MSWIN1251

sqlite:/opt/databases/mydb.sq3
sqlite::memory:
sqlite2:/opt/databases/mydb.sq2
sqlite2::memory:
*/

$dsn="mysql:host=$hostname;dbname=$database";
try {
    $DB_Link = new PDO($dsn, $username,$password
        /*,array(
            PDO::ATTR_PERSISTENT => true,  // 開啟 DB 長連接
            PDO::MYSQL_ATTR_INIT_COMMAND =>  // MySQL 前置設定
             "SET NAMES 'utf8'; SET group_concat_max_len=65536;",
            PDO::MYSQL_ATTR_USE_BUFFERED_QUERY=>false // MySQL 查詢緩衝區
        )*/
    ); 
} catch (PDOException $e) {
    // 資料庫連結失敗
    $e->errorInfo ; // 錯誤明細
    $e->getMessage(); // 返回異常資訊
    $e->getPrevious(); // 返回前一個異常
    $e->getCode(); // 返回異常程式碼
    $e->getFile(); // 返回發生異常的檔案名
    $e->getLine(); // 返回發生異常的程式碼行號
    $e->getTrace(); // backtrace() 陣列
    $e->getTraceAsString(); // 已格成化成字串的 getTrace() 資訊    
    
    // 錯誤處理...
}

/*
PDO::ATTR_CASE: 返回的資料欄位名稱設定
    PDO::CASE_LOWER: 欄位名稱全部轉換成小寫
    PDO::CASE_NATURAL: 使用原始欄位名稱(預設)
    PDO:: CASE_UPPER: 欄位名稱全部轉換成大寫
*/
//$DB_Link->setAttribute(PDO::ATTR_CASE,PDO::CASE_NATURAL);


/*
PDO::ATTR_ERRMODE: 錯誤報告
    PDO::ERRMODE_SILENT: 不顯示錯誤信息，只顯示錯誤碼。
    PDO::ERRMODE_WARNING: 顯示警告跟錯誤。
    PDO::ERRMODE_EXCEPTION: 拋出異常。
*/
$DB_Link->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_WARNING);


/*
PDO::ATTR_ORACLE_NULLS : 轉換無效的空字串
    PDO::NULL_NATURAL: 不轉換(預設)。
    PDO::NULL_EMPTY_STRING: 空字串轉換為 NULL。
    PDO::NULL_TO_STRING: NULL 轉換為空字串。
*/
//$DB_Link->setAttribute(PDO::ATTR_ORACLE_NULLS,PDO::NULL_NATURAL);

/*
PDO::ATTR_STRINGIFY_FETCHES:
    Convert numeric values to strings when fetching. Requires bool. 
*/
//$DB_Link->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, true);

    
/*
PDO::ATTR_STATEMENT_CLASS: 變更預設 PDOStatement Class
*/
//class DBStatement extends PDOStatement {}
//$DB_Link->setAttribute(PDO::ATTR_STATEMENT_CLASS,
//     array('DBStatement', array($DB_Link))
//);

    
/*
PDO::ATTR_AUTOCOMMIT: 自動啟動交易處理
    在設置成true的時候，PDO會自動嘗試停止接受委託，開始執行
*/
//$DB_Link->setAttribute(PDO::ATTR_AUTOCOMMIT,true);



// 設定存取的編碼方式
$DB_Link->exec("SET NAMES 'utf8';");

// 設定 GROUP_CONCAT 的最大長度
$DB_Link->exec("SET group_concat_max_len=65536;");

// 關閉查詢快取
//$DB_Link->exec("SET SESSION query_cache_type=OFF;");

參考來源：
PHP: PDO Drivers - Manual
PHP: PDO::__construct - Manual
PHP: PDOException - Manual
PHP: PDO::setAttribute - Manual

牛刀小小試 PHP5中PDO的簡單使用