10.4. Querying a SQL Database
Problem
You want to retrieve some data from your database.
Solution
Use 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"; }
Discussion
The 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'])); ...
Get PHP Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.