SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Mar 2010
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Trouble with umlauts and accents, collations and the convert-command

    Hello there,

    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.

    Sincerely yours.

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Character set and collation are two different things. If the character set is OK, then you do not want to try to convert anything. You only want to change the collation (which defines sort order).

    http://dev.mysql.com/doc/refman/5.0/...et-column.html

  3. #3
    SitePoint Member
    Join Date
    Mar 2010
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you so far, but unfortunately this only solves half of my problem.

    I forgot to mention that I already tried to set the table and its fields to charset latin1 and collation latin1_german2_ci.
    This way, there is no more confusion between "y" and "" but "" (and every other accent) and "e" are still the same for a SELECT-query.

    I don't know how to fix this.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •