Home arrow PHP arrow Page 6 - Accessing Databases with DB

Running a Query Multiple Times - PHP

This chapter discusses the database abstraction layer PEAR DB. This package supplies a standard set of functions for talking to many different kinds of databases (from Essential PHP Modules, Extensions, Tools, by David Sklar, 2004, Apress, ISBN: 1590592808).

TABLE OF CONTENTS:
  1. Accessing Databases with DB
  2. Introducing DSNs
  3. Understanding Quoting and Placeholders
  4. Examining Data Retrieval Convenience Methods
  5. Understanding Query Information
  6. Running a Query Multiple Times
  7. Introducing Sequences
By: David Sklar
Rating: starstarstarstarstar / 14
November 30, 2004

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

Often, a program needs to run a query many times with different values each time. A query that inserts a product into a product catalog is called ten times to insert ten new products into the catalog. Each time, the structure of the query is identical. However, new values such as product name and price must be incorporated into the query on each invocation.

DB::prepare() and DB::execute()

To run a query multiple times with different values each time, use prepare() and execute(). Call prepare() once with placeholders representing the values that change on each query execution. This returns a prepared statement handle. Then, call execute() with the prepared statement handle and each set of values:

$prh = $dbh->prepare('INSERT INTO ice_cream (flavor,price)
       VALUES (?,?)');
$dbh->execute($prh,array('Coffee',1.25));
$dbh->execute($prh,array('Pistachio',2.00));
$dbh->execute($prh,array('Caramel Pecan',1.75));

The prepare() method supports the same set of placeholders that query() does, so you can use ! for unquoted values and & for file contents:

$prh = $dbh->prepare('INSERT INTO ! (flavor,price,image)
       VALUES (?,?,&)');
$dbh->execute($prh,array('frozen_yogurt','Tofu Health
      Crunch',2.50, 'yogurt-tofu-crunch.jpg'));
$dbh->execute($prh,array
     ('ice_cream','Vanilla',1.40,'delicious-vanilla.jpg'));

These methods can be used for SELECT queries as well. Each successful execute() of a SELECT query returns a statement handle. These are the same statement handles that query() returns:

$prh = $dbh->prepare('SELECT flavor FROM !');
$res = $dbh->execute($prh,'frozen_yogurt');
print_flavors('Frozen Yogurt',$res);
$res = $dbh->execute($prh,'ice_cream');
print_flavors('Ice Cream',$res);

DB::autoPrepare() and DB::autoExecute()

While prepare() and execute() make it easier to run the same query multiple times, autoPrepare() and autoExecute() make it easier to build queries from arrays of field names and values. The autoPrepare() method returns a prepared statement handle just like prepare(). Instead of passing it an SQL query with placeholders, however, you pass it a table name, an array of field names, and amode. For example, these calls to autoPrepare() and prepare() return identical statement handles:

$dbh->autoPrepare('ice_cream',array('flavor','price'),
      DB_AUTOQUERY_INSERT);
$dbh->prepare("INSERT INTO ice_cream ('flavor','price') 
      VALUES (?,?)");

The first argument to autoPrepare() is the name of the table to use. The second argument is an array of field names. The third argument tells autoPrepare() whether to prepare an INSERT or UPDATE query. To prepare an UPDATE query, use DB_AUTOQUERY_UPDATE:

$dbh->autoPrepare('ice_cream',array('flavor','price'),
      DB_AUTOQUERY_UPDATE);

This returns a prepared statement handle as if you had called this:

$dbh->prepare('UPDATE ice_cream SET flavor = ?,
      price = ?');

To include a WHERE clause in an UPDATE query generated by autoPrepare(), pass it as a fourth argument to autoPrepare():

$dbh->autoPrepare('ice_cream',array('flavor','price'),
      DB_AUTOQUERY_UPDATE, 'price < 10');

This returns a prepared statement handle as if you had called this:

$dbh->prepare('UPDATE ice_cream SET flavor = ?, price = ?
      WHERE price < 10');

The autoExecute() method takes autoPrepare() one step further. It prepares a query but also executes it with an array of values. Instead of an array of field names such as autoPrepare(), autoExecute() takes an associative array of fields and values:

$dbh->autoExecute('ice_cream',array('flavor' => 'Blueberry'
      , 'price' => 3.00), DB_AUTOQUERY_INSERT);

This prepares and executes a query as if you had called this:

$prh = $dbh->prepare('INSERT INTO ice_cream (flavor,price)
       VALUES (?,?)');
$dbh->execute($prh, array('Blueberry',3.00));

The autoExecute() method runs UPDATE queries just like autoPrepare():

$dbh->autoExecute('ice_cream',
                            array('flavor' => 'Blueberry', 'price' => 3.00),
                            DB_AUTOQUERY_UPDATE);

This prepares and executes a query as if you had called this:

$prh = $dbh->prepare('UPDATE ice_cream SET flavor = ?,
       price = ?');
$dbh->execute($prh, array('Blueberry',3.00));

The autoExecute() method also accepts a WHERE clause just like autoPrepare():

$dbh->autoExecute('ice_cream',array('flavor' =>
      'Blueberry', 'price' => 3.00),
      DB_AUTOQUERY_UPDATE,'id = 23');

This prepares and executes a query as if you had called this:

$prh = $dbh->prepare('UPDATE ice_cream SET flavor = ?,
       price = ? WHERE id = 23');
$dbh->execute($prh, array('Blueberry',3.00));

The autoPrepare() and autoExecute() methods are especially useful for saving information from a Web form that has many fields. Define those fields in an array, and use autoExecute() to save information from the $_REQUEST array into the database. If the fields in the form change, you have to only update the line of code that defines the $fields array, and the query is automatically changed as well:

$fields = array('flavor','price','id','rating');
$values = array();
foreach ($fields as $f) { $values[$f] = $_REQUEST[$f]; }
$dbh->autoExecute('ice_cream',$values,DB_AUTOQUERY_INSERT);

This chapter is from Essential PHP Modules, Extensions, Tools, by David Sklar (Apress, 2004, ISBN: 1590592808). Check it out at your favorite bookstore today.

Buy this book now.



 
 
>>> More PHP Articles          >>> More By David Sklar
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

PHP ARTICLES

- 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: