Hi,

I try to filter in views by the title of the node. The accented characters in the title cannot be differentiated. For example, I would like to filter the node that its title = qué. The node that its title is que or qué would be showed in the view.

I checked the view preview. The where clause is qué, so I think it is database coding problem. then I changed the code collation of database (every tables in mySQL) to "utf8-unicode-ci" (orginally it is utf8-general-ci). I got the same result.

Anyone knows how to make accented character different in view filter (or db_query())? Thanks.

hosais

Comments

hosais’s picture

hosais commented
Title: Node Title: Accented characters in database cannot be differentiated? (using utf8-unicode-ci) » Accented characters in database cannot be differentiated? How to set mySQL (SET NAMES 'UTF8';)?

Hi,

I realized that set utf8-unicode-ci (collation) is not enough. You need to set connection character set too.
This needs to be set as first statement after the client connected to the MySQL server. The commad is SET NAMES 'UTF8'; which was tested effective in phpMyadmin.

Where to put this commad in Drupal?

hosais’s picture

hosais commented

Hi,

I found the code in database.mysql.inc line 86. 

 if (!empty($GLOBALS['db_collation'])) {
    mysql_query('SET NAMES utf8 COLLATE '. $GLOBALS['db_collation'], $connection);	
  }
  else {
    mysql_query('SET NAMES utf8', $connection);	
  }

I know it run mysql_query('SET NAMES utf8', $connection);. This makes me confused. Since with this SET NAMES command and the SQL query I copied from the view (preview query), I can search correctly with accented letter.

My database was changed from utf8-gernal-ci => utf8-unicode-ci, all the tables included.
MySQL server:
Server: Localhost via UNIX socket
Server version: 5.1.58-community-log
MySQL charset: UTF-8 Unicode (utf8)
MySQL client version: 5.1.58

Now I am not sure what the problem is. Anyone has ideas? please help.

hosais

mdupont’s picture

mdupont
Primary language French
Location Switzerland
commented
Status: Active » Closed (works as designed)

The problem is MySQL configuration, not Drupal. MySQL needs to be set to use UTF-8 charset and collation by default.

hosais’s picture

hosais commented

Hi mdupont,

Thank you very much for reply.

Do you mean the mySQL server configuration? I use phpMyAdmin. In the settings in phpMyAdmin, the database default was utf8-unicode-ci and all the tables were also utf8-unicode-ci. Could you tell me more about the MySQL configuration that you mentioned?

Thanks in advanced.

hosais,