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