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