Recipe 10.8. Finding the Number of Rows Returned by a Query

10.8.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.8.2. Solution

If you're issuing an INSERT, UPDATE, or DELETE with PDO::exec( ), the return value from exec( ) is the number of modified rows.

If you're issuing an INSERT, UPDATE, or DELETE with PDO::prepare( ) and PDOStatement::execute( ), call PDOStatement::rowCount( ) to get the number of modified rows, as shown in Example 10-22.

Counting rows with rowCount( )

<?php $st = $db->prepare('DELETE FROM family WHERE name LIKE ?'); $st->execute(array('Fredo')); print "Deleted rows: " . $st->rowCount(); $st->execute(array('Sonny')); print "Deleted rows: " . $st->rowCount(); $st->execute(array('Luca Brasi')); print "Deleted rows: " . $st->rowCount(); ?> 

If you're issuing a SELECT statement, the only foolproof way to find out how many rows are returned is to retrieve them all with fetchAll( ) and then count how many rows you have, as shown in Example 10-23.

Counting rows from a SELECT

<?php $st = $db->query('SELECT symbol,planet FROM zodiac'); $all= $st->fetchAll(PDO::FETCH_COLUMN, 1); print "Retrieved ". count($all) . " rows"; ?> 

10.8.3. Discussion

Although some database backends provide information to PDO about the number of rows retrieved by a SELECT so that rowCount( ) can work in those circumstances, not all do. So relying on that behavior isn't a good idea.

However, retrieving everything in a large result set can be inefficient. As an alternative, ask the database to calculate a result set size with the COUNT(*) function. Use the same WHERE clause as you would otherwise, but ask SELECT to return COUNT(*) instead of a list of fields.

10.8.4. See Also

Documentation on PDO::rowCount at http://www.php.net/PDO::rowCount and on PDO::exec( ) at http://www.php.net/exec.