Last updated: Fri, 27 Jun 2008

PDOStatement->rowCount

(PHP 5 >= 5.1.0, PECL pdo:0.1-1.0.3)

PDOStatement->rowCount — Returns the number of rows affected by the last SQL statement

Description

int PDOStatement::rowCount ( void )

PDOStatement::rowCount() returns the number of rows affected by the last DELETE, INSERT, or UPDATE statement executed by the corresponding PDOStatement object.

If the last SQL statement executed by the associated PDOStatement was a SELECT statement, some databases may return the number of rows returned by that statement. However, this behaviour is not guaranteed for all databases and should not be relied on for portable applications.

Return Values

Returns the number of rows.

Examples

Example #1 Return the number of deleted rows

PDOStatement::rowCount() returns the number of rows affected by a DELETE, INSERT, or UPDATE statement.


<?php
/* Delete all rows from the FRUIT table */
$del = $dbh->prepare('DELETE FROM fruit');
$del->execute();

/* Return number of rows that were deleted */
print("Return number of rows that were deleted:\n");
$count = $del->rowCount();
print("Deleted $count rows.\n");
?>

The above example will output:

Deleted 9 rows.

Example #2 Counting rows returned by a SELECT statement

For most databases, PDOStatement::rowCount() does not return the number of rows affected by a SELECT statement. Instead, use PDO::query() to issue a SELECT COUNT(*) statement with the same predicates as your intended SELECT statement, then use PDOStatement::fetchColumn() to retrieve the number of rows that will be returned. Your application can then perform the correct action.


<?php
$sql = "SELECT COUNT(*) FROM fruit WHERE calories > 100";
if ($res = $conn->query($sql)) {

    /* Check the number of rows that match the SELECT statement */
  if ($res->fetchColumn() > 0) {

        /* Issue the real SELECT statement and work with the results */
         $sql = "SELECT name FROM fruit WHERE calories > 100";
       foreach ($conn->query($sql) as $row) {
           print "Name: " .  $row['NAME'] . "\n";
         }
    }
    /* No rows matched -- do something else */
  else {
      print "No rows matched the query.";
    }
}

$res = null;
$conn = null;
?>

The above example will output:

apple
banana
orange
pear


For SELECT what you want is:

<?php



$rows = $statement->fetchAll();

$count = count($rows);



?>



This will work with every database and has almost no overhead if you wanted to fetch all rows anyway.



$statement->rowCount() can only count rows in buferred queries, which means it does exactly what the code above, but you don't see it :)

Anonymous
26-Dec-2007 05:10


The rowCount method does not seem to work with pdo_sqlite, maybe because it will not support forward-only cursors:



<?php

error_reporting(E_ALL+E_NOTICE);



$dsn = 'sqlite::memory:';

$pdo = new PDO($dsn);



$pdo->exec('CREATE TABLE foo(id varchar(11) NOT NULL, PRIMARY KEY(id))');

$pdo->exec("INSERT INTO foo(id) VALUES ('ffs')");



$sqlGet = 'SELECT * FROM foo WHERE id=:id';

$stmt = $pdo->prepare($sqlGet);

$id = 'ffs';

$stmt->bindParam(':id', $id, PDO::PARAM_STR);

$stmt->execute();



var_dump($stmt->rowCount(), count($stmt->fetchAll()));

?>



which outputs: 0 1



http://php.net/manual/en/function.sqlite-num-rows.php says sqlite_num_rows() cannot be used on unbuffered results; the explanation to the failure of the rowCount() method is probably along the same lines.

The workaround would be to use the count() function on a fetched result, but it might not be as efficient.

Mr. Perl
14-Nov-2007 08:06


To Matt,



PDO is very similar in design to Perl's DBI which does allow you to set driver specific attributes such as mysql_client_found_rows=1 as part of the DSN. 



PDO has a setAttribute() method, but afaik there is no 

MYSQL_ATTR_CLIENT_FOUND_ROWS constant (yet). Hopefully some PDO developer will patch it in one day.



Setting that (at least in Perl and C) will make rowCount() return the number of rows selected for the update, not just the number of rows actually updated.

Matt
07-Oct-2007 10:22


Great, while using MySQL5, the only way to get the number of rows after doing a PDO SELECT query is to either execute a separate SELECT COUNT(*) query (or to do count($stmt->fetchAll()), which seems like a ridiculous waste of overhead and programming time.



Another gripe I have about PDO is its inability to get the value of output parameters from stored procedures in some DBMSs, such as SQL Server.



I'm not so sure I'm diggin' PDO yet.

evangun2001 at yahoo dot fr
10-Sep-2007 11:59


hipska at mac dot com : what you say is not correct.

The resulting $fruits cannot be used as an array, and count($fruits) always equals 1.



The confusion is however understandable, since foreach ($fruits->query($query) as $row){} will work, as with any array.



The most efficient way to retrieve number of rows found by SELECT in a portable application is probably to make a condition :



- Check that the current database system (get its name via getAttribute()), is one where rowCount() returns the number of rows found

1) If it does, there you have it

2) If it does not, execute an extra COUNT query.

hipska at mac dot com
01-Aug-2007 02:14


If PDOStatement->rowCount() does not return the number of rows affected by a SELECT statement i use a smaller way to get the number of rows.



<?php



$sql = 'SELECT name FROM fruit WHERE calories > 100';



$fruits = $db->query($sql);



if( count($fruits) > 0 ){

    

    foreach ($fruits as $fruit) {

        echo 'Name: ' . $fruit['name'] . "\n";

    }

    

}else{

    echo 'No fruits ...';

}



?>



i don't know if that is a correct way, but it works

pcdinh at phpvietnam dot net
21-Mar-2006 03:39


In some case you need to know how many row are retrieved from a select query (i.e to page a result set) you can use:



$totalRows = count($resultSet->fetchAll());



It works on MySQL 5.0.18 and PHP 5.1

Pablo Godel
30-Jan-2006 01:18


For large resultsets the method described below is not very efficient. It is better to do a select COUNT(*).

markjcrane at gmail dot com
29-Nov-2005 05:12


A rowcount that would return the record count with a select statement would be extremely usefull in PDO. However since it doesn't exist here is an another method to return the record count.



$sql = "SELECT * FROM tblusers ";

$prepstatement = $db->prepare($sql);

$prepstatement->execute();



//save the results to array

$result = $prepstatement->fetchAll();



//count the array

$resultcount = count($result);

echo "resultcount : ".$resultcount ."<br>";



foreach($result as $field) {

    echo "username: ".$field[username]."<br>";

    echo "<br>";

}

unset($sql, $result, $resultcount);

add a note

PDOStatement->setAttribute

PDOStatement->nextRowset

Last updated: Fri, 27 Jun 2008

PDOStatement->rowCount

Description

Return Values

Examples

See Also