Jump to content

INSERT IGNORE but how to update existing?


cobusbo

Recommended Posts

Hi I'm currently experiencing a problem with my query. I've used the `INSERT IGNORE` option in my query and it works pretty well not to add duplicates, but the problem is that if my unique field match it doesn't update any other info in the row, is there maybe another option to update a row if an existing field exists but insert new row if it doesn't exists?

 

my current query is as follow

 

$query = mysqli_query($con,"INSERT IGNORE INTO mxit (ip,time,user_agent,contact,userid,id,login,nick,location,profile) 
VALUES ('$ip','$post_time','$mxitua','$mxitcont','$mxituid','$mxitid','$mxitlogin','$mxitnick','$mxitloc','$mxitprof')") or die(mysqli_error($con)); 
Edited by cobusbo
Link to comment
Share on other sites

You use "... ON DUPLICATE KEY UPDATE .."

 

http://dev.mysql.com/doc/refman/5.6/en/insert-on-duplicate.html

ok I followed the example, but receiving an error message

 

$query = mysqli_query($con,"INSERT IGNORE INTO mxit (ip,time,user_agent,contact,userid,id,login,nick,location,profile) 
VALUES ('$ip','$post_time','$mxitua','$mxitcont','$mxituid','$mxitid','$mxitlogin','$mxitnick','$mxitloc','$mxitprof') ON DUPLICATE KEY UPDATE (ip=$ip,user_agent=$mxitua,contact=$mxitcont,login=$mxitlogin,nick=$mxitnick,location=$mxitloc,profile=$mxitprof)") or die(mysqli_error($con)); 

the error message is as follow

 

 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(ip=197.79.26.192,user_agent=PURPLE,contact=guniverse,login=cobusbo,nick=~cobusb' at line 2

 

 

Link to comment
Share on other sites

No you haven't. You are getting that error because you have given the ON DUPLICATE KEY UPDATE clause an incorrect value.

 

The value you pass it is the unique primary key that identifies the row you are going to update when a duplicate entry occurs.

My Unique field is the userid field and I didnt include it as you can see above. I only included certain fields to update?

Edited by cobusbo
Link to comment
Share on other sites

Parentheses!

Ok I tried

$query = mysqli_query($con,"INSERT IGNORE INTO mxit (ip,time,user_agent,contact,userid,id,login,nick,location,profile) 
VALUES ('$ip','$post_time','$mxitua','$mxitcont','$mxituid','$mxitid','$mxitlogin','$mxitnick','$mxitloc','$mxitprof') ON DUPLICATE KEY UPDATE (ip,user_agent,contact,login,nick,location,profile) VALUES ('$ip','$mxitua','$mxitcont','$mxitlogin','$mxitnick','$mxitloc','$mxitprof')") or die(mysqli_error($con));
Link to comment
Share on other sites

 

I don't get it I used commas

ON DUPLICATE KEY UPDATE (ip=$ip,user_agent=$mxitua,contact=$mxitcont,login=$mxitlogin,nick=$mxitnick,location=$mxitloc,profile=$mxitprof)")

Yes, but you put them inside parentheses. Look at the examples in manual for the ON DUPLICATE KEY UPDATE clauses - not a "(" or ")" to be seen

Link to comment
Share on other sites

Yes, but you put them inside parentheses. Look at the examples in manual for the ON DUPLICATE KEY UPDATE clauses - not a "(" or ")" to be seen

Thank you it solved my problem

$query = mysqli_query($con,"INSERT IGNORE INTO mxit (ip,time,user_agent,contact,userid,id,login,nick,location,profile) 
VALUES ('$ip','$post_time','$mxitua','$mxitcont','$mxituid','$mxitid','$mxitlogin','$mxitnick','$mxitloc','$mxitprof') ON DUPLICATE KEY UPDATE ip='$ip',user_agent='$mxitua',contact='$mxitcont',login='$mxitlogin',nick='$mxitnick',location='$mxitloc',profile='$mxitprof'") or die(mysqli_error($con)); 

Link to comment
Share on other sites

FYI: If you are doing an ON DUPLICATE KEY UPDATE, there is a way to reference the INSERT values in the UPDATE portion of the query instead of having to reuse the variables. I find this a better approach. If you have a typo in the variable name you may not catch it. But, if you mistype a field reference it would produce an error regardless if whether the INSERT or UPDATE is run. Also, I would highly advise creating your queries as string variables so you can echo them to the page for debugging purposes. Plus, using tabs and spacing to format the query in a readable format helps save a lot of time.

 

E.g.

 

$query = "INSERT IGNORE INTO mxit
              (ip, time, user_agent, contact, userid, id, login, nick, location, profile) 
          VALUES
              ('$ip', '$post_time', '$mxitua', '$mxitcont', '$mxituid', '$mxitid', '$mxitlogin',
               '$mxitnick', '$mxitloc', '$mxitprof')
           ON DUPLICATE KEY UPDATE
               ip = VALUES(ip), user_agent = VALUES(user_agent), contact = VALUES(contact),
               login = VALUES(login), nick = VALUES(nick), location = VALUES(location), profile = VALUES(profile)";
$result = mysqli_query($con, $query) or die(mysqli_error($con));
  • Like 1
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.