I seem to be full of problems today....

Firstly, I have 3 tables [ flats, facilities and hasFacilities (which links the first two with IDs) ]. Flats i think is notreally important here. facilities contains an ID and a name. hasFacilities contains facilityID and flatID linking the first two together from the normalizatio process.

Im creating a search form where checkboxes are shown, one for each of the facilities within that table. The user will select the relevant ones (any number - none to all) and I want to then query the database to return the flats ( the flatID ) which have all the facilities that the user has selected.

I pretty sure this can be done but i just cant get my head around the query that i need to create - whether thats because i dont have the right knowledge i dont know...

Any helps or points in the right direction are appreciated.

Cheers
Stoot

Table Structures:

flats( flatID, title ... etc )

facilities( facilityID, name )

hasFacilities( flatID, facilityID )

I'd add a flats_facilities table with one-to-many relationships from flatID, facilityID and userID (or whatever you called your user table).

So if user1 selected facility1 from flat1, then you'd have userID1, facility1, flat1 from that list. When they update their selection, delete everything from the old selections and reinsert the new ones and you're back in business.

There's nothing saying you can't have multiple relationships among tables like that.

You could start with

PHP Code:

Select `flatID` FROM `hasFacilities` WHERE 1 = 1 


but then you would need a server side language to check throught the list of possible options like

PHP Code:

$sq .= (isset($_POST['checkbox1']))?"OR `facilitiesID` = '1'":""; 


The user table idea is only if you are trying to save searches which I am not sure that you want to do.

If you have your checkboxes all with the same name the selections will be returned by the form as a comma seperated value list.
So you need to parse this list in whatever server side code you use and concatenate it into AND criterias


SELECT fieldlist FROM hasFacilities as H LEFT JOIN flats AS F ON F.id = H.flatID WHERE H.facilitiesid = criteria1 AND H.facilitiesid = criteria2 .... rest of ANDs ...

Not tested BTW

I should have put AND, I missed the italicized all