douao1854
douao1854
2019-04-02 11:06

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

已采纳

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.

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

2条回答

  • doumu9019 doumu9019 2年前

    The column email should be UNIQUE.

    ALTER TABLE `theElisa_signUpADD` UNIQUE (email);
    
    点赞 评论 复制链接分享
  • dousou1878 dousou1878 2年前

    You can use two queries here's reference

    $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',");
    $query=mysqli_query($conn,"UPDATE theElisa_signUp SET user ='$username', psw = '$password', expire_date = '$expire_date' WHERE name = '$name' ");
    
    点赞 评论 复制链接分享

相关推荐