I have a similar search. My table setup looks like this TABLE vendors: [ id | category_id | bla | bla | city | state | zip ]
TABLE key_list: [ id | category_id | word ] sample data in key_list (category #1 - auto repair) [ 1 | 1 | automobile ] [ 2 | 1 | car ] [ 3 | 1 | repair ] [ 3 | 4 | automobile ] You must enter each keyword into the keylist table but this makes the search quite fast when 'word' is indexed (it may also contain phrases ie. 'new york') For multiple words I build an array of matching categories $matches_array['word'][] = $cat_id then use array_intersect() to get the matches for multi words In the example a search for "auto repair" or "car repair" both return category_id | 1 which is 'auto repair' keywords can point to more than one category and many key words can point to a single category. as in the example "auto" points to cat_id's 1 and 4. I have separate input boxes for city/state [if city is numeric then search is zip code] So query is something like this (if all fields contain data) SELECT $fields FROM $table WHERE city LIKE '$city%' AND state ='$state' AND category_id IN ($category_matches); category_id could be anything really - maybe hotel_id would work for you criticisms and suggestions appreciated :) olinux --- Hugh Bothwell <[EMAIL PROTECTED]> wrote: > > 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 > __________________________________________________ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
