php - Conditional INSERT INTO MySQL - WHERE NOT EXISTS

Question

I am trying to do an INSERT INTO query when the customer does not already have a record of purchasing a product.

I have tried the below SQL, but it doesn't seem to work.

$queryAddPurchase = "INSERT INTO purchase (UserID, Product, Price) 
              VALUES ('$userID', '$product', '$price')
              WHERE NOT EXISTS(SELECT Product
                                       FROM purchase
                       WHERE Product = '$product'
                       AND UserID = '$userID')";

The error I am receiving is as follows:

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 NOT EXISTS(SELECT Product FROM purchase ' at line 3

Any advice would be greatly appreciated!

Answer 1

On the assumption, that a user may only buy one of each product (ever and for all products).

ALTER TABLE purchase ADD UNIQUE KEY (`UserID`, `Product`); -- run this just once. this changes the table

INSERT IGNORE INTO purchase (UserID, Product, Price) VALUES ('$userID', '$product', '$price');

Be aware, that this then prevents him from buying any product multiple times, which might not be the desired outcome.