PHP's PDO prepared INSERT statement: am I able to define if the insert was successful?

Question

I have the following code:

$add_rq = $DBH->prepare('INSERT INTO `table` SET `url` = :url ON DUPLICATE KEY UPDATE `url`=`url`');
if($add_rq->execute(['url' => $url]))
  echo "Added (id: ".$DBH->lastInsertId().")";
else
  echo "Not added";

If the inserting url already exist in the table the ON DUPLICATE KEY UPDATE statement working but the return value of the execute() is always TRUE. However lastInsertId() is zero in this case. I COULD use this as the indication of the insertion duplication or I could do one more query to DB prior to the insert but both approaches looks bad to me.

Is there any better way?

Barmar · Accepted Answer · 2014-11-20T08:50:02.490

1

You can use $add_rq->rowCount(). According to a comment in the documentation, it will return 1 if a new row was inserted, 2 if an existing row was updated. But apparently that's wrong, it returns 0 if a row was updated.

execute() only returns false if it fails because of an error.

edited Nov 20 '14 at 08:50

answered Nov 20 '14 at 08:41

Barmar

741,623
53
500
612

Thanks! That worked fine for me. But in my case if the row already exist in the table the return value was `0` (not `2`). Could you make clear and edit your answer? – Vlada Katlinskaya Nov 20 '14 at 08:46

score 0 · Answer 2 · edited May 23 '17 at 11:57

0

Update the id if it is duplicate

Then lastInsertId() will return the id (instead of 0) after an update
It will return the id after an insert either way

INSERT INTO `table` SET `url` = :url ON DUPLICATE KEY UPDATE `url`=`url`, id=LAST_INSERT_ID(id)

now this will return the id even if no new rows were inserted

$DBH->lastInsertId()

To find out if there was an insert of update, you could add another column called "updated" with a default value of 0, then when it updates it should set the column to 1

INSERT INTO `table` (`url`) VALUES (?) ON DUPLICATE KEY UPDATE `url`=?, `updated`=`updated`+1

Source: it's mentioned at the bottom of the mysql manual http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.htm
MySQL ON DUPLICATE KEY - last insert id?

edited May 23 '17 at 11:57

Community

1
1

answered Nov 20 '14 at 09:12

Timo Huovinen

53,325
33
152
143

The main thing I wanted to achieve is not to find out which row I referred but is there was insert **or** update. – Vlada Katlinskaya Nov 20 '14 at 10:09
oh sorry, I misunderstood the question then, I edited the answer – Timo Huovinen Nov 24 '14 at 17:25

PHP's PDO prepared INSERT statement: am I able to define if the insert was successful?

2 Answers2