Using MySQL to query Spanish letters ~~~

Use SELECT command to fetch data contains Spanish letters

[hapsburg]

Hi, there. I have a web information system designed by PHP and MySQL. But there's a problem on using SELECT command to query the data contains Spanish letters. Because there're many Spanish letters such as á, é, í;, ó, ú, ü, ñ. But most users of this system don't have the Spanish input method installed. It's going to be difficult for them to enter those special letters. So, I'm trying to figure out a way to let the MySQL treat those special letters as its corresponding ones when the data query is performed. For example, if the user entered "Jose" for search, MySQL will fetch all data records with values "Jose" or "José".

The method I can think out for now is to use the regular expresion like this :

SELECT nombre,apellidos FROM cliente WHERE nombre REGEXP 'Jos[eé]';

It works fine for the case of José. But not for all the cases, if the user entered "Maria" or "Agustin" for searching. Although my PHP program can put query command as "Mar[ií]a" or "Agust[ií]n" respectively without any problem. But the MySQL will only fetch the data records with "Maria" or "Agustin". Those data records with the values "María" or "Agustín" can not be fetched by from MySQL by using this command. It looks like only á; and é could work fine in this solution. I'm not sure is this a bug or not. Does anyone know what the problem is? Or anyone has other better solution for this problem? Please help me!

P.S. My PHP program don't have any problem on saving or displaying those Spanish letters in MySQL.

[coopster]

Welcome to WebmasterWorld, hapsburg.

If you are storing the characters in your database table in utf8 you should be able to try COLLATE [dev.mysql.com] utf8_unicode_ci in your query statement.