searching a range when only high/low in database

[turpentyne]

I think I've coded myself into a corner.  I have numerous items entered with measurement extremes - highest and lowest. Now I want somebody to be able to search for an item that could match a specific length.

 

So I could have several items that are entered into a database like this:

 

item_id  |   length  |   high_low_param
-----------+-----------+----------------------
1234      |    12 cm  |  high
-----------+-----------+---------------------
1234      |    6 cm    | low

 

If somebody searches for an item that is 8 cm long, I want the results to be item 1234 and any other items that span that range. I'm not sure how to do this? array_fill ? I don't know. I don't want to slow down the database too much, and I don't want to have to put an entry for every single length each item could be.

 

Any suggestions?

[jcbones]

I would store it all in one line:

item_id  | low_length     | high_length    | measurement_unit
----------+----------------+-----------------+------------------------
1234     | 6               |  12             | cm

 

Then query it:

SELECT * FROM table WHERE  measurement_unit = 'cm' AND 8 BETWEEN low_length AND high_length 

 

or, along those lines.

[turpentyne]

hmmm. I was hoping to avoid having to redo the table. haha!

 

So, if I were to do this, how would it go cross-unit? In other words:

 

low= 2 mm

high =2 cm

 

Do I need to create an id that matches each measurement across the whole metric table?

 

1 = 1 micron

1000 = 1 milimeter

10000 = 1 cm

1000000 = 1 meter

 

and all the numbers between? eek!

 

 

[Psycho]

You really need to redo your table. By storing the values as "12 cm" you lose the ability to use a lot of the database functions.

 

I would suggest you do as jcbones suggested and have one record for each entry with a high and low value. I would also suggest you store the values as numeric fields in the smallest unit. So, if you have "6 cm" save it as 6000. Then create a couple of functions to convert microns into an appropriate length and to convert lengths into the numeric value for microns.

[sandeep529]

I think you could join that table to itself .

 

If the table name is dimensions, you can use something like

 

Select  * from dimensions as `a` join dimensions as `b` on (`a`.`item_id` = `b`.`item_id` and `a`.`high_low_param` ='high' and `b`.`high_low_param` = 'low') where `a`.`length` < 12 and  `b`.`length` >6