PDO rowCount not returning correct number of affected rows

Question

I'm having a problem with PDO prepared statement and rowCount returning incorrect number of affected rows.

I have a simple test database of:

create table test ( boolean var1; );

Then I have the following test code:

$sth = $pdo->prepare("INSERT into test (var1) VALUES (:val)"); $sth->execute(array(':val' => true)); echo $sth->rowCount();

Which returns as expected: 1 rows affected

And when I insert an invalid type and the Insert fails:

$sth = $pdo->prepare("INSERT into test (var1) VALUES (:val)"); $sth->execute(array(':val' => 20)); echo $sth->rowCount();

Which returns as expected: 0 rows affected

However, When I have multiple inserts -

$sth = $pdo->prepare("INSERT into test (var1) VALUES (:val)"); $sth->execute(array(':val' => true)); echo $sth->rowCount() . ", "; $sth->execute(array(':val' => 20)); echo $sth->rowCount();

Results in: 1, 1

And if I flip execution order I get: 0, 1

Why is the rowCount() -- Affected rows not being set to zero on a fail statement after a success statement?

I'm running php 5.3.6-13 and Postgresql 9.1

Just turn on exceptions for PDO and get a better way of handling such cases — zerkms
– zerkms, Commented Mar 1, 2012 at 1:27
This makes sense to me because you re-used the same PDOStatement. If you need a separate rowCount for each statement, then prepare the second statement. — Ozzy
– Ozzy, Commented Apr 27, 2012 at 11:28

dotancohen · Accepted Answer · 2013-11-11 12:31:21Z

It seems to me that $sth->execute(array(':val' => true)) completes successfully thus increasing the rowCount, but $sth->execute(array(':val' => 20)) does not. Here is the state of the rowCount for the $sth at each stage:

$sth = $pdo->prepare("INSERT into test (var1) VALUES (:val)"); # No successful DML queries have been done with the $sth yet. # rowCount == 0 $sth->execute(array(':val' => true)); echo $sth->rowCount() . ", "; # rowCount increases because of a successful INSERT statement # rowCount == 1 $sth->execute(array(':val' => 20)); echo $sth->rowCount(); # rowCount does not increase due to failed INSERT statement # rowCount == 1

Now, lets look at that in the opposite order:

$sth = $pdo->prepare("INSERT into test (var1) VALUES (:val)"); # No successful DML queries have been done with the $sth yet. # rowCount == 0 $sth->execute(array(':val' => 20)); echo $sth->rowCount(); # rowCount does not increase due to failed INSERT statement # rowCount == 0 $sth->execute(array(':val' => true)); echo $sth->rowCount() . ", "; # rowCount increases because of a successful INSERT statement # rowCount == 1

Collectives™ on Stack Overflow

PDO rowCount not returning correct number of affected rows

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related