> 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

Reply via email to