PDO rowCount does not return the correct number of rows affected

Question

I am having a problem with a prepared PDO statement and rowCount returning the wrong number of rows affected.

I have a simple test database:

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(); 

Returns as expected: 1 row affected

And when I insert the invalid type, and the insertion fails:

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

Returns as expected: 0 rows affected

However, when I have a few 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 deduce the order of execution, I get: 0, 1

Why are rowCount () - Affected rows not set to zero in a fail statement after a success statement?

I am running php 5.3.6-13 and Postgresql 9.1

Answer 1

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

 $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 let's look at it in reverse 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