Searching a range 2

[rdcss]

I have a database where the customer wants to be able to find a price range (she has entered a definite price for each item)... how do I set it up for that sort of search?

Thanks in advance!

 

[TonyGroves]

Need a bit more info there!

-----
ALTER world DROP injustice, ADD peace;

 

[tweenerz]

I'll take a stab with the limited info supplied.

Have you tried using the 'BETWEEN' statement?

select * from products where price between 100 and 300;

 

[rdcss]

This goes with my other question... currently the field is set as a varchar()... can this sort of search ("between") be used with a string or would I need to make that field an int field?

That is the sort of thing I'm looking for, though.

What further info would you need, Tony?

 

[TonyGroves]

BETWEEN can certainly be used with strings, but ...

Are you saying you are storing prices in VARCHAR fields? If so, then BETWEEN would most likely return incorrect results, as the string '123.45' is "less than" the string '45.67'.

If possible, monetary amounts should be stored as integer fields, for example €123.45 would be stored as 12345. This method gives the best accuracy and speed. You can then use tweenerz's solution, possibly dividing the prices by 100 before displaying them.


-----
ALTER world DROP injustice, ADD peace;

 

[rdcss]

Right, Tony, that's pretty much what I figured. Thanks much!