cobusbo Posted September 28, 2014 Share Posted September 28, 2014 (edited) 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 Quote Link to comment https://forums.phpfreaks.com/topic/291332-insert-ignore-but-how-to-update-existing/ Share on other sites More sharing options...
Barand Posted September 28, 2014 Share Posted September 28, 2014 You use "... ON DUPLICATE KEY UPDATE .." http://dev.mysql.com/doc/refman/5.6/en/insert-on-duplicate.html Quote Link to comment https://forums.phpfreaks.com/topic/291332-insert-ignore-but-how-to-update-existing/#findComment-1492283 Share on other sites More sharing options...
cobusbo Posted September 28, 2014 Author Share Posted September 28, 2014 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 Quote Link to comment https://forums.phpfreaks.com/topic/291332-insert-ignore-but-how-to-update-existing/#findComment-1492284 Share on other sites More sharing options...
Ch0cu3r Posted September 28, 2014 Share Posted September 28, 2014 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. Quote Link to comment https://forums.phpfreaks.com/topic/291332-insert-ignore-but-how-to-update-existing/#findComment-1492287 Share on other sites More sharing options...
cobusbo Posted September 28, 2014 Author Share Posted September 28, 2014 (edited) 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 Quote Link to comment https://forums.phpfreaks.com/topic/291332-insert-ignore-but-how-to-update-existing/#findComment-1492297 Share on other sites More sharing options...
Barand Posted September 28, 2014 Share Posted September 28, 2014 From that same page in the manual: The ON DUPLICATE KEY UPDATE clause can contain multiple column assignments, separated by commas. Quote Link to comment https://forums.phpfreaks.com/topic/291332-insert-ignore-but-how-to-update-existing/#findComment-1492299 Share on other sites More sharing options...
cobusbo Posted September 28, 2014 Author Share Posted September 28, 2014 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)") Quote Link to comment https://forums.phpfreaks.com/topic/291332-insert-ignore-but-how-to-update-existing/#findComment-1492301 Share on other sites More sharing options...
Barand Posted September 28, 2014 Share Posted September 28, 2014 Parentheses! Quote Link to comment https://forums.phpfreaks.com/topic/291332-insert-ignore-but-how-to-update-existing/#findComment-1492307 Share on other sites More sharing options...
cobusbo Posted September 28, 2014 Author Share Posted September 28, 2014 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)); Quote Link to comment https://forums.phpfreaks.com/topic/291332-insert-ignore-but-how-to-update-existing/#findComment-1492313 Share on other sites More sharing options...
Barand Posted September 28, 2014 Share Posted September 28, 2014 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 Quote Link to comment https://forums.phpfreaks.com/topic/291332-insert-ignore-but-how-to-update-existing/#findComment-1492317 Share on other sites More sharing options...
cobusbo Posted September 29, 2014 Author Share Posted September 29, 2014 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)); Quote Link to comment https://forums.phpfreaks.com/topic/291332-insert-ignore-but-how-to-update-existing/#findComment-1492373 Share on other sites More sharing options...
Psycho Posted September 29, 2014 Share Posted September 29, 2014 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)); 1 Quote Link to comment https://forums.phpfreaks.com/topic/291332-insert-ignore-but-how-to-update-existing/#findComment-1492400 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.