search range of values

viveleroi0

I have a shopping cart that loads products from a database. Right now I have a search that will search the Price, and another for the Date added, to find a match. Like search for product listed as $150.
But, thats not very useful. How would I make a loop or something that would allow all products with prices between $100 and $150 to be displayed?

Quevas

SELECT * FROM Products WHERE Price BETWEEN 100 AND 150 is a simple way...

viveleroi0

wow! That's all I would have to do? What about the date? The date is in fromat of mm/dd/yyyy. What about searching between 10/31/2002 and 11/05/2002? I assume you would need a loop to generate all of the dates in between?

viveleroi0

there seems to be some sort of limit. If i type in $1-$100 no results are found, but if I do $70-$100 then it finds the right results... any ideas how to expand this?

[deleted]

BETWEEN has no limitations, (are you sure you retreive all rows, and not accedently forget to fetch those near 100?) but one nasty 'gotcha'; BETWEEN does not always literally mean 'BETWEEN' as in

BETWEEN 1 AND 5 would suggesnt dant only 2,3, and 4 are valid, because they are truly between 1 and 5. This is not always the case.

You could also try:

WHERE price >= 1 AND price <=100

which is effectively the same, but now you can be sure that 1 and 100 also count.

viveleroi0

Ill try that... Maybe you can explain why when I have BETWEEN 1 AND 100 I still get results like 280, 150 etc? When I do between 100 and 150 I get correct results. I am selecting all fields (there is only one anyway)

[deleted]

Are you sure that the field that contains the number is actually a numeric field, and not a varchar field?