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

Posted on 2009-12-22
Last Modified: 2012-05-08
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)


Question by:willsherwood
    LVL 142

    Expert Comment

    by: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

    Author Comment

    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.

    Author Comment


    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
    LVL 142

    Accepted Solution

    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

    Author Closing Comment

    that's the advice i was seeking,  THANKS

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
    Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL ( several years ago, it seemed like now was a good time to updat…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now