How do I insert multiple items using one insert statement while at the same time detect if a unique column has duplicate values?

Question

Please mark it as duplicate if the same question exists already.

I am using MySQL to do the insertion, and I am using PDO prepared statements. I now want to insert an array of rows into a database. A similar question can be found here Multiple Values Insert with PDO Prepared Statements. However, the author didn't consider the case when duplicates occur on unique column(s). For example, consider the following table:

id    name    counter

where name has unique property and whenever an existing name is trying to be inserted the counter increases by 1. Now I want to insert an array with three names as follows:

$name_array = array('User A','User B','User C');

Suppose my table currently has User A only and its corresponding counter is 1. In that case, I would like the table after insertion be as follows:

id    name      counter
1     User A    2
2     User B    1
3     User C    1

Is it possible for me to do it using one insert statement? If yes, then how?

Answer 1

First of all: IMHO this has nothing to do with PHP, PDO or prepared statements - it is about converting an INSERT into an UPDATE for special cases.

That being so, I would solve this by using the INSERT ON DUPLICATE KEY UPDATE construct, somewhere along the lines of

INSERT INTO tablename(name)
VALUES ('User A'), ('User B'), ('User C')
ON DUPLICATE KEY UPDATE counter=counter+1;

How you send this query to the MySQL server (be it PHP/PDO or whatever) should be of no importance.