Oh no! Where's the JavaScript?
Your Web browser does not have JavaScript enabled or does not support JavaScript. Please enable JavaScript on your Web browser to properly view this Web site, or upgrade to a Web browser that does support JavaScript.
Sign In
Not a member yet? Click here to register.

How to change scrambled characters in the database after v7 to v9 upgrade ( Latin_xx to UTF-8 )

Falk - Latest reply on 08-09-2018 10:33 by janmol

0

Falk
Falk
Simply just increment values with your chars if they don´t exist in the following function.
This can require a lot of resources depending on your database size.
Please note that this will will work for databases that have upgraded the charsets to UTF-8 from other general latin types.

function upgrade_database() {
   dbquery("SET NAMES 'utf8'");
   $result = dbquery("SHOW TABLES");
   while ($row = dbarray($result)) {
      foreach ($row as $key => $table) {
         dbquery("ALTER TABLE ".$table." CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci");
         $result2 = dbquery("SHOW COLUMNS FROM ".$table);
         // We must change all data like find/replace in columns of broken chars, this may differ for each locales.
         // Please help to complete this list if you know what´s missing with your locale set
         while ($column = dbarray($result2)) {
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field']." ,'ß','ß')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field']." ,'ä','ä')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field']." ,'ü','ü')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field']." ,'ö','ö')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field']." ,'Ä','Ä')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field']." ,'Ãœ','Ü')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field']." ,'Ö','Ö')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field']." ,'€','€')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field']." ,'Ã¥','Å')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", 'ð', 'ğ')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", 'ý', 'ı')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", 'þ', 'ş')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", 'Ð', 'Ğ')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", 'Ý', 'İ')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", 'Þ', 'Ş')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", 'É','É')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '“','\"')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", 'â€','\"')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", 'Ç','Ç')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", 'Ã','Ã')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", 'Ã¥','Å')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", 'Ã ','À')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", 'ú','ú')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '•','-')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", 'Ø','Ø')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", 'õ','õ')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", 'í','í')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", 'â','â')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", 'ã','ã')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", 'ê','ê')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", 'á','á')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", 'é','é')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", 'ó','ó')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '–','–')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", 'ç','ç')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", 'ª','ª')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", 'º','º')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", 'Ã ','à')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", 'ç','ç')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", 'ã','ã')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", 'á','á')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", 'â','â')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", 'é','é')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", 'í','í')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", 'õ','õ')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", 'ú','ú')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", 'ç','ç')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", 'Á','Á')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", 'Â','Â')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", 'É','É')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", 'Í','Í')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", 'Õ','Õ')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", 'Ú','Ú')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", 'Ç','Ç')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", 'Ã','Ã')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", 'À','À')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", 'Ê','Ê')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", 'Ó','Ó')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", 'Ô','Ô')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", 'Ü','Ü')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", 'ã','ã')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", 'à','à')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", 'ê','ê')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", 'ó','ó')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", 'ô','ô')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", 'ü','ü')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '&','&')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '>','>')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '<','<')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", 'ˆ','ˆ')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '˜','˜')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '¨','¨')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '&cute;','´')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '¸','¸')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '"','\"')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '“','“')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '”','”')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '‘','‘')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '’','’')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '‹','‹')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '›','›')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '«','«')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '»','»')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", 'º','º')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", 'ª','ª')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '–','–')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '—','—')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '¯','¯')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '…','…')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '¦','¦')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '•','•')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '¶','¶')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '§','§')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '¹','¹')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '²','²')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '³','³')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '½','½')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '¼','¼')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '¾','¾')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '⅛','⅛')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '⅜','⅜')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '⅝','⅝')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '⅞','⅞')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '>','>')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '<','<')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '±','±')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '−','−')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '×','×')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '÷','÷')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '∗','∗')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '⁄','⁄')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '‰','‰')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '∫','∫')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '∑','∑')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '∏','∏')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '√','√')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '∞','∞')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '≈','≈')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '≅','≅')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '∝','∝')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '≡','≡')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '≠','≠')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '≤','≤')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '≥','≥')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '∴','∴')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '⋅','⋅')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '·','·')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '∂','∂')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", 'ℑ','ℑ')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", 'ℜ','ℜ')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '′','′')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '″','″')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '°','°')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '∠','∠')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '⊥','⊥')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '∇','∇')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '⊕','⊕')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '⊗','⊗')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", 'ℵ','ℵ')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", 'ø','ø')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", 'Ø','Ø')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '∈','∈')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '∉','∉')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '∩','∩')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '∪','∪')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '⊂','⊂')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '⊃','⊃')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '⊆','⊆')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '⊇','⊇')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '∃','∃')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '∀','∀')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '∅','∅')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '¬','¬')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '∧','∧')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '∨','∨')");
            dbquery("UPDATE ".$table." SET ".$column['Field']." = REPLACE(".$column['Field'].", '↵','↵')");
         }
      }
   }
}

// Force the database to UTF-8 because we'll convert to it
upgrade_database();

Edited by Falk on 23-04-2018 13:50, 6 months ago

Sort replies by

0

janmol
janmol
An idea ... if you are changing this anyway: Not all characters are relevant in all language versions. Maybe it's possible (to reduce the load on server) to cut this op into chunks ... if language is Danish for example, only a handfull of the characters are relevant (æ,ø,å and Æ,Ø,Å) - if tha main language is French it's all about é, é and so on ..
...........................
Jan Mølgård
PHP-Fusion, Denmark
Phone: 004528966794
Mail: janmol@wordit.dk
Mail: janm@janm.dk

Testsite version 9: http://php-fusion.dk/fusion_9_test/
Actions
You can view all discussion threads in this forum.
You can start a new discussion thread in this forum.
You cannot reply in this discussion thread.
You cannot start on a poll in this forum.
You cannot upload attachments in this forum.
You can download attachments in this forum.
More topics like this
Participated Users
Users who participated in discussion: :
janmol, Falk

Moderators:
{%user_avatar%}

How to change scrambled characters in the database after v7 to v9 upgrade ( Latin_xx to UTF-8 )
by {%user_profile_link%} {%post_date%}