Zend Framework教程-Zend_Db-數據庫操作2-Zend_Db_Statement

一、Zend_Db_Statement官方文檔翻譯。

zend此部分文章，沒有給出中文翻譯，其實如果少有pdo開發經驗的人，也不難看懂。這里做粗略的補全，如有歧義，可以參考原文理解。

Zend_Db_Statement

In addition to convenient methods such as fetchAll() and insert() documented in Zend_Db_Adapter, you can use a statement object to gain more options for running queries and fetching result sets. This section describes how to get an instance of a statement object, and how to use its methods.
Zend_Db_Statement is based on the PDOStatement object in the ? PHP Data Objects extension.
 

除了使用Zend_Db_Adapter提供的 fetchAll() ， insert() 之外，你還可以使用Statement 對象執行sql查詢和獲取結果集。這里介紹如何獲取一個statement 對象實例，以及如何使用statement 對象提供的方法。

Zend_Db_Statement是基于PDO的PDOStatement對象的。

Creating a Statement


Typically, a statement object is returned by the query() method of the database Adapter class. This method is a general way to prepare any SQL statement. The first argument is a string containing an SQL statement. The optional second argument is an array of values to bind to parameter placeholders in the SQL string.

通常，可以通過Zend_Db_Adapter提供的query()方法來獲取一個Statement對象。query()是一個執行預處理SQL語句的常見方法。

第一個參數是一個SQL語句字符串。

第二個可選的參數是一個數組。數組的值和SQL字符串中的參數占位符是一一對應的。

例如#1 Creating a SQL statement object with query()

$stmt = $db->query(            'SELECT * FROM bugs WHERE reported_by = ? AND bug_status = ?',            array('goofy', 'FIXED')  );

The statement object corresponds to a SQL statement that has been prepared, and executed once with the bind-values specified. If the statement was a SELECT query or other type of statement that returns a result set, it is now ready to fetch results.
You can create a statement with its constructor, but this is less typical usage. There is no factory method to create this object, so you need to load the specific statement class and call its constructor. Pass the Adapter object as the first argument, and a string containing an SQL statement as the second argument. The statement is prepared, but not executed.

如果是select查詢或者其它有結果集返回的語句，當statement 完成預處理，并綁定值后，就可以獲取結果集。

你可以使用statement 的構造方法創建一個statement ，但是很少使用這種方式，也沒有工廠模式來創建statement 對象。所以你需要通過數據庫特定的實現類來實例化statement 。

第一個參數是一個適配器對象；

第二個參數是包含一個SQL語句的字符。statement 只是預定義，但是不會被執行。 

Example #2 Using a SQL statement constructor

$sql = 'SELECT * FROM bugs WHERE reported_by = ? AND bug_status = ?'; $stmt = new Zend_Db_Statement_Mysqli($db, $sql);Executing a Statement

You need to execute a statement object if you create it using its constructor, or if you want to execute the same statement multiple times. Use the execute() method of the statement object. The single argument is an array of value to bind to parameter placeholders in the statement.
If you use positional parameters, or those that are marked with a question mark symbol ('?'), pass the bind values in a plain array.

當使用statement 構造函數創建statement 對象 之后，你可以多長使用相同的statement 對象。

使用Statement對象的execute() 方法時，唯一的參數是傳遞一個數組，數組的值會作為參數綁定到語句中對應的占位符。

如果是使用的positional 參數，或者被問號標記的地方。可以通過普通的數組傳入值即可。

Example #3 Executing a statement with positional parameters

$sql = 'SELECT * FROM bugs WHERE reported_by = ? AND bug_status = ?';$stmt = new Zend_Db_Statement_Mysqli($db, $sql);$stmt->execute(array('goofy', 'FIXED'));

If you use named parameters, or those that are indicated by a string identifier preceded by a colon character (':'), pass the bind values in an associative array. The keys of this array should match the parameter names.
如果你使用named 參數，或者使用“:”冒號之前的字符串標識符，可以通過一個關聯數組綁定值。關聯數組的值對應參數的名稱
Example #4 Executing a statement with named parameters

$sql = 'SELECT * FROM bugs WHERE ' .       'reported_by = :reporter AND bug_status = :status'; $stmt = new Zend_Db_Statement_Mysqli($db, $sql); $stmt->execute(array(':reporter' => 'goofy', ':status' => 'FIXED'));

