Trouble with umlauts and accents, collations and the convert-command
I've got a database here and realised search requests delivering equal results for "y" and "ü", as well as "e" and "é" etc.
The website is in German, the entries in the database above all German, French and English. So a confusion with the chars mentioned above is not allowed to happen.
I think I spotted the problem: collation of the mysql-connection and the table: utf8_general_ci; collation of the fields in the table: latin1_swedish_ci.
For testing purposes I set up a database with everything set to latin1_german2_ci. On entering new data there is no more confusion, e.g. "Ethanol" and "éthanole".
Unfortunately simply inserting the data from the original database does not work. Several special characters are now shown as placeholders and the confusion persists.
I tried to use mysql's convert-command, but somehow I do not succeed in altering the encoding.
Am I right by blaming the trouble on the collations? If yes, how do I convert the data in a way that the confusions above do not happen anymore? If no, what else can I do about it?
Thanks in advance.