> 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.
Advertising
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
