PHP Database Access Finding the Number of Rows Returned by a Query

PHP Database Access

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 an INSERT, UPDATE, or DELETE query changed.

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.

Example  Counting rows with rowCount()

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

Example  Counting rows from a SELECT

          $st = $db->query('SELECT symbol,planet FROM zodiac');

          $all= $st->fetchAll(PDO::FETCH_COLUMN, 1);

          print "Retrieved ". count($all) . " rows";

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.