asked on

mysql ?join? how to allow a table of synonyms/misspellings/thesaurus

This project has a pretty simple (web) user-search into the Description field of their db.
currently we use Descr LIKE '%$SearchTerm%'

How is easiest to allow (the cross product of) $SearchTerm to "first" be looked up in a table of synonyms (and common misspellings) so then that "translated" (correct/alternate) keyword can also be searched for in the Description column along with the existing search for $SearchTerm to still happen.

This is an InnoDB table. (I'd rather not get into a full text search using a different db model)

thanks

Guy Hengel [angelIII / a3]

I would do that in a separate query, and show the user the possible spelling alternatives.
see how google does it, showing a link with the suggested search alterternatives

willsherwood

ASKER

I hear your point.
In this particular application however, we'd rather not embarrass the user's misspellings and just give the right result.
Should i just do an outer loop and cross-product that way? (i.e.,
1. find synonym corrections list
2. for each correction, then do a normal search
3. then (add on) the listings for the original/regular search

i was hoping there was a sql trick with JOIN or something to do the crossproduct simultaneously.

willsherwood

ASKER

example

translation table:

ID old new
------------------------
10 taper candle
11 candel candle

products table:

ID Descr
----------------------
1 red candle long
2 holiday taper
3 linen cloth

if user searches for candel it displays ID1
if tape it displays ID1, ID2

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

willsherwood

ASKER

that's the advice i was seeking, THANKS