Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

mysql query - confused 1

Status
Not open for further replies.
Jul 28, 2005
358
FR
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
Code:
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
 
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
Code:
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
 
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.
 
Absolutely brilliant, thanks loads for that. So simple yet wouldn't have got there on my own.

Richard
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top