CODEWITHSUNDEEP
phpmysqlsql
Guesser
Guesser

Reputation: 1857

PHP SQL ON DUPLICATE KEY having no affect

I only want to insert if there are no entries where "name" and "email" exist together.

So it's ok if "name" exists with a different e-mail or vica versa.

mysql_query("INSERT INTO 
             list (name,email) 
             VALUES ('$name','$email') 
             ON DUPLICATE KEY 
             UPDATE name='$name',email='$email'");

I've made both name & email primary keys but the ON DUPLICATE statement is having no effect.

Upvotes: 1

Views: 127

Answers (2)

Sven
Sven

Reputation: 70863

If name and email is your combined unique key, it makes no sense to trigger the update statement (overwriting with the exact same values) if a dataset already exists. In such a case I would really only INSERT and then check if the database responds with a key constraint violation. This is the expected error that is then ignored. Anything else is still an error.

I wouldn't silence this with this NO-OP update.

Upvotes: 1

zerkms
zerkms

Reputation: 254916

You need to have a composite unique key to have ON DUPLICATE KEY UPDATE worked:

CREATE UNIQUE INDEX name_email ON list (name, email);

that is why it called ON DUPLICATE **KEY** UPDATE

Upvotes: 0

Related Questions