Search Box Problems in Dreamweaver

Report · Aug 03, 2011

HI

I am having a really stupid problem with my search box. I have created a form which has a drop down list for 'Positions' and one for 'Location' and then I have a keyword search field. The keyword search field works fine but I am struggling with the drop down fields. Currently, if i do not make a selection from both drop down fields and say only make a selection on one, it doesn't bring up any results. I would like to insert some sort of AND/OR statement in my SQL but not sure how to do this - does anyone have any ideas - this is my current statement:-

SELECT `Position`, Nursery, Location, Salary, Email, ContactNo, JobDescription, JobID, town, jobs.jobtitle

FROM jobs

WHERE `Position` = colname and jobs.Location = varLocation or jobtitle LIKE %colname2% or Nursery LIKE %colname2% or Position LIKE %colname2% or Location LIKE %colname2% or town LIKE %colname2%

Also, I have another two issues if anyone can help:-

I would like to insert a 'Select All' Category in the drop down field - can't seem to find anything telling me how to do this. I just want people to be able to select all items in the list if they want to.

Finally, in my drop down lists - when i go into List Values - I have all the positions listed under the label side and exactly the same position listed on the right hand 'Value' side. All tutorials i read seem to say that you can list values as anything but if i change it to anything other than what is listed as the label it returns a blank result when i carry out the search. Also, when i changed the label from Nursery Managers to Nursery Managers Jobs it returned a blank result. I know there is something simple i should be doing/adding somewhere but can't find anything to tell me what.

Any help would be very much appreciated

THANKS

Report · Aug 03, 2011

When combining AND with OR in the SQL where clause, you need to use parenthesis to explicitly say how you want logic to be combined.

In addition, if someone does not make a selection, or chooses 'Select All', then do not include that column in the where clause at all. You do this by dynamically building the where clause; check the values from the submission and if they are not null, add them to the where clause, otherwise do not add them.