貌似這種使用方法是錯誤的，不是用法有誤，就是真的有bug了。

PDO statements support both positional parameters and named parameters, but not both types in a single SQL statement. Some of the Zend_Db_Statement classes for non-PDO extensions may support only one type of parameter or the other.

PDO statements 支持positional 參數和named 參數，但是一個sql語句中不能同時使用兩種方式。

Fetching Results from a SELECT Statement


You can call methods on the statement object to retrieve rows from SQL statements that produce result set. SELECT, SHOW, DESCRIBE and EXPLAIN are examples of statements that produce a result set. INSERT, UPDATE, and DELETE are examples of statements that don't produce a result set. You can execute the latter SQL statements using Zend_Db_Statement, but you cannot call methods to fetch rows of results from them.

你可以調用Statement對象中的方法來檢索SQL statements 返回的結果集。

SELECT，SHOW，DESCRIBE 和EXPLAIN 會產生一個結果集。 INSERT，UPDATE和DELETE不會產生結果集。您可以使用Zend_Db_Statement執行后者的SQL語句，但你不能調用Statement對象中的方法來獲取結果集。
Fetching a Single Row from a Result Set


To retrieve one row from the result set, use the fetch() method of the statement object. All three arguments of this method are optional:
Fetch style is the first argument. This controls the structure in which the row is returned. See this chapter for a description of the valid values and the corresponding data formats.
Cursor orientation is the second argument. The default is Zend_Db::FETCH_ORI_NEXT, which simply means that each call to fetch() returns the next row in the result set, in the order returned by the RDBMS.
Offset is the third argument. If the cursor orientation is Zend_Db::FETCH_ORI_ABS, then the offset number is the ordinal number of the row to return. If the cursor orientation is Zend_Db::FETCH_ORI_REL, then the offset number is relative to the cursor position before fetch() was called.
fetch() returns FALSE if all rows of the result set have been fetched.


可以使用statement 對象的fetch() 方法來獲取結果集的一行數據。該方法可以使用三個可選參數：
第一個參數用于設置返回結果集的格式。
第二個參數用于游標定位。默認值是Zend_Db::FETCH_ORI_NEXT。表示 fetch() 每次返回RDBMS結果集的下一行
第三個參數是Offset 。如果游標定位方式采用的 Zend_Db::FETCH_ORI_ABS。offset 偏移量用于定位到指定行。如果游標定位方式采用的是Zend_Db::FETCH_ORI_REL。offset偏移量是相對于當前游標在 fetch()調用之前的位置


fetch() 返回FALSE ，表示結果集已被取出
Example #5 Using fetch() in a loop

$stmt = $db->query('SELECT * FROM bugs'); while ($row = $stmt->fetch()) {    echo $row['bug_description'];}

可以參考php文檔的  PDOStatement::fetch().

Fetching a Complete Result Set

To retrieve all the rows of the result set in one step, use the fetchAll() method. This is equivalent to calling the fetch() method in a loop and returning all the rows in an array. The fetchAll() method accepts two arguments. The first is the fetch style, as described above, and the second indicates the number of the column to return, when the fetch style is Zend_Db::FETCH_COLUMN.

如果要一次性獲取結果集的所有數據，可以使用 fetchAll() 方法。相當于調用fetch()循環獲取所有行。fetchAll() 方法接受兩個參數，第一個參數用于指定獲取方式，如上所述。當采用Zend_Db::FETCH_COLUMN獲取數據是，第二個參數用于表示獲取指定的列
Example #6 Using fetchAll()

$stmt = $db->query('SELECT * FROM bugs'); $rows = $stmt->fetchAll(); echo $rows[0]['bug_description'];

可以參考php文檔的 PDOStatement::fetchAll().

Changing the Fetch Mode


By default, the statement object returns rows of the result set as associative arrays, mapping column names to column values. You can specify a different format for the statement class to return rows, just as you can in the Adapter class. You can use the setFetchMode() method of the statement object to specify the fetch mode. Specify the fetch mode using Zend_Db class constants FETCH_ASSOC, FETCH_NUM, FETCH_BOTH, FETCH_COLUMN, and FETCH_OBJ. See this chapter for more information on these modes. Subsequent calls to the statement methods fetch() or fetchAll() use the fetch mode that you specify.
默認情況下，statement 對象采用關聯數組返回結果集，列名對應列的值。你可以指定返回結果集的格式，類似你可以在適配器類中使用setFetchMode() 方法，來指定抓取模式。可以使用Zend_Db的類常量FETCH_ASSOC，FETCH_NUM，FETCH_BOTH，FETCH_COLUMN，FETCH_OBJ來設置抓取模式。設置之后，后續調用 fetch() 或者fetchAll() 將使用指定的抓取模式。
Example #7 Setting the fetch mode

