I need to realize a little search engine. The problem is, that there are data inside the database that use instead of letters like ä ö ü the letters ae oe ue. I need a possibility to find them. I can do this easily with my regex-generator function:
function shittyumlauts($string){
$string = mb_strtolower($string);
$string = preg_replace('/(Ä|ä|ae)/i', '(ä|ae)', $string);
$string = preg_replace('/(Ö|ö|oe)/i', '(ö|oe)', $string);
$string = preg_replace('/(Ü|ü|ue)/i', '(ü|ue)', $string);
return $string;
}
I put it in the query with a >RLIKE $string<. So far so good.
But there are also "Renés" in the database. When I use RLIKE, I cant find them by entering "Rene". On the other side, if I use >LIKE $string<, it finds "René" when entering "Rene" but using a regex is not possible there.
I googled for hours now and I also found a fews similar topics here. But there hasnt been a real solution so far.
What I considered is that I could SELECT the fields and apply a REGEX REPLACE on it like REPLACE(field, [^a-z], '_') (not valid code) and use LIKE.
Does anyone have a clue?
Okay, I found the answer myself. It's proably not best practise. But I works fine. So if you have a couple of characters that could be written otherwise like in my case the german "ö" as "oe" you can build your query like this:
... WHERE REPLACE(LOWER(c_name), 'ö', 'oe') COLLATE utf8_general_ci
LIKE '".mysqli_real_escape_string($link, $search)."' ...
then it will find not only "möglich" by typing "moeglich" (and vice versa), but also "rené" by searching for "rene" (and vice versa)
additionally you have to transform the characters in the search query too:
$string = str_replace(array('ä', 'ö', 'ü', 'ß'),
array('ae', 'oe', 'ue', 'ss'),
mb_strtolower($string));
thats it. I hope this can help somebody :)