I have a table "products" with columns like:
| price | discount_price | start_discount | end_discount | ... |
*start_discount and end_discount is a UNIX timestamp
Current price of a product depends on time - if 'time()' is between 'start_discount' and 'end_discount' then the current price is 'discount_price', otherwise is 'price'
I am trying to return "current_price" variable. In PHP, I would just write:
$current_price = (time() > $start_discount && time() < $end_discount)? $discount_price : $price;
How can I achieve the same as above in MySQL without involving PHP?
I have this so far:
SELECT *, (**price OR discount_price**) as current_price FROM products WHERE active = 1 AND current_price BETWEEN '100' AND '200' ORDER BY current_price;
Here is a way to do it, I am only selecting the price you can select whatever you want from the table
select
case when
now() > start_discount AND now() < end_discount then discount_price
else price end as current_price
from
products
WHERE
active = 1
AND price BETWEEN '100' AND '200'
ORDER BY current_price
EDIT : Just noticed you are using current_price in the where condition, which you can not do since its an alias so you may need to use a table column in the where clause or use having clause as
select
case when
now() > start_discount AND now() < end_discount then discount_price
else price end as current_price
from
products
WHERE
active = 1
having current_price BETWEEN '100' AND '200'
ORDER BY current_price
Here is a test I did
mysql> create table products (price int, discount_price int ,start_discount date ,end_discount date);
Query OK, 0 rows affected (0.08 sec)
mysql> insert into products values (100,60,'2014-10-01','2014-11-01'),(200,160,'2014-10-01','2014-10-10'),(300,200,'2014-11-01','2014-11-15');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select case when now() > start_discount AND now() < end_discount then discount_price else price end as current_price from products order by current_price;
+---------------+
| current_price |
+---------------+
| 60 |
| 200 |
| 300 |
+---------------+
3 rows in set (0.00 sec)
You could do this with an IF statement in mySQL
select id, name,
if(startDiscount_at <= now() && endDiscount_at > now(), discountPrice, price) as current_price
from products
where active = 1
having current_price between 100 and 200
order by current_price;