Update stock value with mysql-CodePudding

Now i have this sql update code:

$sql = "UPDATE products SET stock = stock - '$quantity' WHERE product_id = '$id'";

I have some problem with this. It works, but if the stock value in the table is smaller, than the quantity in the webshop cart, it will update the stock field to a minus value.

I know, that there is a function in mysql for this, that will only update it to 0, and not to minus. Whats that function, or how should i do this?

CodePudding user response：

You can use case

UPDATE products SET stock 
  = case
     when stock > '$quantity' then stock - '$quantity' 
     else 0 end
WHERE product_id = '$id';

Be sure that your variables $quantity and $id are checked to be sure that they are numeric to avoid SQL injection.

CodePudding user response：

You can use the MySQL IF Statement for this. First check if stock - quantity is greater than 0 then return the value, else return 0.

UPDATE products
SET stock = IF(stock - '$quantity' > 0, stock - '$quantity', 0) 
WHERE product_id = '$id';

CodePudding user response：

You could use the GREATEST function to get the largest value of your existing calculation or 0, then if the current calculation is negative, 0 will be inserted.

I haven't tested this, but this should work:

$sql = "UPDATE products SET stock = GREATEST(stock - '$quantity', 0) WHERE product_id = '$id'";

I'd just be careful you aren't prone to SQL injection here as you are passing $quantity and $id directly to the query string and if this is from a $_POST or $_GET request variable then the query can be manipulated.