搜索引擎的SQL查询（PHP/MySQL）

Question

我正在为我的web项目（PHP/MySQL）开发搜索引擎。 用户应该能够通过一定的条件下

1. 位置找到一家酒店
2. 设施
3. 语言

表是如下

1. tblHotels * HOTEL_ID * PK hotel_name
个
2. tblLocations * LOCATION_ID * PK LOCATION_NAME
3. tblFacilities * facility_id * PK facility_name
4. tblLanguages * LANGUAGE_ID * PK LANGUAGE_NAME
5. tblHotelLocations * LOCATION_ID * FK * HOTEL_ID * FK
6. tbl酒店设施 * facility_id * FK 个* HOTEL_ID * FK
7. tblHotelLanguages * LANGUAGE_ID * FK * HOTEL_ID * FK

例如用户想搜索的酒店，位于旧金山（* LOCATION_ID *） 配有免费的Wi- （* facility_id *）和Pool（* facility_id *） 工作人员会说英语（* language_id *）和西班牙语（* language_id *）

显然，用户可能会更改搜索条件，例如将早餐添加到设施或德语语言。 在这种情况下，搜索结果应该反映所有条件都成立的酒店。

或者他可能不会选择任何设施（语言等），在这种情况下，只有没有设施的酒店应该退回。

可以用一个查询来做到吗？我很确定它是以及以某种方式与INNER JOIN连接。但我只是停留:(任何线索事先

感谢

更新：？！DB模式连接

/* 
-- 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,''); 

Answer 1

Select * 
from tbHotels h, tblFacilities f, tblHotelFacilities hf 
where f.facilitiesName IN (?,?,?) AND hf.facilityId = f.id 
AND hf.hotelId = h.id 

这是唯一的设施查询，U可以添加地点和语言......

？可以像这样添加，

假设你有一个ArrayList包含用户输入的设施名称。

String query = " Select * " + 
"from tbHotels h, tblFacilities f, tblHotelFacilities hf"+ 
"where f.facilitiesName IN <facilities> AND hf.facilityId = f.id "+ 
"AND f.hotelId = hf.hotelId"; 

String x = ""; 
int length = ArrayListOfFacilityNames.length(); 
while(length>0) { 
    x=x+"?, "; 
    length = length-1; 
} 
//remove last "," from string x 

x=x.substring(0, x.length -1); 

    //paste this string of facilities in main query 

    query = query.replace("<facilities>",x); 

    // now ur query contains number of question marks equivalent to number of facilities  entered by user 

    //make prepares statement with String query 

    Iterator fnames = ArrayListOfFacilitiesNames.Iterator(); 
    int i=0; 
    while(fnames.hasNext()) 
    { 
    String name = fname.next(); 
    preparesStatement.setString(i,name) 
    i++; 
    }