Results 1 to 2 of 2

Thread: MySQL Match...Against syntax for a partial match?

  1. #1
    Join Date
    Jul 2008
    Charlottesville, VA

    MySQL Match...Against syntax for a partial match?

    I am using the SEARCH AGAINST MySQL syntax in a site I am working on to search several fields for a submitted search. However, it's not working so well for partial matches. My client wants to be able to (for instance) search for a name, such as Smith, and have it find Bob Smith's record in addition to Jim Smithfield.

    Here's the actual query:

    PHP Code:
    MATCH(Issue.keywords,Issue.descriptionAGAINST('{$search}' IN BOOLEAN MODE 
    No, the fields aren't names, but that was the best example I can think of. Any thoughts? I appreciate any help that you can offer.


  2. #2
    Join Date
    Jun 2009

    By adding a star after your $search, the query will match any pattern starting with $search, i.e. searching 'Smith*' will match 'Smith' 'Smithy' etc. Unfortunately there's no way to match $search inside another pattern so searching '*at*' won't match 'data' for example.
    If you need this, you might need to explode the search string (split it using a given delimiter, i.e. space), and then use an OR combination of LIKE clauses. For example, suppose you have a people database where you need to search over 3 fields: firstname, lastname and address. Someone searches for 'ob mit'. First you would split the query in two separate strings 'ob' and 'mit' and then the SQL query would look like:

    SELECT *
    FROM sometable 
    WHERE UPPER(firstname + lastname + address) LIKE UPPER('%ob%')
    OR UPPER(firstname + lastname + address) LIKE UPPER('%mit%');
    That would concatenate the fields you need to search for and try to match them with each substring provided by the user. This should match something like:

    Firstname     Lastname    Address
    Bob           ...         ...
    Robert        Smith       ...
    Mitch         ...         ...
    ...           ...         ... Observatory ...
    Good luck

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

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