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
