I was hoping for a quick solution to this problem.....
I'm allowing users of my site to input a price range to filter the product list. Sounds simple but I need to compare the values against either 'prod_price' or 'product_saleprice' but only compare against 'prod_saleprice' if the value is greather than 0. I don't know how or if I can do some sort of if statement within my where clause?
At the minute my query looks like this:
This produces incorrect results because a product would be shown if it has a regular price of lets say £85 but a sale price of £60. So how can I adapt it to use 'prod_saleprice' if a value exists or just use 'prod_price' if 'prod_saleprice' is 0?
FROM products_to_categories AS ptc
products AS p
ON p.prod_id = ptc.prod_id
AND p.prod_price >= 80
AND p.prod_price <= 100
brands AS b
ON b.brand_id = p.brand_id
products_photos AS pp
ON pp.photo_id = p.default_photo_id
ptc.category_id = 25
ORDER BY p.prod_price ASC
LIMIT 0, 9