Hi.
I have a MySQL table where a product price ranges are set according to
quantities.
For instance for a certain product you'd have (besides id columns):
range_begin | range_end | range_price
0 | 35 | 1.5
36 | 70 | 2
71 | - | 3
Where '-' is always the character that means '..and above' and closes the
entire range field. So in the above example a quantity of 71 and above has a
price tag - $3.
I have limited success with writing the simple query that when given a
quantity returns its correct price range. This is what I came up with:
SELECT `range_price` FROM `price_ranges` WHERE `product_id` = '$product_id'
&& `size_id` = '$size_id' && ((`range_begin` <= '$quantity' && `range_end` =
'-') || (`range_begin` <= '$quantity' && `range_end` >= '$quantity'));
It partially works. But given the above example, it'll correctly return for
a quantity of 71-79 the price of 3, but for anything above that it'll return
the wrong price - 1.5. It works fine if I remove the second part of the OR
construct.
Also it returns nothing for the quantities of 4-9 (?!).
I've tested this on my machine (MySQL ver: 3.23.54) and on the customer's
server
(ver: 4.0.14), and got the same results. Obviously I'm not getting the
logical scheme right. If anyone can point out the problem or suggest a
better way to write the query I'd appreciate it.
Thank you, Boaz Amit
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
