Getting accented letters into mysql

Question

I've set up the collation of my table to utf8_unicode_ci, so that when people do searches, for example, on MONÃE, they also get a hit on MONAE and MONÁE. Works fine.

There's a problem with my script that enters the data into the tables in the first place.

How the script works basically...

It creates the records in mysql by iterating through a php object array, and dumping the data into a unique column in mysql. It then needs to get the id (and the data) of the record just created (or the id of a matching record if one was already there) from mysql, find that data in the php object array, and stick the mysql id into the matched object in the array for further parsing in the script.

Example successful entry...

For simplicity, let's say the php array has just one record:

DAVID

David gets entered in mysql (wasn't there before), and we find the mysql id created is 7.

We search through the php array for DAVID, and insert 7 as it's ID key. Then go on to successfully finish whatever parsing needs to be done.

Example problem entry...

Again, dealing with one record...

MONÁE

(note, that MONÃE, with a different accent, is ALREADY in mysql)...

Problem 1: MONÁE doesn't get entered in the unique column, I think because the collation sees MONÁE and MONÃE as equal.

Problem 2 When my php then searches mysql for the id for MONÁE, the id for MONÃE and the data MONÃE is instead returned, again due to collation I think.

Problem 3 php now searches for MONÃE in the php array, but can't find it (since MONÁE is in there)

The mysql id doesn't get inserted into the array, and the rest of my parsing fails.

I have spent three days trying to solve 1 and 2. Any ideas of how to get around the problem?

Cheers!!

Answer 1

You have to modify your table structure :

alter table tablename alter column colname collate utf8_bin. 

This will resolve your problems, but be aware of the implication this will have on sorting before you apply it.