PDO/MySQL rowCount not returning as expected

Question

Post-answer edit: I think this was a bug in my own code -- I don't know what it was but I proceeded to fix it. See answer below.

I'm using MySQL/PHP to perform a series of INSERT ... ON DUPLICATE KEY UPDATE statements. The documentation I've read indicates that the row count for this will return:

-1 : an error
0 : update, no changes to row made (i.e. all values duplicated)
1 : row inserted
2 : update performed on row with duplicate key

However, I'm only seeing results of 0s where I should be seeing 2s (since I am watching the code update various database values.) Here is the code:

$stmt = $db->prepare('INSERT INTO sometable (`id`, `name`, `email`) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE `name` = ?, `email` = ? ;');

$stmt->execute( array ( $id, $name, $email, $name, $email ) );

$rc = $stmt->rowCount();
echo $rc;

$rc is always coming up 0 for updates (even when values were definitely changed) or 1 (for successful inserts, as expected.)

What am I missing? :)

Is your table named table? I don't see how the query would succeed with it being a reserved keyword. — JustinDanielson
– JustinDanielson, Commented Aug 29, 2012 at 2:15
I'm not sure if PDO properly reports rowCount in this situation. If I get time I will set up a test database and find out. — Levi Morrison
– Levi Morrison, Commented Aug 29, 2012 at 2:16
JustinDanielson: No, I just made the code generic and didn't think about little details while doing so. I edited for clarity, I promise it has a real table name. :) — Christian Sieber
– Christian Sieber, Commented Aug 29, 2012 at 16:16

xdazz · Accepted Answer · 2012-08-29 02:33:20Z

3

Try using the MySQL function, if it returns the right result, the problem will be PDO:rowCount()

$stmt = $db->prepare('INSERT INTO table (`id`, `name`, `email`) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE `name` = ?, `email` = ? ;');

$stmt->execute( array ( $id, $name, $email, $name, $email ) );

$rc = $db->query("SELECT ROW_COUNT()")->fetchColumn();
echo $rc;

answered Aug 29, 2012 at 2:33

xdazz

161k38 gold badges255 silver badges278 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Christian Sieber Over a year ago

I tried this and not only did it work properly, but the PDOStatement->rowCount() method starting working as expected also. I thought I had been pretty thorough but I think I have to chalk this one up to PEBKAC; sorry for wasting everyone's time. :)

Steve Robbins · Accepted Answer · 2012-08-29 16:19:48Z

0

I think rowCount() is meant to return the number of rows returned in a SELECT statement.

From the manual

PDO::exec — Execute an SQL statement and return the number of affected rows

Try using exec() instead.

answered Aug 29, 2012 at 16:19

Steve Robbins

13.8k12 gold badges81 silver badges129 bronze badges

Collectives™ on Stack Overflow

PDO/MySQL rowCount not returning as expected

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related