1

I'm trying to update a users details inside an SQL database, however if a row of that user dose not exist, I need to create one for that user (Only one per user) It's just not updating nor inserting. Here is the following script.

$sql = "IF EXISTS (SELECT * FROM user_details WHERE user_id = $user_id)
UPDATE user_details SET work = $work, education = $education, location = $location, relationship = $relationship, phone = $phone, email = $email, website = $website, language = $language, skill = $skill, tumbkr = $tumblr, instagram = $instagram, skype = $skype, facebook = $facebook, youtube = $youtube, twitter = $twitter, about = $about, user_id = $user_id WHERE user_id = $user_id
ELSE
INSERT INTO user_details (work, education, location, relationship, phone, email, website, language, skill, tumblr, instagram, skype, facebook, youtube, twitter, about, user_id)";
    $q = $conn->prepare($sql);
    $q->execute();
Casper Round
  • 343
  • 2
  • 14
  • 4
    you should learn about prepared statements – Jens May 24 '16 at 09:27
  • 4
    Look up [insert...on duplicate key update](http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html) syntax. – Jonnix May 24 '16 at 09:27
  • read about merge statement – Jens May 24 '16 at 09:28
  • I will be doing a prepared statement, however it will take a long time to write. I'm just trying to get this base script working – Casper Round May 24 '16 at 09:29
  • 1
    Prepared statements do _not_ take long to write. You've already got 90% of it. – Jonnix May 24 '16 at 09:29
  • Yes, I've read about merge and insert on duplicate but nothing seems to be working. I have user_id as unique key too but not auto increment – Casper Round May 24 '16 at 09:30
  • 1
    As long as it's the primary key (i.e. unique) then insert on duplicate key will work. If it doesn't you should be asking why that isn't working. Your current SQL is... not great, and broken. – Jonnix May 24 '16 at 09:31
  • By the way, in general, an `UPDATE` statement without a `WHERE` condition is probably not going to do what you expect it to. – jeroen May 24 '16 at 09:32
  • I know its not great, Its most likely because i'm getting frustrated with researching and not finding any help after constantly writing out more and more code. – Casper Round May 24 '16 at 09:32
  • @jeroen Have a look at the insert too, it's missing any insert values. – Jonnix May 24 '16 at 09:34
  • 1
    @JonStirling Yes, I think *... not great, and broken* is the right term :-) – jeroen May 24 '16 at 09:37

1 Answers1

1

Do as below:

INSERT INTO table_name (field1,field2) VALUES (val1,val2) ON DUPLICATE KEY UPDATE field_name=VALUE;

Dharmesh patel
  • 654
  • 1
  • 12
  • 23