10.5. Querying a SQL Database10.5.2. SolutionUse DB::query( ) from PEAR DB to send the SQL query to the database, and then DB_Result::fetchRow( ) or DB_Result::fetchInto( ) to retrieve each row of the result: // using fetchRow() $sth = $dbh->query("SELECT sign FROM zodiac WHERE element LIKE 'fire'"); if (DB::isError($sth)) { die($sth->getMessage()); } while($row = $sth->fetchRow()) { print $row[0]."\n"; } // using fetchInto() $sth = $dbh->query("SELECT sign FROM zodiac WHERE element LIKE 'fire'"); if (DB::isError($sth)) { die($sth->getMessage()); } while($sth->fetchInto($row)) { print $row[0]."\n"; } 10.5.3. DiscussionThe fetchRow( ) method returns data, while fetchInto( ) puts the data into a variable you pass it. Both fetchRow( ) and fetchInto( ) return NULL when no more rows are available. If either encounter an error when retrieving a row, they return a DB_Error object, just as the DB::connect( ) and DB::query( ) methods do. You can insert a check for this inside your loop: while($row = $sth->fetchRow()) { if (DB::isError($row)) { die($row->getMessage()); } print $row[0]."\n"; } If magic_quotes_gpc is on, you can use form variables directly in your queries: $sth = $dbh->query( "SELECT sign FROM zodiac WHERE element LIKE '" . $_REQUEST['element'] . "'"); If not, escape the value with DB::quote( ) , or use a placeholder in the query: $sth = $dbh->query("SELECT sign FROM zodiac WHERE element LIKE " . $dbh->quote($_REQUEST['element'])); $sth = $dbh->query('SELECT sign FROM zodiac WHERE element LIKE ?', array($_REQUEST['element'])); Recipe 10.10 goes into detail about when you need to quote values and how to do it. By default, fetchRow( ) and fetchInto( ) put data in numeric arrays. You can tell them to use associative arrays or objects by passing an additional parameter to either method. For associative arrays, use DB_FETCHMODE_ASSOC: while($row = $sth->fetchRow(DB_FETCHMODE_ASSOC)) { print $row['sign']."\n"; } while($sth->fetchInto($row,DB_FETCHMODE_ASSOC)) { print $row['sign']."\n"; } For objects, use DB_FETCHMODE_OBJECT: while($row = $sth->fetchRow(DB_FETCHMODE_OBJECT)) { print $row->sign."\n"; } while($sth->fetchInto($row,DB_FETCHMODE_OBJECT)) { print $row->sign."\n"; } Whatever the fetch mode, the methods still return NULL when there is no more data to retrieve and a DB_Error object on error. The default numeric array behavior can be specified with DB_FETCHMODE_ORDERED. You can set a fetch mode to be used in all subsequent calls to fetchRow( ) or fetchInto( ) with DB::setFetchMode( ) : $dbh->setFetchMode(DB_FETCHMODE_OBJECT); while($row = $sth->fetchRow()) { print $row->sign."\n"; } // subsequent queries and calls to fetchRow() also return objects 10.5.4. See AlsoRecipe 10.4 for connecting to a SQL database; Recipe 10.7 for modifying a SQL database; Recipe 10.10 details how to quote data for safe inclusion in queries; documentation on DB::query( ) at http://pear.php.net/manual/en/core.db.tut_query.php and http://pear.php.net/manual/en/core.db.query.php, fetching at http://pear.php.net/manual/en/core.db.tut_fetch.php, DB_Result::fetchRow( ) at http://pear.php.net/manual/en/core.db.fetchrow.php, DB_Result::fetchInto( ) at http://pear.php.net/manual/en/core.db.fetchinto.php, and DB::setFetchMode( ) at http://pear.php.net/manual/en/core.db.setfetchmode.php. Copyright © 2003 O'Reilly & Associates. All rights reserved. |
|