Executing Database Queries with PDO - Prepared Statements

(Page 2 of 2 )

Each time a query is sent to the Oracle server, the query syntax must be parsed to ensure a proper structure and to ready it for execution. This is a necessary step of the process, and it does incur some overhead. Doing so once is a necessity, but what if you’re repeatedly executing the same query, only changing the column values as you might do when batch-inserting several rows? A prepared statement will eliminate this additional overhead by caching the query syntax and execution process to the server, and traveling to and from the client only to retrieve the changing column value(s).

PDO offers prepared-statement capabilities for those databases supporting this feature. Because Oracle supports prepared statements, you’re free to take advantage of this feature. Prepared statements are accomplished using two methods,prepare(), which is responsible to ready the query for execution, andexecute(), which is used to repeatedly execute the query using a provided set of column parameters. These parameters can be provided toexecute()either explicitly by passing them into the method as an array, or by using bound parameters assigned using thebindParam()method. All three of these methods are introduced next.

Using Prepared Statements

Theprepare()method is responsible for readying a query for execution. Its prototype follows:

PDOStatement PDO::prepare(string query [, array driver_options])

A query intended for use as a prepared statement looks a bit different from those you might be used to because placeholders must be used instead of actual column values for those that will change across execution iterations. Two syntax variations are supported, named parameters and question mark parameters. For example, a query using the former variation might look like this:

INSERT INTO products SET product_id = :productid, sku = :sku, title = :title;

while the same query using the latter variation would look like this:

INSERT INTO products SET product_id = ?, sku = ?, title = ?;

The variation you choose is entirely a matter of preference, although perhaps the former is a tad more explicit. For this reason, this variation will be used in relevant examples. To begin, let’s useprepare()to ready a query for iterative execution:

// Connect to the database
$dbh = new PDO("oci:dbname=xe", "chapter23", "secret");

// Create the query
$query = "INSERT INTO products (product_id, sku,title)
          VALUES (:productid, :sku,:title)";

// Prepare the query
$stmt = $dbh->prepare($query);

Once the query is prepared, it must be executed. This is accomplished by theexecute()method, introduced next.

In addition to the query, you can also pass along database driver–specific options via thedriver_optionsparameter. See the PHP manual for more information about these options.

Please check back next week for the next part of this article.

DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware.

blog comments powered by Disqus