Geek Answers Handbook

MySQL - finding records with or without special characters

I created an online store and I noticed a problem with the search function. If the user enters a search string, I doSELECT * FROM PRODUCTS WHERE DESCRIPTION LIKE %....%

This works well, but problems exist in special charachters. Imagine I have products like préparé or côte à l'os. When the user searches “prepare” or “pave l'os”, the products will not be found, however they are there.

The problem is also different. If a user searches for "dûroc", but my product is in the database as "duroc", he will not find it.

It could also be something in between: users are looking for “cook” or “côte a l'os”.

How can I fix this problem or at least reduce it?

MySQL 5.1.73 / PHP 5.6

UPDATE

I read some additional tests after reading these answers and I can give further details. If I use tools like MySQL Workbench or Sequel Pro, everything works fine. Therefore, I can execute SELECT * FROM PRODUCTS WHERE DESCRIPTION LIKE '%prepare%'or SELECT * FROM PRODUCTS WHERE DESCRIPTION LIKE '%preparé%'; they both return every entry where the description contains “prepare”, “préparé” or “prepare”, just like I want. I did not know that MySQL did this because SQL Server did not test it.

Then return to the site. If users type “prepare,” all the same, everything is going well, all records are returned, as indicated above. But then, if users type “drug”, it does not return anything, even those who have “drug” in the description.

This is the request in PHP that I am using:

$qryPLU = sprintf(SELECT * FROM PRODUCTS WHERE DESCRIPTION LIKE "%%%s%%", $conn->real_escape_string($mywgid));

, :

SELECT * FROM PRODUCTS WHERE DESCRIPTION LIKE '%PREPARé%'

- , é , , , , .

- @Reversal , .

+4
3

php/ ISO-8859-1 :

iconv('UTF-8', 'ISO-8859-1//TRANSLIT//IGNORE', $variable)
+1

charset collation.

mysql> set names latin1;
Query OK, 0 rows affected (0.00 sec)

mysql> select 'préparé' like '%prepare%';
+------------------------------+
| 'préparé' like '%prepare%' |
+------------------------------+
|                            0 |
+------------------------------+
1 row in set (0.00 sec)

mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> select 'préparé' like '%prepare%';
+------------------------------+
| 'préparé' like '%prepare%' |
+------------------------------+
|                            1 |
+------------------------------+
1 row in set (0.00 sec)
+1

This is what works for me:

SELECT _utf32 'préparé' = _utf32 'prepare' collate utf32_general_ci;
+--------------------------------------------------------------+
| _utf32 'pr?par?' = _utf32 'prepare' collate utf32_general_ci |
+--------------------------------------------------------------+
|                                                            1 |
+--------------------------------------------------------------+

select _utf32 "côte à l'os" = _utf32 "cote a l'os" collate utf32_general_ci;
+----------------------------------------------------------------------+
| _utf32 "c?te ? l'os" = _utf32 "cote a l'os" collate utf32_general_ci |
+----------------------------------------------------------------------+
|                                                                    1 |
+----------------------------------------------------------------------+
+1
source

More articles:


All Articles