INSERT INTO ... ON DUPLICATE KEY UPDATE不更新PHP和mySQL中的重复值

Question

I'm trying to use the funcion INSERT INTO ... ON DUPLICATE KEY to insert some data in a mySQL table. And if there's already a user registered with the same email, i want to update the values.

So I implemented the code below after searching in this discussions: link-1, link-2, and other links...

I also watched the manuals link-4

$query=mysqli_query($conn,"INSERT INTO theElisa_signUp (name, email, telephone, user, psw, role, expire_date)
VALUES ('$name', '$email', 'not-set', '$username', '$password', '$role', '$expire_date')
ON DUPLICATE KEY
UPDATE name = '$name', user ='$username', psw = '$password', expire_date = '$expire_date' ");

In fact, the code can correctly insert a new user. But if I try to update a user that already exist, it creates a new record. So i have two user registered with the same email (I need to update the user if already there's the email in the table)

I hope this question respect the standards and I've already done all the researches in stackoverflow but none of the answers could help me. So please don't mark my question as duplicate.

P.s. I'm actually a web designer with zero experience in coding and this project is beyond my capabilities. But unfortunately I have to solve this question. Thank you.

Answer 1

The column email should be UNIQUE.

ALTER TABLE `theElisa_signUpADD` UNIQUE (email);