Re: Ranges query gone wild

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



"Boaz Amit" <boaz17@netvision.net.il> wrote in message
000f01c3964d$b501fb40$6400a8c0@w5978.tdak.com">news:000f01c3964d$b501fb40$6400a8c0@w5978.tdak.com...
>
> 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     hugh_bothwell@hotmail.com     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


[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux