CODEWITHSUNDEEP
phpmysqlsqlsyntax-errorsql-insert
Barton Dong
Barton Dong

Reputation: 127

Insert into MySQL "on duplicate key update" syntax error

$sql = "INSERT INTO couponentries (itemid, coupon, MSISDN, channel, result) 
       VALUES ('".$itemid."','".$CouponCode."', '".$MSISDN."','".$channel."','".$status."')
       ON DUPLICATE KEY UPDATE couponentries.result = VALUES('Invalid couponcode[ERR: Already exists]')";

I am trying to insert new item from PHP webform to MySQL database. If I insert a duplicate row I will make the result update to an error message. Here is my code. It keeps giving me a syntax error.

ERROR: Could not able to execute 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 ''Invalid couponcode[ERR: Already exists]')' at line 3

Upvotes: 1

Views: 1458

Answers (1)

Mureinik
Mureinik

Reputation: 311188

You are using a string literal to update your column - lose the values:

$sql = "INSERT INTO couponentries (itemid, coupon, MSISDN, channel, result) 
       VALUES ('".$itemid."','".$CouponCode."', '".$MSISDN."','".$channel."','".$status."')
       ON DUPLICATE KEY UPDATE couponentries.result = 'Invalid couponcode[ERR: Already exists]'";

Obligatory side note:
Concatinating strings like this makes your code is vulnerable to SQL Injection attacks. You should probably look into Prepared Statements instead.

Upvotes: 2

Related Questions