Jump to content


Photo

Select Statement based on search


  • Please log in to reply
3 replies to this topic

#1 mawilliams

mawilliams
  • Members
  • Pip
  • Newbie
  • 2 posts

Posted 31 May 2006 - 04:29 PM

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:

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

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

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 31 May 2006 - 05:04 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 mawilliams

mawilliams
  • Members
  • Pip
  • Newbie
  • 2 posts

Posted 01 June 2006 - 09:00 AM

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

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 01 June 2006 - 05:41 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users