3

I've searched for a question for hours and I haven't found an answer that was suitable for me, so... Here I come...

I'm Hungarian and we use the following accented characters in our language: áéíóöőúüű (and of course the capital counterparts) I want to make a smart search in php where the user is allowed to enter a search word and it finds the result whether it has the accent or not and both in the MySQl table or the search field. So...

My MySQL table is using utf8_hungarian_ci I can do the php conversion so that either the user types in 'Bla' or 'blá', it will return 'bla' and we are searching the mySQL database.

But my problem is... My database might have a 'bla fér' or a 'bláter' field entry. But if we search with 'bla' (from PHP) it only return 'bla fer'. How can I convert the field I'M searching to make 'bla fér'-> 'bla fer' and 'bláter' - > 'blater'. So essentially...

I want to get rid of the accented characters and make them into unaccented ones. But of course, only for the sake of searching. Please help! Thank you!

EDIT:

<?php 
$search = $_GET["search"]; // May contain áéíóöőúüű
$accented= array("Ö","ö","Ü","ü","ű","Ó","ó","O","o","Ú","ú","Á","á","U","u","É","é","Í","í"," ","+","'","ő", "Ű", "Ő", "ä","Ä","ű","Ű","ő","Ő");
$nonaccented=array("O","o","U","u","u","O","o","O","o","U","u","A","a","U","u","E","e","I","i","_","_","_","o", "U", "O", "a","A","u","u","o","o");
$search = str_replace($accented,$nonaccented,$search);
$query = "SELECT id, name FROM people WHERE name LIKE '%$search%'"; // Database column 'name' may also contain áéíóöőúüű
?>
  • Can you post the relevant SQL and php code? – barsju Apr 05 '12 at 06:42
  • Possible duplicate of: http://stackoverflow.com/questions/3304464/mysql-diacritic-insensitive-search-spanish-accents – barsju Apr 05 '12 at 06:50
  • I tried http://stackoverflow.com/questions/3304464/mysql-diacritic-insensitive-search-spanish-accents but it doesn't work for me. I set the characters to utf8 –  Apr 05 '12 at 07:10
  • Having the same problem. Did you ever find out the cause? – MastaBaba Jun 02 '18 at 08:05

2 Answers2

1

Here are some results from my tests. You can compare to yours:

CREATE TABLE `test` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(32) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8

Table contents:

mysql> select * from test;
+----+---------+
| id | name    |
+----+---------+
|  1 | bla     | 
|  2 | blater  | 
|  3 | bláter | 
|  4 | bhei    | 
+----+---------+
4 rows in set (0.00 sec)

Search results;

mysql> select * from test where name like '%bla%';
+----+---------+
| id | name    |
+----+---------+
|  1 | bla     | 
|  2 | blater  | 
|  3 | bláter | 
+----+---------+
3 rows in set (0.00 sec)

Search with accent:

mysql> select * from test where name like '%blá%';;
+----+---------+
| id | name    |
+----+---------+
|  3 | bláter | 
+----+---------+
1 row in set (0.00 sec)

I get the same results even with COLLATE=utf8_hungarian_ci

barsju
  • 4,408
  • 1
  • 19
  • 24
0

For me, setting the character encoding to utf8_general_ci (or something else with "_ci") did the trick. The _ci means that SQL will match searches irrespective of case or accent.

MastaBaba
  • 1,085
  • 1
  • 12
  • 29