PHP Cookbook

10.8. Finding the Number of Rows Returned by a Query

Problem

You want to know how many rows a SELECT query returned, or you want to know how many rows were changed by an INSERT, UPDATE, or DELETE query.

Solution

To find the number of rows returned by a SELECT query, use PEAR DB’s DB_Result::numRows( ) :

// query
$sth = $dbh->query('SELECT * FROM zodiac WHERE element LIKE ?', array('water'));
$water_rows = $sth->numRows();

// prepare and execute
$prh = $dbh->prepare('SELECT * FROM zodiac WHERE element LIKE ?');
$sth = $dbh->execute($prh,array('fire'));
$fire_rows = $sth->numRows();

To find the number of rows changed by an INSERT , UPDATE, or DELETE query, use DB::affectedRows( ) :

$sth = $dbh->query('DELETE FROM zodiac WHERE element LIKE ?',array('fire'));
$deleted_rows = $dbh->affectedRows();

$prh = $dbh->prepare('INSERT INTO zodiac (sign,symbol) VALUES (?,?)',
                     array('Leap Day','Kangaroo'));
$dbh->execute($prh,$sth);
$inserted_rows = $dbh->affectedRows();

$dbh->query('UPDATE zodiac SET planet = ? WHERE sign LIKE ?',
            array('Trantor','Leap Day'));
$updated_rows = $dbh->affectedRows();

Discussion

The number of rows in a result set is a property of that result set, so that numRows( ) is called on the statement handle and not the database handle. The number of rows affected by a data manipulation query, however, can’t be a property of a result set, because those queries don’t return result sets. As a result, affectedRows( ) is a method of the database handle.

See Also

Documentation ...