SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Zealot jonsteele's Avatar
    Join Date
    Jan 2001
    Location
    Toronto
    Posts
    106
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    match..against substrings

    Hi,

    I am using a match..against query to pull up search results from a database. I have the FULLTEXT index set up and everything is working fine.

    But as I understand, this method only searches words. Is there a trick/workaround to pull up substrings as part of the search.

    Current Situation:

    Say the user searches for "tools" and a listing has the word "tools" somewhere in the description field in the database. The record is recognized and retrieved. Perfect.

    Now say the user searches for "tool". The same record with "tools" in the description will not be found.

    Anyone have experience with this and found a solution?

    Using LIKE will not really work because I will need to match every search word with every field in the database. So if the user puts in 4 keywords to search for, to search the database against the 4+ index fields would mean 16+ LIKE clauses in the SQL ![img]images/smilies/eek.gif[/img].

    Cheers,
    Jon

  2. #2
    SitePoint Addict xDev's Avatar
    Join Date
    Jul 2003
    Location
    Moncton, New Brunswick, Canada
    Posts
    247
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Jon,

    You can do a fulltext search in boolean mode:

    SELECT * FROM your_table WHERE MATCH (field1,field2,field3,field4)
    AGAINST ('tool*' IN BOOLEAN MODE);

    This will be a match for tool, tools, toolshed.. etc

    The drawback to this is that a boolean mode search won't sort rows in order of decreasing relevance.

    http://www.mysql.com/doc/en/Fulltext_Search.html

    -xDev

  3. #3
    SitePoint Zealot jonsteele's Avatar
    Join Date
    Jan 2001
    Location
    Toronto
    Posts
    106
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Unfortunately, this version of mysql does not support boolean mode.

    Any other options?

    If there is no other option maybe an upgrade is in order!!

    Thanks xDev,
    Jon


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •