PHP update query using IF

Question

I would like to run an update query with an if statement on a MySQL database using PHP. I would like to update the stock in my eshop, but only if the quantity that customer order exists. I tried something like this

$updatequery = "UPDATE products 
                SET stock 
                CASE WHEN (stock >= '$quantity') THEN (stock=stock-'$quantity')
                    ELSE (stock= stock)
                END
                WHERE product='$product'"; `

or like this

$sql = "UPDATE products SET\n"
. "stock = IF(stock>=$quantity, stock=stock-$quantity, IF(stock<=$quantity, stock=stock) )\n"
. "WHERE product=$product"; `

Is there anyone that could help me with the issue I'm having?

What error are you getting?

neelsg
– neelsg

2014-05-26 12:59:37 +00:00
Commented May 26, 2014 at 12:59 — neelsg
– neelsg, Commented May 26, 2014 at 12:59

souslov · Accepted Answer · 2014-05-26 13:30:59Z

2

You can't return expression like stock = stock from your CASE . Try the following :

$sql = "UPDATE products 
        SET stock = CASE
                        WHEN stock >= $quantity THEN (stock - $quantity)
                        ELSE stock 
                    END
        WHERE product = '$product' "

or

$sql = "UPDATE products 
        SET stock = IF(stock >= $quantity, (stock - $quantity), stock)
        WHERE product = '$product' "

edited May 26, 2014 at 13:30

answered May 26, 2014 at 12:40

souslov

44.7k11 gold badges92 silver badges113 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gordon Linoff Over a year ago

+1 . . . But the single quotes around "quantity" are superfluous (because it should be a number).

Collectives™ on Stack Overflow

PHP update query using IF

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related