Recipe 10.9 Finding the Number of Rows Returned by a Query
10.9.1 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.
10.9.2 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();
10.9.3 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.
10.9.4 See Also
Documentation on DB_Result::numRows( ) at
http://pear.php.net/manual/en/core.db.numrows.php
and DB::affectedRows( ) at
http://pear.php.net/manual/en/core.db.affectedrows.php.
|
