Finding the Number of Rows Returned by a Query (Perl Cookbook, 2nd Edition)

14.14. Finding the Number of Rows Returned by a Query

14.14.1. Problem

You want to find out how many rows were returned by a query.

14.14.2. Solution

For operations that aren't queries (such as INSERTs, UPDATEs, and DELETEs), the do method returns the number of rows affected, -1 when it can't determine the right value, or else undef in case of failure.

$rows = $dbh->do("DELETE FROM Conference WHERE Language='REBOL'");
if (! defined $rows) {
  # failed, but this is not needed if RaiseError is active
} else {
  print "Deleted $rows rows\n";
}

You can't reliably get row counts from queries without either fetching all of the results and then counting them, or writing another query.

14.14.3. Discussion

The easiest way to find out how many rows a query will return is to use the COUNT function in SQL. For example, take this query:

SELECT id,name FROM People WHERE Age > 30

To find out how many rows it will return, simply issue this query:

SELECT COUNT(*) FROM People WHERE Age > 30

If the database is so volatile that you're afraid the number of rows will change between the COUNT query and the data-fetching query, your best option is to fetch the data and then count rows yourself.

With some DBD modules, execute returns the number of rows affected. This isn't portable and may change in the future.

14.14.4. See Also

The documentation with the DBI module from CPAN; http://dbi.perl.org; Programming the Perl DBI