PDOStatement::rowCount

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

Description

publicintPDOStatement::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:

Return number of rows that were deleted:
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.

User Contributed Notes 11 notes

When updating a Mysql table with identical values nothing's really affected so rowCount will return 0. As Mr. Perl below noted this is not always preferred behaviour and you can change it yourself since PHP 5.3.

Note that an INSERT ... ON DUPLICATE KEY UPDATE statement is not an INSERT statement, rowCount won't return the number or rows inserted or updated for such a statement. For MySQL, it will return 1 if the row is inserted, and 2 if it is updated, but that may not apply to other databases.

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.

It'd better to use SQL_CALC_FOUND_ROWS, if you only use MySQL. It has many advantages as you could retrieve only part of result set (via LIMIT) but still get the total row count.
code:
<?php
$db = new PDO(DSN...);
$db->setAttribute(array(PDO::MYSQL_USE_BUFFERED_QUERY=>TRUE));
$rs = $db->query('SELECT SQL_CALC_FOUND_ROWS * FROM table LIMIT 5,15');
$rs1 = $db->query('SELECT FOUND_ROWS()');
$rowCount = (int) $rs1->fetchColumn();
?>

Yet another workaround to return the row count inside only ONE select (see limitations below!):

$sth = $dbh->prepare("SELECT *,count(*) AS howmany FROM users WHERE email=:email and password=:pass"); #var placeholders$sth->execute(array(':email'=>$email, ':pass'=>$pass)); #var binding$row = $sth->fetch(); #get one row (it'll always be one and only one!!!)

if ($row['howmany'] == 1){ #we have a match and only one! cool! echo $row['email'], $row['name'], $row['phone'], ... ;} elseif ($row['howmany']>1) { #more than one row returned #one programmer should be fired 'cause he's not checking for #for existing emails, before creating a new user ... # treat this exception somehow or simply skip this branch, # if you're sure it won't happen in your table} else { #no match in the table ($row['howmany'] == 0) echo "Email/pass didn't match the ones in the database!";}

Advantages: - only one select statement is executed, no two steps needed!- it checks if one row exists in the table or not, according to the WHERE clause.- it returns all (or only a selection of) fields for that one row, if exists.

Disadvantages:- it doesn't return row fields reliable if more than one row found. If more than one row responds to the SELECT query, the query returns still only one row and you don't know which one exactly .Maybe using a SORT BY, would make it a bit more predictible (as in: "if more than one users found, return the last user added in the table") but it's more a matter of good design of the program that fills in the table initially.

Uses: - It is perfect for checking if a user/pass pair is present in a users table and to return the other fields of the user (like name, phone, whatever) if user was found.

As of SQLite 3.x, the SQLite API itself changed and now all queries are implemented using "statements". Because of this, there is no way for PDO to know the rowCount of a SELECT result because the SQLite API itself doesn't offer this ability.

As a workaround, I created my own rowCount() function - it's a bit of a hack and hasn't been fully tested yet (I don't know how it will work when using JOINs in SELECTs, etc...), but at least alleviates the necessity for SELECT COUNT(*)'s everywhere in your code.

I would have preferred if it were possible to overload the rowCount() function from PDOStatement, but I don't think it's possible (or I don't know how to do it). There's also potential room for a bit more security ensuring that $queryString is wiped clean after other query()s so that you don't get a bad result, etc...

The actual code should be posted in the above/below post (max post limits, argh!). If others wish to extend/perfect this method, please keep me posted with an email as to what you've done.