Search code examples
phpmysqlsearchsql-likesymbols

Mysql query LIKE search and using strings with symbols

My problem is having symbols used in the search query. I want users to be able to use symbols without being a problem, but the LIKE function in mysql seems to not be the solution so I need some help.

EXAMPLE: If someone searches for "Blue's car" and "Blues car" is in the database, this query will return with 0 results. OR viseversa, if someone searches for "Blues car" and "Blue's car" is in the database, this query will return with 0 results as well.

This is an example of what I'm currently using:

("SELECT Title FROM MyData WHERE Title LIKE '%".$search."%'")

Is there another way on providing better search results?

Thanks.

Solution

  • Search Field

    You could have an additional "search" field, that stores the title without punctuation and when user enters search string, strip out punctuation before applying query. You could also remove leading "The".

    I'll further explain the above.

    In the database you have a record like this. The SearchTitle is the Title with the leading "The" and punctuation removed:

    Title                  SearchTitle
------------------------------------------
The Blue's Clues       Blues Clues

    Here, the user knows the exact title and enters the following search string:

    The Blue's Clues

    You strip out the leading "The" and the punctuation, yielding the following:

    Blues Clues

    Your query looks like this

    SELECT Title FROM MyData WHERE SearchTitle LIKE '%Blues Clues%'

    Here, the user doesn't know the exact title and enters the following:

    Blues Clues

    Stripping the leading "The" and the punctuation, still yields the same thing:

    Blues Clues

    Your query stays the same, and matches what's in the search field:

    SELECT Title FROM MyData WHERE SearchTitle LIKE '%Blues Clues%'

    This can be further improved. The key is to apply the same rules to the search string as to what's stored in the search field. For example, convert "two" and "II", to "2", etc., remove additional words like "ON" and "AND", etc.

    Full-Text Searches

    MySQL FullText searches use "Natural Language" searches. I'm not sure if this would do the trick in your case, but it might be worth researching.