> How can I accomplish the following? > > I have a table called search, > > I have 3 fields, city, country, type > > I also have an imput box where you can type in > a search word. What I need is, say you type in > hotels germany, I need the search to break up > the sentence and then search each 3 fields in the > DB looking for a match. > > So, for example: it would find hotels in type and > germany in country, see that they belong together > and then display those results - how can I do this? > > I know I need to splitup my search phrase into > individual words to search, but i'm stuck.
Two possible approaches spring to mind: 1. Search all three categories for each keyword, ie 'hotel germany' would turn into SELECT * FROM mytable WHERE (country='hotel' OR type='hotel' OR city='hotel') AND (country='germany' OR type='germany' OR city='germany') 2. A translator table: two text fields, 'phrase' and 'search', filled with something like: phrase | search 'germany' | 'country=\'de\'' 'states' | 'country=\'us\'' 'brazil' | 'country=\'br\'' 'hotel' | 'type=2' 'bed' | 'type=1' 'b\&b' | 'type=3' 'london' | 'city=31854' 'paris' | 'city=22059' ... you can then use a search through the translator table to build the select statement that actually does the query. Both of these have drawbacks, mainly that the tables they nominally reference are severely non-normalized; I suspect the proper approach should be a series of queries for each term among a set of normalized tables, followed by a final constructed query... but you get the idea. Another point: single-word searches could be a problem, stumbling over 'New York' or 'bed & breakfast'; it might be worth amending the search to also try consecutive-word pairs. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php