(repost: first try bounced)
"Boaz Amit" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
>
> 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.
First danger signal: duplication of data (or, storage of
data that should be a result of calculation).
For a given product, range_end = (range+1)_begin - 1
Second danger signal: needing a 'bad data' type.
In some cases, may be necessary to have a separate
valid-data field, but better to simply not allow bad
data if at all possible.
Instead, remove the redundant field:
range_begin | range_price
0 | 1.5
36 | 2
71 | 3
and the query becomes
SELECT range_price
FROM price_ranges
WHERE
product_id = '$product_id'
&& size_id = '$size_id'
&& range_begin <= '$quantity'
ORDER BY range_begin DESC
LIMIT 1
--
Hugh Bothwell [EMAIL PROTECTED] Kingston ON Canada
v3.1 GCS/E/AT d- s+: a- C+++ L++>+++$ P+ E- W+++$ N++ K? w++ M PS+
PE++ Y+ PGP+ t-- 5++ !X R+ tv b++++ DI+++ D-(++) G+ e(++) h-- r- y+
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
