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.
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 PDOState ment::execute(), call PDOStatement::rowCount() to get the number of modified rows, as shown in Example 10-22.
Example 10-22. Counting rows with rowCount()
|
1 2 3 4 5 6 7 |
$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.
Example 10-23. Counting rows from a SELECT
|
1 2 3 |
$st = $db->query('SELECT symbol,planet FROM zodiac'); $all= $st->fetchAll(PDO::FETCH_COLUMN, 1); print "Retrieved ". count($all) . " rows"; |
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.