$stmt = $db->query('SELECT * FROM bugs'); $stmt->setFetchMode(Zend_Db::FETCH_NUM); $rows = $stmt->fetchAll(); echo $rows[0][0];

可以參考php文檔的  PDOStatement::setFetchMode().

Fetching a Single Column from a Result Set


To return a single column from the next row of the result set, use fetchColumn(). The optional argument is the integer index of the column, and it defaults to 0. This method returns a scalar value, or FALSE if all rows of the result set have been fetched.
Note this method operates differently than the fetchCol() method of the Adapter class. The fetchColumn() method of a statement returns a single value from one row. The fetchCol() method of an adapter returns an array of values, taken from the first column of all rows of the result set.


從結果集的下一行獲取一列，可以使用fetchColumn()。可選參數是指定列的數字索引，默認值是0。該方法可以返回游標對應的值，如果結果集所以行被取出，會返回FALSE 。
注意：此方法不同于是適配器類的fetchCol() 方法。該fetchColumn() 方法返回從一行的單個值。而 fetchCol() 方法返回 結果集的所有行中第一列的值數組集合。


Example #8 Using fetchColumn()

$stmt = $db->query('SELECT bug_id, bug_description, bug_status FROM bugs'); $bug_status = $stmt->fetchColumn(2);

可以參考php文檔的 PDOStatement::fetchColumn().

Fetching a Row as an Object


To retrieve a row from the result set structured as an object, use the fetchObject(). This method takes two optional arguments. The first argument is a string that names the class name of the object to return; the default is 'stdClass'. The second argument is an array of values that will be passed to the constructor of that class.
如果想把查詢語句返回的一行作為一個對象返回，可以使用 fetchObject()。這個方法有兩個可選參數。第一個參數是返回對象的類名，默認是stdClass。第二個參數是個數組，數組的值可以作為此類的構造器參數
Example #9 Using fetchObject()

$stmt = $db->query('SELECT bug_id, bug_description, bug_status FROM bugs'); $obj = $stmt->fetchObject(); echo $obj->bug_description;

可以參考php文檔的? PDOStatement::fetchObject(). 章節說明。

二、Statement 舉例

    public function indexAction()    {        require_once 'Zend/Db.php';		$params = array ('host'     => '127.0.0.1',		                 'username' => 'root',		                 'password' => '',		                 'dbname'   => 'test');				$db = Zend_Db::factory('PDO_MYSQL', $params);		//fetch，占位符		$stmt = $db->query(				'SELECT * FROM user WHERE user_name = ? AND user_email = ?',				array('user1', 'user1@a.com')		);		while ($row = $stmt->fetch()) {			var_dump($row);			echo "";		}		echo "";		//fetchAll ， named參數		$stmt = $db->query(				'SELECT * FROM user WHERE user_name = :username AND user_email = :useremail ',				array(':username'=>'user3', ':useremail'=>'user3@a.com')		);		$rows = $stmt->fetchAll();		var_dump($rows);		echo "";				echo "";		//數字索引		$stmt = $db->query(				'SELECT * FROM user WHERE user_name = :username AND user_email = :useremail ',				array(':username'=>'user3', ':useremail'=>'user3@a.com')		);		$stmt->setFetchMode(Zend_Db::FETCH_NUM);				$rows = $stmt->fetchAll();		var_dump($rows);		echo "";		//返回對象				echo "";		$stmt = $db->query(				'SELECT user_name FROM user WHERE user_name = ? AND user_email = ?',				array('user3', 'user3@a.com'));				$obj = $stmt->fetchObject(); 		var_dump($obj);		echo "";				//獲取指定列		echo "";		$stmt = $db->query('SELECT user_id, user_name FROM user');				$user_name = $stmt->fetchColumn(1);		var_dump($user_name);		echo "";				 		exit;    }