mysql query - confused 1

[richcleverley]

Hi all,

Hope someone can help.

I need to construct a mysql select statement but am confused as to how to go around it.

Basically, in php a search form is submitted that passes user data that needs to be searched for on the data in a table. The problem is that some of the fields on the form are optional but I want them to be used in the event that they are filled in.

So, the form asks for

Type of property - data taken from a dropdown and correlates to an enum field in the database
Number of bedrooms - simple INT field and optional
How many people the property sleeps - same as bedrooms
Minimum price - queries a single field set in the database and optional
Maximum price - as minimum price

The form also asks for start and end dates of the visit which are held in a different table as mysql date format. These are also optional but I'm not too bothered about them at the moment as as soon as I can construct the query for he other data I will be able to work out how to do this.

I know I could write various different queries and use php to decide which one to use but it seems silly to have to do this when a single query could be used (which I am sure it is possible to do)

Hope someone can help out here, and thanks in advance.

Richard

 

[r937]

test each form field to see if it's present, and if it is, append another condition onto the end of the WHERE clause, which you have conveniently initialized with 1=1

select ...
  from ...
 where 1=1
[red]if $formtype[/red]
   and form = '$formtype'
[red]endif[/red]
[green]if $formbedrooms[/green]
   and bedrooms = $formbedrooms
[green]endif[/green]
etc.

r937.com | rudy.ca

 

[hvass]

You would use php code to construct a query - adding a term to the where clause for each field that the user populated. Then submit the query to mysql.

 

[richcleverley]

Absolutely brilliant, thanks loads for that. So simple yet wouldn't have got there on my own.

Richard