INSERT IGNORE but how to update existing?

[cobusbo]

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 September 28, 2014 by cobusbo

[Barand]

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

 

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

[cobusbo]

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

 

 

[Ch0cu3r]

 

 

ok I followed the example

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.

[cobusbo]

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 September 28, 2014 by cobusbo

[Barand]

From that same page in the manual:

 

 

 

The ON DUPLICATE KEY UPDATE clause can contain multiple column assignments, separated by commas.

[cobusbo]

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)")

[Barand]

Parentheses!

[cobusbo]

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));

[Barand]

 

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

[cobusbo]

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)); 

[Psycho]

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));