PHP Cookbook

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