Select Statement based on search

[mawilliams]

Hi,

I have a search form that contains 3 dropdown boxes:

- search_bedrooms
- search_price
- search_area

Each of these boxes contain values which will get info from a database when submitted, but they also have a value called [no preference], this is selected when the user doesn't want to search for any particular value.

When submitted it goes through to a page which has a MYSQL SELECT statement:

[code]$query = "SELECT * FROM property_details WHERE area = '$search_area' AND price = '$search_price' AND bedrooms = '$search_bedrooms'";[/code]

What I need to know is if the user selects [no preference] on any one of the dropdowns, how can I make the above SELECT statement not include that in the search, but still search on the other criterias.

(e.g. if in the search_bedrooms dropdown I selected [no preference], how could it be excluded from the search, but still include area and price?)

I know it can be achieved with multiple SELECT statements, but there has to be a more efficient way.

Hope that makes sense.

Many thanks in advance.
Mark

[fenway]

Well, since you're going via PHP anyway, the best way is to build the where clause in pieces, based on the value passed in for each searchable field. If it's specified (by whatever way you determine this), then add it, otherwise, don't.

[mawilliams]

Thanks for that Fenway, the problem is I'm not sure how to go about doing it...could you offer me some guidence?

Many thanks
Mark

[fenway]

I can provide you with pseudo code; my PHP skills are not the best:

- assign the first part of the query -- without any WHERE clause -- to one variable
- create a new array
- check to see if each of the 3 variable is equal to the "no preference value", and push on that specific condition to the above array if it is not
- if this array has length, prepend with " WHERE ", join with " AND ", and append to the original query variable

Hope that makes sense -- I'm not a PHP coder.