jay jay - 5 months ago 6x
SQL Question

PHP Mysql field value based on another field value

Not sure how to ask this but I have a database of products and their prices. The price consist of 3 columns (Current Price, Discounted Price, Previous Price).

I use fgetcsv() to read a CSV of new records then the Mysqli query below to insert or update new records to my database:

$query = "INSERT INTO products (prodname,currprice,prevprice,discprice) VALUES ('$EachRow[0]','$EachRow[1]','$EachRow[2]','$EachRow[3]') ON DUPLICATE KEY UPDATE currprice='$EachRow[1]';";

The problem is for existing products. I only want to update their currprice if their prevprice is different than the currprice and to update them, the new currprice must be used. I was hoping for something in mysqli like

"UPDATE IF $EachRow[1]<>currprice SET prevprice=currprice,currprice='$EachRow[1]'"

Basically for existing records, if the current price is has changed, I want the previous currprice recorded as prevprice before updating currprice to the new $EachRow[1].

Is there a way to make this happen? Can we make IF statements in mysqli query?


Yes, you can use IF clause after Duplicate Key Update

INSERT INTO tbl (name, some_column, some_other)
VALUES (username, some_data)
some_other = IF(some_other > somevalue, lastupdate)

These are called Flow Control Operators