I've created an online shop and I noticed an issue with the search-function.
If the user enters a string to search, I do a
SELECT * FROM PRODUCTS WHERE DESCRIPTION LIKE %....%
This works good, but the problems exists in the special charachters. Imagine I have products like "préparé" or "côte à l'os". When the user searches for "prepare" or "cote a l'os", the products won't be found, nonetheless they are in there.
The problem is also there in the other way. If the user searches for "dûroc" but my product is in the database as "duroc", he will not find it.
It can also be something in between: the users searches for "preparé" or "côte a l'os".
How can I fix this problem, or at least make it smaller?
MySQL 5.1.73 / PHP 5.6
I did some extra testing after reading the given answers and I can give some extra details.
If I use tools like MySQL Workbench or Sequel Pro, it all works just fine. So I can execute
SELECT * FROM PRODUCTS WHERE DESCRIPTION LIKE '%prepare%'
SELECT * FROM PRODUCTS WHERE DESCRIPTION LIKE '%preparé%'
; they both return every record where the description contains "prepare", "préparé" or "preparé", exactly as I want it.
I did not know MySQL did this, because SQL Server does not (just tested it)
Then back to the site. If the users types "prepare", it still goes well, all records are returned as above.
But then, if the users types "preparé", it returns nothing, not even those with "preparé" in the description.
This is the query in PHP that I use:
$qryPLU = sprintf(SELECT * FROM PRODUCTS WHERE DESCRIPTION LIKE "%%%s%%", $conn->real_escape_string($mywgid));
When I echo this I get:
SELECT * FROM PRODUCTS WHERE DESCRIPTION LIKE '%PREPARé%'
So somewhere in it's way to the database that é is changed I think, but I don't know how, where or why.
Somehow @Reversal was getting in the right direction I see now.