0

I am trying to use ON DUPLICATE KEY UPDATE in PHP. I really am not sure what the correct syntax is. I am using MySQL 8.0.19

I have, 

$sql = "INSERT INTO cart (title, price, productID, quantity) VALUES ('$title', '$price', '$productID', '$quantity') ON DUPLICATE KEY UPDATE quantity WHERE productID='$productID'";

And quantity is auto-increment and productID is the unique key. I want quantity to increase if the productID already exists.

I get the error,

"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 'WHERE productID='1'' at line 1".

Jay Blanchard
  • 34,243
  • 16
  • 77
  • 119
Emily Vaughn
  • 1
  • 3
    [The manual](https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html) is a good start. – Funk Forty Niner Apr 16 '20 at 17:01
  • 4
    [Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)***. Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! – Jay Blanchard Apr 16 '20 at 17:04
  • I see `insert into where` construct attempts now and then but I've never figured out what the intention is :-? – Álvaro González Apr 16 '20 at 17:06
  • Sort of https://stackoverflow.com/questions/867166/mysql-on-duplicate-key-update with prepared statements. – Nigel Ren Apr 16 '20 at 17:07
  • @JayBlanchard I am just creating this for a class at my university! I would love to understand sql more but I am not quite there yet. – Emily Vaughn Apr 16 '20 at 17:08
  • Oh... There's even a popular question about it: [MySQL Insert Where query](https://stackoverflow.com/questions/485039/mysql-insert-where-query). – Álvaro González Apr 16 '20 at 17:13

1 Answers1

0

You don't need a where clause because it updated based on the duplicate key and you have to assign the same value incremented by $quantity.

$stmt = $conn->prepare("
  INSERT INTO cart (title, price, productID, quantity)
  VALUES (?, ?, ?, ?)
  ON DUPLICATE KEY UPDATE quantity = quantity + ?;
");
$stmt->bind_param($title, $price, $productID, $quantity, $quantity);
$stmt->execute();
Diadistis
  • 12,086
  • 1
  • 33
  • 55
  • You'd probably want to increase it by `$quantity`, not `1`. Also, use prepared statements and don't quote numerics. – Sammitch Apr 16 '20 at 18:11
  • 1
    @Sammitch yes, I agree on both points. I was just trying to illustrate the correct usage of the INSERT/ON DUPLICATE – Diadistis Apr 16 '20 at 18:16