Accessing Databases with DB - Running a Query Multiple Times

(Page 6 of 7 )

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.

Next: Introducing Sequences >>
 

More PHP Articles
More By David Sklar

Comments On: Accessing Databases with DB

· Is it just me or is the syntax for PEAR DB access, as well as actual calls to the DB...    · dbase -> dBasefbsql -> FrontBaseibase -> InterBaseifx -> Informixmsql ...    · Perhaps it's a Perl DBI rip off, but a least the Perl DBI isn't so bug ridden and...   >>> Post your comment now!