I have a MySQL table where a product price ranges are set according to
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
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
(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

Reply via email to