Best approach to build a filtering Query?

[mjpr]

Hi!

I'm trying to build a query to filter car makes, models and trims based on price ranges.

My table name is "TblVersoes" and my price field is called "Euros"
So, I have 4 price ranges:

1 -> TblVersoes.Euros >= 15000 AND TblVersoes.Euros < 25000
2 -> TblVersoes.Euros >= 25000 AND TblVersoes.Euros < 35000
3 -> TblVersoes.Euros >= 35000 AND TblVersoes.Euros < 45000
4 -> TblVersoes.Euros >= 45000

The user will be able to select 1 or more prices.
For just one price it's easy.
But what if the user selects price range 1 and 4 or 2 and 3?

How should I build the query for those cases?

My DB is MSSQL and I'm building my webpage with PHP.

Thankx for any help,
Mário

[r937]

But what if the user selects price range 1 and 4 or 2 and 3?

are you really sure you want to offer this choice to users?

please explain why someone would be interested in a car that is between 15000 and 25000 (range 1) or greater than 45000 (range 4) but not between 25000 and 45000

that doesn't make sense

i think you should allow a choice of only one price range

[mjpr]

Hi r937.
You have a good point. I could limit the price choice to only 1 range at a time.
Maybe selecting range 1 and 4 is really silly (it's just another way of saying any price at all), but range 1 and 2, 2 and 3 make as much sense as only range 1 or only range 4, for example.

Thanks

[r937]

in my opinion, your ranges should be inclusive

1 -> up to 25000
2 -> up to 35000
3 -> up to 45000
4 -> over 45000

[mjpr]

That would resolve some issues. The user could only select 1 price range with that approach.

[samanime]

I agree with r937.

However, just to answer the question as a learning exercise.

But what if the user selects price range 1 and 4 or 2 and 3?

If they want 1 and 4, it means it should be valid if the range is within 1 or 4.

1 OR 4
(1) OR (4)
(TblVersoes.Euros >= 15000 AND TblVersoes.Euros < 25000) OR (TblVersoes.Euros >= 45000)

See what I did there?

(Just to note, the last set of parentheses isn't really necessary, it's just there for clarity.)

Also, note that if it was filtered so they were AND-ed together (which you could technically do), they would never return a result, as no number can be >= 45000 and less than 25000.

[r937]

Just to note, the last set of parentheses isn't really necessary

neither is the first set of parentheses

[samanime]

Quite true. =p

[mjpr]

Thanks for all your input guys!