PHP CookBook

Recipe 10.5 Querying a SQL Database

10.5.1 Problem

You want to retrieve some data from your database.

10.5.2 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";
}

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

$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 Also

Recipe 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.