Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 329
  • Last Modified:

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)


  • 3
  • 2
1 Solution
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
willsherwoodAuthor Commented:
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.
willsherwoodAuthor Commented:

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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you don't need to ask the user indeed.
you can first query for any "misspellings" and replace the search criteria as needed before running the actual query
I would not do that in a single query
willsherwoodAuthor Commented:
that's the advice i was seeking,  THANKS

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now