Mysql Diacritic/Accent Insensitive search and storage

Question

I am having frustrating times with performing Diacritic/accent insensitive searches of my database.

I have a table with alternative language terms stored in it. I have ensured that it has been set to use utf8 and collated in utf8_general_ci (I have also tried _bin and _unicode_ci).

The trouble is that:

• Search for Hahnchen when Hähnchen is stored it will yield a result
• Search for Hähnchen when Hähnchen is stored will not yield a result

If I work in phpmyadmin I can save values as above:

Hähnchen

But, working from my scripts this gets converted to:

Hühnchen

• Search for Hahnchen when Hühnchen is stored it will not yield a result
• Search for Hähnchen when Hühnchen is stored will yield a result

Is there anything I am missing? I've read dozens of similar posts with no definitive answer (everything I have tried has failed).

Answer 1

Useful post

After reading the above linked post, in particular the answer from 'deceze' it appears that he is correct in stating that:

    $db->setAttribute(PDO::MYSQL_ATTR_INIT_COMMAND, 'SET NAMES utf8');

Is a nonsense (in some instances). I ended up adding the option explicitly in the connection parameters.

Solution eg:

    mysql:host=localhost;charset=utf8;dbname=.....

This resolves the search issue and the garbled data saving issue.

:)