While working under e-commerce project in PHP – MySql, You may came under the situation where you need to show the product price based on condition. It means if the product is that is going to be displayed in the search result has any special offer till some date then show the “special offer” otherwise show normal product price.

I was confuse How to do it. But then I got solution. It was The mysql magic :). The “CASE WHEN ( condition ) THEN some_column_1 ELSE some_column_2”. This solved my problem exactly.

The product table is below.

Now you may getting clear what I want to say. When special_status column has value 1 and special_expire date is greater than current time and special_price is greater than 0(zero) , at that show special_price and if any of the condition fails then show net_price

I select query is as below:

$sql = “SELECT products_id, net_price, special_price, special_expire, special_status, products_name,
CASE WHEN (special_price > 0 AND special_expire > NOW() AND special_status != 0)
THEN special_price ELSE net_price END AS price FROM product_master
WHERE products_name LIKE ‘%s%’
HAVING price >= 100 AND price <= 1000”;

The query above I have fired in such a way that you can get all the rows in order to understand how exactly works. The result of the select query is as below:

See The last column : price where you can see the magic of mysql

Hope this may be helpful to the guys working for e-commerce projects.

Sorry for misspelled words and bad English if you find any.

Thank for reading.

Rajan Rawal

Advertisements