How to see if mysql replace or insert my query

Question

if I have a query with field 1 being a primary key:

$rep = "Replace into table (field1,field2) values ('value1','value2')";
$stmt = $db->query($rep);

Is there a way to tell if mysql inserted the row, or found and replaced the row?

already has an answer here: https://stackoverflow.com/q/16148611/6385306, https://stackoverflow.com/a/4205207/6385306 — Daniyal, Oct 11 '18 at 11:44
`$stmt->affected_rows` should have a value of 1 on insert or greater than 1 on replace (assuming `$db` is a mysqli connection link) — apokryfos, Oct 11 '18 at 11:48

score 1 · Answer 1 · answered Oct 11 '18 at 11:50

1

For Posterity:

$rowCount = $stmt->rowCount();

if $rowCount == 1 it was an insert, if $rowCount == 2, it was a replace.

answered Oct 11 '18 at 11:50

bart2puck

2,378
3
21
46

score 0 · Answer 2 · answered Oct 11 '18 at 11:50

INSERT INTO AggregatedData (datenum,Timestamp) VALUES ("734152.979166667","2010-01-14 23:30:00.000") ON DUPLICATE KEY UPDATE Timestamp=VALUES(Timestamp)

To achieve this type of task mysql provide us DUPLICATE KEY UPDATE.

Below is the example how you will create new record if record is not exists in database otherwise it will update record

$rep = "INSERT into table (primaryField,field2) values ('value1','value2') ON DUPLICATE KEY UPDATE primaryField=VALUES(primaryField)";
$stmt = $db->query($rep);

For more detail you can read this https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html

I think this will help you.

How to see if mysql replace or insert my query

2 Answers2