CODEWITHSUNDEEP
phpmysql
user1341104
user1341104

Reputation: 1398

mysql price range with or in range

I have a table products with price and sale columns (float 8,2). Products without sale price will have values as shown below. price 13.20 sale 0.00

And products with sale price as: price 10.00 sale 08.00

I'm trying to get all products where price is between $min and $max or sale is between $min and $max.

 select * from `products`  where

( (price BETWEEN 0.00 AND 22.00) OR (sale BETWEEN 0.00 AND 22.00) )
and status = 1 and sold = 0 and deleted = 0 order by id desc limit 12

But this is not working? Can anyone help me to fix this? Thanks in advance.

Ok i tested this one - 

 select * from `products`  where

( (price BETWEEN 0.00 AND 22.00) OR (sale BETWEEN 0.00 AND 22.00) )
and status = 1 and sold = 0 and deleted = 0 order by id desc limit 12

Problem i found that it works if min price is like for example 0.01 but if min price is 0.00 i get products with no price limits. Why query hates 0.00 min price? oO

Upvotes: 0

Views: 840

Answers (3)

rccoros
rccoros

Reputation: 590

You can use BETWEEN

select * from `products` where (price BETWEEN $min AND $max) OR `sale` >= $min AND `sale` <= $max) and `status` = 1 and `sold` = 0 and `deleted` = 0 order by `id` desc limit 12

Upvotes: 0

Krish R
Krish R

Reputation: 22711

Can you try this, Added BETWEEN min AND max

  select * from `products`  where       
  ( (`price` BETWEEN $min AND $max) OR (`sale` BETWEEN $min AND $max) )       
  and `status` = 1 and `sold` = 0 and `deleted` = 0 order by `id` desc limit 12

Upvotes: 0

Xardas
Xardas

Reputation: 142

I think, that you forgot braces between OR substatements:

select * from `products`
where (
    (`price` >= $min AND `price` <= $max) OR (`sale` >= $min AND `sale` <= $max)
) and `status` = 1 and `sold` = 0 and `deleted` = 0 order by `id` desc limit 12

Upvotes: 0

Related Questions