Page 251 - Open Soource Technologies 304.indd

Unit 10: Database

The values array must be numerically indexed from 0 and have values that are arrays of values Notes
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 book-insertions code is:
$books = array(array(‘Foundation’, 1951),
array(‘Second Foundation’, 1953),
array(‘Foundation and Empire’, 1952));
$compiled = $q->prepare(‘INSERT INTO books (title,pub_year) VALUES (?,?)’);
$db->insertMultiple($compiled, $books);

10.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 ]);

$when = $db->getOne(“SELECT avg(pub_year) FROM books”);
if (DB::isError($when)) {
die($when->getMessage( ));
}

echo “The average book in the library was published in $when”;
The average book in the library was published in 2010.
The getRow( ) method returns the first row of data returned by an SQL query:
$row = $db->getRow(SQL [, values ]]);
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.
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 bookid,title FROM books ORDER BY pub_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.

LOVELY PROFESSIONAL UNIVERSITY 245