In this third part of a five-part series, you'll learn how to execute queries and use statements with PDO. 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).
Executing Queries
PDO offers several methods for executing queries, with each attuned to executing a specific query type in the most efficient way possible. The following list breaks down each query type:
Executing a query with no result set: When executing queries such asINSERT,UPDATE, andDELETE, no result set is returned. In such cases, theexec()method will return the number of rows affected by the query.
Executing a query a single time: When executing a query that returns a result set, or when the number of affected rows is irrelevant, you should use thequery()method.
Executing a query multiple times: Although it’s possible to execute a query numerous times using awhileloop and thequery()method, passing in different column values for each iteration, doing so is more efficient using a prepared statement.
Adding, Modifying, and Deleting Table Data
Chances are your applications will provide some way to add, modify, and delete data. To do this you would pass a query to theexec()method, which executes a query and returns the number of rows affected by it. Its prototype follows:
int PDO::exec(string query)
Consider the following example:
$query = "UPDATE products SET title='Painful Aftershave' WHERE sku='ZP457321'"; $affected = $dbh->exec($query); echo "Total rows affected: $affected";
Based on the sample data, this example would return the following:
-------------------------------------------- Total rows affected: 1 --------------------------------------------
Note that this method shouldn’t be used in conjunction withSELECTqueries; instead, thequery()method should be used for these purposes, which is introduced next.
Selecting Table Data
Thequery()method executes a query, returning the data as aPDOStatementobject. Its prototype follows:
PDOStatement query(string query)
An example follows:
$query = "SELECT sku, title FROM products ORDER BY product_id"; foreach ($dbh->query($query) AS $row) { $sku = $row['sku']; $title = $row['title']; printf("Product: %s (%s) <br />", $title, $sku); }
Based on the sample data introduced earlier in the chapter, this example produces the following:
/ 3 