Php, ON DUPLICATED KEY UPDATE. Errors?

Question

I have read the documentation and cant see what is wrong with my Statement

Errors are hitting at the 'KEY'

    INSERT INTO employeedetails (userid,ref,name,department,commenced) VALUES 
('$userid','$ref','$name','$department','$commenced')
ON DUPLICATE KEY UPDATE
ref='" . $ref . "',
name = '" . $name . "',
department = '" . $department . "',
commenced = '" . $commenced . "'
WHERE userid=" . $userid;

Thank you very much!

EDIT:

The Primary KEY is 'userid'

        INSERT INTO employeedetails (userid,ref,name,department,commenced) 
        VALUES ('1','11','','','2017-03-08 00:00:00') ON DUPLICATE KEY UPDATE ref='11', 
        name = '', department = '', commenced = '2017-03-08 00:00:00' WHERE userid=1

The Error is: Unrecognized keyword. (near "KEY" at position 529)

What are the errors? What database are you using? Can you show us what the full SQL looks like once you've [injected](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) all the parameters? — IMSoP, Mar 08 '17 at 10:34
You cannot use `WHERE` in an `INSERT` statement, it doesn't make sense. And you could very well be sql-injecting yourself. — jeroen, Mar 08 '17 at 10:36
Hi i have updated it, i am using the WHERE in the UPDATE not the INSERT — Alex Banerjee, Mar 08 '17 at 10:38
Its a inhouse system so getting sql injected is not a issue of mine — Alex Banerjee, Mar 08 '17 at 10:41
Ah, I see your confusion: there is no UPDATE query, the whole thing is seen by MySQL as one "INSERT ... ON DUPLICATE UPDATE" query. So you can't apply the rules of a normal "UPDATE" statement. — IMSoP, Mar 08 '17 at 10:42
@AlexBanerjee Good luck when you have an unhappy but technically knowledgeable employee, then. Or an intern who decides to give themselves extra privileges. Or even just somebody who raises a bug because they wrote "Bob's Department" and it crashed the system. Proper escaping or parameterising of queries is something you should do *every time*, like looking both ways before crossing the road. — IMSoP, Mar 08 '17 at 10:44
Check if your sql mode is STRICT_ALL_TABLES by following WAMP > MySQL > my.ini > and looked for sql-mode="STRICT_ALL_TABLES" If it is yes then comment it — Saad Suri, Mar 08 '17 at 10:45
@SaadSuri What makes you think this is written using WAMP? And what does that setting have to do with this error? — IMSoP, Mar 08 '17 at 10:46
Nothing can be written in Wamp. It can be run on wamp @IMSoP — Saad Suri, Mar 08 '17 at 10:48
@SaadSuri I know. It can also be run without WAMP. I was just pointing out that your instructions won't be as useful for somebody running in a different environment. — IMSoP, Mar 08 '17 at 10:51
@IMSoP I can only do what i have been taught i am a apprentice myself and my senior developer has now left and we are waiting to recruit a new full stack dev. So in the meantime this is how i have been taught so its what im doing.. — Alex Banerjee, Mar 08 '17 at 10:51
I didn't say it can't be run without wamp. I encountered the same problem and I solved it by this. peace @IMSoP — Saad Suri, Mar 08 '17 at 10:52
@SaadSuri the strict sql mode is actually very useful. Without providing any context why it should be removed, your comment is a harmful one. — Shadow, Mar 08 '17 at 11:30

score 0 · Answer 1 · answered Mar 08 '17 at 11:03

Removing the WHERE still brings up errors but it runs and works fine

INSERT INTO employeedetails (userid,ref,name,department,commenced) 
        VALUES ('1','11','','','2017-03-08 00:00:00') ON DUPLICATE KEY UPDATE ref='11', 
        name = '', department = '', commenced = '2017-03-08 00:00:00'

Php, ON DUPLICATED KEY UPDATE. Errors?

1 Answers1