CODEWITHSUNDEEP
phppdo
James Cowhen
James Cowhen

Reputation: 2955

PDO rowCount not returning correct number of affected rows

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

Upvotes: 6

Views: 2886

Answers (1)

dotancohen
dotancohen

Reputation: 31481

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

Upvotes: 2

Related Questions