multiple search help

I am a relative newbie to PHP/MySQL development, but understand the basics. I am trying to present a user with a search page containing from 4-10 select boxes containing companies abilities(multiple selections are possible in each box). I would like to then query a database table that contains all of the information for each company, including seperate fields for each ability category (i.e. hosting type, O.S. Support type, etc.) Each field can have several answers in it (i.e. O.S. Supported would be linux and unix and Mac OS). How do I create the query to check the database and figure out that there are several different answers in a field, not just one?

Mike

Hi,

I'm not sure my answer will be as exhaustive as you would expect it to be, but I'll do my best ;)

Conditions in SQL are mainly written in the WHERE clause. If you want to get two conditions on some fields to be fulfilled, then you have to separate them by the AND operator. On the contrary, if you want either one condition or an other, then you have to use the OR operator... You can then combine AND and OR as you like in order to get the results you want.

A quick example:
A table: persons
with fields: id, firstName, name, address, city, country, phoneNumber
You want to get the ids of all the persons that have "Peter" or "Mikael" for a first name and that live in Canada...
Then your SQL request will look like
SELECT id
FROM persons
WHERE (firstName='Peter' OR firstName='Mikael') AND country='Canada';

(Note: This SQL request is not the best one that can be built to get the same results, but it shows how to use and combine AND and OR)


Now back to your problem.
I guess you have criteria like "operating system" or "language" or "hosting type". Those criteria will be the conditions separated by AND...
Now for each criteria, you have several possible options(checkboxes). Those will be the conditions separated by OR.
Of course, when no option has been selected for a given criteria, you should not define any condition for that criteria...

An example:
criteria "Operating Systems"
=> 5 options: "Windows, Linux, Unix, Max OS, Other"
criteria "Language"
=> 2 options: "English, French"
Once the form is submitted, you know that Windows and Linux have been selected for the 1st criteria, and that French was the only one selected for "Language"
Then your task is to generate a SQL request with the following WHERE clause:
WHERE (operating_systems='Windows' OR operating_systems='Linux')
AND (language='French')
Once again, it is not the best request one can build, but it gives you ideas of how it works...

Hope this helps... ;)

/Flood