I'm trying to write a query that basically is searching by price, though sometimes the items may have a discount on them and this price isn't stored in the DB, only the discount percent and dates the discount runs between are, so we have to work out the price of the item on the fly to see if it should be included.
Additionally, if the item is an auction we ignore the sale price and just look at the
max_bid to work out the current price (max bid is only set if the item has at least 1 bid).
We know it is NOT an auction if
start_price == buyout_price, so know we have to figure out if the item is on sale or not and if so calculate the sale price to see if to include it in their requested price range.
I have this code..
SELECT whatever FROM auctions AS a LEFT JOIN .............. WHERE .............. AND IF(a.start_price == a.buyout_price AND a.discount_start_date < UNIX_TIMESTAMP() AND a.discount_end_date > UNIX_TIMESTAMP() AND a.discount > 0, (a.buyout_price - ((a.buyout_price / 100) * a.discount)), IF(a.max_bid > a.start_price, a.max_bid, a.start_price)) >= '500' AND IF(a.start_price == a.buyout_price AND UNIX_TIMESTAMP() > a.discount_start_date AND UNIX_TIMESTAMP() < a.discount_end_date AND a.discount > 0, (a.buyout_price - ((a.buyout_price / 100) * a.discount)), IF(a.max_bid > a.start_price, a.max_bid, a.start_price)) <= '700' AND ..............;
As you may be able to make out I'm trying to get all results with prices between 500 - 700.
I'm also getting a error which appears to have something to do with the
UNIX_TIMESTAMP(), though not sure why!?
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '== a.buyout_price AND a.discount_start_date < UNIX_TIMESTAMP() AND a.discount_e' at line 1