Search code examples
phpmysqlsqlsql-like

stored with degree symbol, be able to search without, php SQL LIKE statement

I'm having a bit of trouble with a search function I've built.

I have a column in a mysql table called: title (utf8_swedish_ci)

This is 2 titles out of around 20.000:

50° Riesling Réserve Trocken  
50° Parallel Riesling Trocken

Is there somehow I can make it work for the user to search 50 Riesling or 50 Parallel, that is without the degree symbol?

My code:

$search = str_replace("\'","’", $_GET['searchString']);
$search = htmlentities($search);
$search = esc_sql($search);
$search = trim($search);
if(!empty($_GET["searchString"])){
    $str .= "`title` LIKE '%".$search."%' AND ";
}

SQL STATEMENT (part of it, regarding this):

$res = $wpdb->get_results("SELECT * FROM searchtbl WHERE ".$str." ORDER BY ".$sortby." ".$sort." LIMIT 500");

As an extra question: Is there some "easy" way to make the user able to search 50 Riesling and find both items? (It's relevant for a lot of titles not just these two)

Solution

  • You have multiple options, which you can even combine to achieve the desired output.

    1. Create another field in the table in which you store the titles without any specials characters, such as ° and your run the serach on both columns or you strip the special characters from the seraches as well. If you have mysql v5.7.6 or newer, then using generated columns is an ideal solution for this.

    2. You can remove these special characters 'on the fly', while searching in the where criteria. Again, generated columns would probably be a faster solution.

    3. Use fulltext index and search. However, you need to reduce the default minimum word length to 2 and you probably have to play around with the collation file as well. This SO topic contains more information on this.

    4. You can split the search string into words in php for example using explode() and serach for the words not in a single serach criteria, but in separate ones.

    The advantage of the last 2 approaches would be that they would find both records if 50 riesling were the search criteria.