Accessing Databases with DB - Examining Data Retrieval Convenience Methods

(Page 4 of 7 )

PEAR DB provides methods that make common data retrieval operations easier. These methods combine query() and fetchRow(), allowing you to send a SELECT query to the database and retrieve its results in one step.

DB::getRow()

Use the getRow() method when you want all values in the first or only row returned from a query. getRow() returns an array or an object, depending on the current fetch mode. The default fetch mode is an indexed array:

$row = $dbh->getRow('SELECT flavor,price FROM ice_cream  
       WHERE id = 56');
print "Flavor: $row[0], Price: $row[1]";

Placeholders work with getRow() just as they do with query():

$row = $dbh->getRow('SELECT flavor,price FROM ice_cream 
       WHERE id = ?', array($id));

An alternate fetch mode can be passed as a third argument to getRow():

$row = $dbh->getRow('SELECT flavor,price FROM ice_cream
       WHERE id = ?', array($id),DB_FETCHMODE_OBJECT);
print "Flavor: $row->flavor, Price: $row->price";

Placeholders must be passed to getRow() in an array, just as they must be passed to query().

DB::getAll()

Use getAll() when you want all values in all rows returned from a query. Just like getRow(), getAll() accepts an array of placeholder replacements as a second argument and an optional alternate fetch mode as a third argument. The getAll() function always returns an array, but the type of each element in the array is controlled by the fetch mode. Iterate through the array that getAll() returns to display results:

$results = $dbh->getAll('SELECT flavor,price
           FROM ice_cream');
foreach($results as $row) {
    print "Flavor: $row[0], Price: $row[1]<br>";
}

DB::getOne()

Use getOne() when you want the first value from the first or only row returned from a query. The getOne() function accepts an array of placeholder replacements as an optional second argument. It returns a string containing the retrieved value or NULL if the query returned no results. It also returns NULL if the first value of the first row of the result is NULL:

$flavor = $dbh->getOne('SELECT flavor FROM ice_cream ORDER
           BY price DESC LIMIT 1');
if (is_null($flavor)) {
     print "We don't have any ice cream!";
} else {
     print "Our most expensive flavor is $flavor.";
}

DB::getCol()

Use getCol() when you want the value of a particular column in all the rows returned from a query. The getCol() function accepts a column name or number to return as an optional second argument. If this is not specified, getCol() returns the first column. The function also accepts an array of placeholder replacements as an optional third argument. The retrieved values are returned as an indexed array:

$flavors = $dbh->getCol('SELECT flavor FROM ice_cream');
print "Our flavors: <ul> <li> " . join('<li>',$flavors) 
       ."</ul>";

DB::getAssoc()

Use getAssoc() when you want an entire result set, such as getAll(), but you want to easily access particular rows of the result. The getAssoc() function returns an associative array whose keys are the values of the first column of the query results. If you select two columns, the associative array values are the values of the second column:

$res = $dbh->getAssoc('SELECT flavor,price FROM
       ice_cream');
print "A serving of Heavenly Hash costs: ".$res['Heavenly
       Hash']

If you select more than two columns, the associative array values are themselves arrays of the remaining column values for each row:

$res = $dbh->getAssoc('SELECT id,flavor,price FROM
       ice_cream');
if ($flavor = $res[$_REQUEST['flavor_id']]) {
     print "Your selected flavor is: $flavor[0] with price
            $flavor[1].";
} else {
     print "No flavor has ID $_REQUEST[flavor_id].";
}

To force getAssoc() to return values as arrays instead of scalars when only one column of values is involved, pass true as a second argument when calling the function:

$res = $dbh->getAssoc('SELECT flavor,price FROM
       ice_cream', true);
print "A serving of Heavenly Hash costs: ".$res['Heavenly
       Hash'][0];

The third argument to getAssoc() is an array of values to replace any placeholders in the query:

$res = $dbh->getAssoc('SELECT id,flavor,price FROM
       ice_cream WHERE price > ?', true,array($_REQUEST
       ['min_price']));
if ($flavor = $res[$_REQUEST['flavor_id']]) {
print "Your selected flavor is: $flavor[0] with price
       $flavor[1].";
} else {
print "No flavor has ID $_REQUEST[flavor_id].";
}

Tell getAssoc() to return each array of values as an associative array instead of an indexed array by passing DB_FETCHMODE_ASSOC as a fourth argument:

$res = $dbh->getAssoc('SELECT id,flavor,price FROM
       ice_cream', null, null, DB_FETCHMODE_ASSOC);
if ($flavor = $res[$_REQUEST['flavor_id']]) {
print "Your selected flavor is: $flavor[flavor] with price
       $flavor[price].";
} else {
print "No flavor has ID $_REQUEST[flavor_id].";
}

The getAssoc() function returns an array whose values are easy to display as an HTML <select> widget. Select the columns for the value and label of each option and then use foreach to loop through the array:

$flavors= $dbh->getAssoc('SELECT id,flavor FROM
          ice_cream');
print '<select name="flavor">';
foreach ($flavors as $id => $flavor) {
     print "<option value=\"$id\">$flavor</option>";
}
print '</select>';

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: Understanding Query Information >>
 

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!