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