I'm trying to discern whether a replace into
query resulted in a straight write, or first a deletion then a write.
The MySQL docs say the affected-rows count should return 1 in the case of the former, or more than 1 in the case of the latter:
The affected-rows count makes it easy to determine whether REPLACE only added a row or whether it also replaced any rows: Check whether the count is 1 (added) or greater (replaced).
For me, however, doing this via PHP and MySQLI, the value is always exactly 1, regardless of whether my query results in a straight write or first a deletion then a write.
I have a table "foo" with one column: a varchar that is also the primary key. So to start off with it's empty. I run:
$sql = "REPLACE INTO foo VALUES('bar');"
$db->query($sql); //$db is an instantiated and working MySQLI instance
echo $db->affected_rows;
That gives me "1" - fair enough, it was a straight write. But if I then run the same query again, it should give me "2", right? First deleting the row, then re-inserting it, as the primary key is the same. 2 affected rows, therefore.
Incidentally, I have tried this with basic queries and also prepared statements, i.e.
$sql = "REPLACE INTO foo VALUES(?)";
$stmt = $db->prepare($sql);
$bar = "bar";
$stmt->bind_param('s', $bar);
$stmt->execute();
echo $stmt->affected_rows; //still 1
Any thoughts!?