6

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? :)

Christian Sieber
  • 63
  • 5
  • Is your table named table? I don't see how the query would succeed with it being a reserved keyword. – JustinDanielson Aug 29 '12 at 02: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 Aug 29 '12 at 02: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 Aug 29 '12 at 16:16

2 Answers2

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;
xdazz
  • 158,678
  • 38
  • 247
  • 274
  • 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. :) – Christian Sieber Aug 29 '12 at 16:36
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.

Steve Robbins
  • 13,672
  • 12
  • 76
  • 124