home | O'Reilly's CD bookshelfs | FreeBSD | Linux | Cisco | Cisco Exam  


Programming PHPProgramming PHPSearch this book

8.4. Advanced Database Techniques

PEAR DB goes beyond the database primitives shown earlier; it provides several shortcut functions for fetching result rows, as well as a unique row ID system and separate prepare/execute steps that can improve the performance of repeated queries.

8.4.2. Prepare/Execute

When issuing the same query repeatedly, it can be more efficient to compile the query once and then execute it multiple times, using the prepare( ) , execute( ), and executeMultiple( ) methods.

The first step is to call prepare( ) on the query:

$compiled = $db->prepare(SQL);

This returns a compiled query object. The execute( ) method fills in any placeholders in the query and sends it to the RDBMS:

$response = $db->execute(compiled, values);

The values array contains the values for the placeholders in the query. The return value is either a query response object, or DB_ERROR if an error occurred.

For example, we could insert multiple values into the movies table like this:

$movies = array(array('Dr No', 1962),
                array('Goldfinger', 1965),
                array('Thunderball', 1965));
$compiled = $q->prepare('INSERT INTO movies (title,year) VALUES (?,?)');
foreach ($movies as $movie) {
  $db->execute($compiled, $movie);
}

The executeMultiple( ) method takes a two-dimensional array of values to insert:

$responses = $db->executeMultiple(compiled, values);

The values array must be numerically indexed from 0 and have values that are arrays of values to insert. The compiled query is executed once for every entry in values, and the query responses are collected in $responses.

A better way to write the movie-insertions code is:

$movies = array(array('Dr No', 1962),
                array('Goldfinger', 1965),
                array('Thunderball', 1965));
$compiled = $q->prepare('INSERT INTO movies (title,year) VALUES (?,?)');
$db->insertMultiple($compiled, $movies);

8.4.3. Shortcuts

PEAR DB provides a number of methods that perform a query and fetch the results in one step: getOne( ) , getRow( ), getCol( ), getAssoc( ), and getAll( ). All of these methods permit placeholders.

The getOne( ) method fetches the first column of the first row of data returned by an SQL query:

$value = $db->getOne(SQL [, values ]);

For example:

$when = $db->getOne("SELECT avg(year) FROM movies");
if (DB::isError($when)) {
  die($when->getMessage( ));
}
echo "The average James Bond movie was made in $when";
The average James Bond movie was made in 1977

The getRow( ) method returns the first row of data returned by an SQL query:

$row = $db->getRow(SQL [, values ]]);

This is useful if you know only one row will be returned. For example:

list($title, $actor) = $db->getRow(
  "SELECT movies.title,actors.name FROM movies,actors 
   WHERE movies.year=1977 AND movies.actor=actors.id");
echo "($title, starring $actor)";
(The Spy Who Loved Me, starring Roger Moore)

The getCol( ) method returns a single column from the data returned by an SQL query:

$col = $db->getCol(SQL [, column [, values ]]);

The column parameter can be either a number (0, the default, is the first column), or the column name.

For example, this fetches the names of all the Bond movies in the database, ordered by the year they were released:

$titles = $db->getAll("SELECT title FROM movies ORDER BY year ASC");
foreach ($titles as $title) {
  echo "$title\n";
}
Dr No
From Russia With Love
Goldfinger
...

The getAll( ) method returns an array of all the rows returned by the query:

$all = $db->getAll(SQL [, values [, fetchmode ]]);

For example, the following code builds a select box containing the names of the movies. The ID of the selected movie is submitted as the parameter value.

$results = $db->getAll("SELECT id,title FROM movies ORDER BY year ASC");
echo "<select name='movie'>\n";
foreach ($results as $result) {
  echo "<option value={$result[0]}>{$result[1]}</option>\n";
}
echo "</select>";

All the get*( ) methods return DB_ERROR when an error occurs.



Library Navigation Links

Copyright © 2003 O'Reilly & Associates. All rights reserved.