I'm developing a search engine for my web project (PHP/MySQL). User should be able to find a hotel by certain conditions
- Location
- Facilities
- Languages
tables are as following
- tblHotels *hotel_id* PK hotel_name
- tblLocations *location_id* PK location_name
- tblFacilities *facility_id* PK facility_name
- tblLanguages *language_id* PK language_name
- tblHotelLocations *location_id* FK *hotel_id* FK
- tblHotelFacilities *facility_id* FK *hotel_id* FK
- tblHotelLanguages *language_id* FK *hotel_id* FK
For example user wants to search for hotel, located in San Francisco (*location_id*) with Free Wi-Fi (*facility_id*) and Pool (*facility_id*) where staff speaks English (*language_id*) and Spanish (*language_id*)
Obviously, user may change search conditions, like adding Breakfast to facilities or German to languages. In this case, search results should reflect hotels, where all conditions are true.
Or he may not pick any facilities (languages etc), in this case, only hotels with no facilities at all should be returned.
Is it possible to do it with one query? I'm pretty sure it is and somehow connected with INNER JOINs. But i'm just stuck :( Any clue?
Thanks in advance!
UPDATE: DB Schema attached
/*
-- Query: desc tblHotels
-- Date: 2011-11-02 09:37
*/
INSERT INTO `tblhotels` (`Field`,`Type`,`Null`,`Key`,`Default`,`Extra`) VALUES ('hotel_id','int(11)','NO','PRI',NULL,'auto_increment');
INSERT INTO `tblhotels` (`Field`,`Type`,`Null`,`Key`,`Default`,`Extra`) VALUES ('hotel_name','varchar(45)','YES','UNI',NULL,'');
/*
-- Query: desc tblFacilities
-- Date: 2011-11-02 09:37
*/
INSERT INTO `tblfacilities` (`Field`,`Type`,`Null`,`Key`,`Default`,`Extra`) VALUES ('facility_id','int(11)','NO','PRI',NULL,'auto_increment');
INSERT INTO `tblfacilities` (`Field`,`Type`,`Null`,`Key`,`Default`,`Extra`) VALUES ('facility_name','varchar(45)','YES','UNI',NULL,'');
/*
-- Query: desc tblLocations
-- Date: 2011-11-02 09:37
*/
INSERT INTO `tbllocations` (`Field`,`Type`,`Null`,`Key`,`Default`,`Extra`) VALUES ('location_id','int(11)','NO','PRI',NULL,'auto_increment');
INSERT INTO `tbllocations` (`Field`,`Type`,`Null`,`Key`,`Default`,`Extra`) VALUES ('location_name','varchar(45)','YES','UNI',NULL,'');
/*
-- Query: desc tblLanguages
-- Date: 2011-11-02 09:37
*/
INSERT INTO `tbllanguages` (`Field`,`Type`,`Null`,`Key`,`Default`,`Extra`) VALUES ('language_id','int(11)','NO','PRI',NULL,'auto_increment');
INSERT INTO `tbllanguages` (`Field`,`Type`,`Null`,`Key`,`Default`,`Extra`) VALUES ('language_name','varchar(45)','YES','UNI',NULL,'');
/*
-- Query: desc tblHotelFacilities
-- Date: 2011-11-02 09:39
*/
INSERT INTO `tblhotelfacilities` (`Field`,`Type`,`Null`,`Key`,`Default`,`Extra`) VALUES ('tblHotelFacilities_id','int(11)','NO','PRI',NULL,'auto_increment');
INSERT INTO `tblhotelfacilities` (`Field`,`Type`,`Null`,`Key`,`Default`,`Extra`) VALUES ('facility_id','int(11)','YES','',NULL,'');
INSERT INTO `tblhotelfacilities` (`Field`,`Type`,`Null`,`Key`,`Default`,`Extra`) VALUES ('hotel_id','int(11)','YES','',NULL,'');
/*
-- Query: desc tblHotelLocations
-- Date: 2011-11-02 09:39
*/
INSERT INTO `tblhotellocations` (`Field`,`Type`,`Null`,`Key`,`Default`,`Extra`) VALUES ('tblHotelLocations_id','int(11)','NO','PRI',NULL,'auto_increment');
INSERT INTO `tblhotellocations` (`Field`,`Type`,`Null`,`Key`,`Default`,`Extra`) VALUES ('location_id','int(11)','YES','',NULL,'');
INSERT INTO `tblhotellocations` (`Field`,`Type`,`Null`,`Key`,`Default`,`Extra`) VALUES ('hotel_id','int(11)','YES','',NULL,'');
/*
-- Query: desc tblHotelLanguages
-- Date: 2011-11-02 09:39
*/
INSERT INTO `tblhotelanguages` (`Field`,`Type`,`Null`,`Key`,`Default`,`Extra`) VALUES ('tblHotelLanguages_id','int(11)','NO','PRI',NULL,'auto_increment');
INSERT INTO `tblhotelanguages` (`Field`,`Type`,`Null`,`Key`,`Default`,`Extra`) VALUES ('language_id','int(11)','YES','',NULL,'');
INSERT INTO `tblhotelanguages` (`Field`,`Type`,`Null`,`Key`,`Default`,`Extra`) VALUES ('hotel_id','int(11)','YES','',NULL,'');