In this conclusion to a five-part series on PDO, you'll learn various useful methods, such as fetchAll(), fetchColumn(), and more. This article is excerpted from chapter 23 of the book Beginning PHP and Oracle: From Novice to Professional, written by W. Jason Gilmore and Bob Bryla (Apress; ISBN: 1590597702).
Simultaneously Returning All Result Set Rows
ThefetchAll()method works in a fashion quite similar tofetch(), except that a single call to it will result in all rows in the result set being retrieved and assigned to the returned array. Its prototype follows:
array PDOStatement::fetchAll([int fetch_style])
The way in which the retrieved columns are referenced depends upon how the optionalfetch_styleparameter is set, which by default is set toPDO_FETCH_BOTH. See the preceding section regarding thefetch()method for a complete listing of all availablefetch_stylevalues.
The following example produces the same result as the example provided in thefetch()introduction but this time depends onfetchAll()to ready the data for output:
// Execute the query $stmt = $dbh->query("SELECT sku, title FROM products ORDER BY title");
// Retrieve all of the rows $rows = $stmt->fetchAll();
As to whether you choose to usefetchAll()overfetch(), it seems largely a matter of convenience. However, keep in mind that usingfetchAll()in conjunction with particularly large result sets could place a large burden on the system, both in terms of database server resources and network bandwidth.
Fetching a Single Column
ThefetchColumn()method returns a single column value located in the next row of the result set. Its prototype follows:
The column reference, assigned tocolumn_number, must be specified according to its numerical offset in the row, which begins at zero. If no value is set,fetchColumn()returns the value found in the first column. Oddly enough, it’s impossible to retrieve more than one column in the same row using this method, as each call will move the row pointer to the next position; therefore, consider usingfetch()should you need to do so.
The following example both demonstratesfetchColumn()and shows how subsequent calls to the method will move the row pointer:
// Execute the query $result = $dbh->query("SELECT sku, title FROM products ORDER BY title");
// Fetch the first row first column $sku = $result->fetchColumn(0);
// Fetch the second row second column $name = $result->fetchColumn(1);
// Output the data. echo "Product: $title ($sku)";
The resulting output follows. Note that the product name and SKU don’t correspond to the correct values as provided in the sample table because, as mentioned, the row pointer advances with each call tofetchColumn(), therefore be wary when using this method:
In the previous section you learned how to set thefetch_style parameter in the fetch() and fetchAll()methods to control how the result set columns will be made available to your script. You were probably intrigued by thePDO_FETCH_BOUNDsetting because it seems to enable you to avoid an additional step altogether when retrieving column values and instead just assign them automatically to predefined variables. Indeed this is the case, and it’s accomplished using thebindColumn()method, introduced next.
Binding a Column Name
ThebindColumn()method is used to match a column name to a desired variable name, which, upon each row retrieval, will result in the corresponding column value being automatically assigned to the variable. Its prototype follows:
boolean PDOStatement::bindColumn(mixed column, mixed ¶m [, int type [, int maxlen [, mixed driver_options]]])
Thecolumn parameter specifies the column offset in the row, whereas the ¶mparameter defines the name of the corresponding variable. You can set constraints on the variable value by defining its type using thetypeparameter and limiting itslengthusing themaxlenparameter.
Sixtypeparameter values are supported. See the earlier introduction tobindParam()for a complete listing.
The following example selects theskuandname columns from theproductstable whereproduct_idequals 1, and binds the results according to a numerical offset and associative mapping, respectively:
// Connect to the database server $dbh = new PDO("oci:dbname=xe", "chapter23", "secret");
// Create and prepare the query $query = "SELECT sku, title FROM products WHERE product_id=1"; $stmt = $dbh->prepare($query); $stmt->execute();
// Bind according to column offset $stmt->bindColumn(1, $sku);
// Bind according to column title $stmt->bindColumn('title', $title);
// Fetch the row $row = $stmt->fetch(PDO::FETCH_BOUND);
// Output the data printf("Product: %s (%s)", $title, $sku);
/ 6 