Home arrow PHP arrow Page 5 - Databases: Finishing a Listing Service

PDO and prepared statements - PHP

Concluding our discussion of databases and PHP, we'll finish building the example that we started last week. This article is excerpted from chapter eight of the book Programming PHP, Second Edition, written by Kevin Tatroe, Rasmus Lerdorf, and Peter MacIntyre (O'Reilly, 2006; ISBN: 0596006810). Copyright © 2006 O'Reilly Media, Inc. All rights reserved. Used with permission from the publisher. Available from booksellers or direct from O'Reilly Media.

  1. Databases: Finishing a Listing Service
  2. Adding a Business
  3. Displaying the Database
  4. PHP Data Objects
  5. PDO and prepared statements
By: O'Reilly Media
Rating: starstarstarstarstar / 2
July 05, 2007

print this article



PDO also allows for what is known as prepared statements. This is done with PDO calls in stages or steps. Consider the following code:

  $stmt = $ConnHandle->prepare( "SELECT * FROM books");

  while ($row = $stmt->fetch()) {   // gets rows one at a time
print_r ($row);
// or do something more meaningful with each returned row
  $stmt = null;

In this code, we “prepare” the SQL code then “execute” it. Next, we cycle through the result with thewhilecode and, finally, we release the result object by assigningnullto it. This may not look all that powerful in this simple example, but there are other features that can be used with prepared statements. Now, consider this code:

  $stmt = $db->prepare("INSERT INTO authors"
. "(authorid, title, ISBN, pub_year)"
. "VALUES (:authorid, :title, :ISBN, :pub_year)");
  $stmt->execute(array('authorid'  => 4,
'title'     => 'Foundation',
'ISBN'      => 0-553-80371-9,
'pub_year' => 1951)

Here, we prepare the SQL statement with four named placeholders:authorid, title,ISBN,  andpub_year. These happen to be the same names as the columns in the database. This is done only for clarity; the placeholder names can be anything that is meaningful to you. In the execute call, we replace these placeholders with the actual data that we want to use in this particular query. One of the advantages of prepared statements is that you can execute the same SQL command and pass in different values through the array each time. You can also do this type of statement preparation with positional placeholders (not actually naming them), signified by a?, which is the positional item to be replaced. Look at the following variation of the previous the code:

  $stmt = $db->prepare("INSERT INTO authors"
. "(authorid, title, ISBN, pub_year)"
. "VALUES (?,?,?,?)");

  $stmt->execute(array(4, 'Foundation', 0-553-80371-9, 1951));

This code accomplishes the same thing but with less code, as the value area of the SQL statement does not name the elements to be replaced, and, therefore, the array in the execute statement only needs to send in the raw data and no names. You just have to be sure about the position of the data that you are sending into the prepared statement.

This was just a brief overview of what the new PDO library will be able to do for you in the database realm of PHP. If you want to explore this new library in more depth, be sure to do your research and testing before using it in a production environment. You can find information on PDO at http://ca.php.net/pdo.

>>> More PHP Articles          >>> More By O'Reilly Media

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Hackers Compromise PHP Sites to Launch Attac...
- Red Hat, Zend Form OpenShift PaaS Alliance
- PHP IDE News
- BCD, Zend Extend PHP Partnership
- PHP FAQ Highlight
- PHP Creator Didn't Set Out to Create a Langu...
- PHP Trends Revealed in Zend Study
- PHP: Best Methods for Running Scheduled Jobs
- PHP Array Functions: array_change_key_case
- PHP array_combine Function
- PHP array_chunk Function
- PHP Closures as View Helpers: Lazy-Loading F...
- Using PHP Closures as View Helpers
- PHP File and Operating System Program Execut...
- PHP: Effects of Wrapping Code in Class Const...

Developer Shed Affiliates


Dev Shed Tutorial Topics: