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.
Falk

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

by Falk, Last updated on 12 days ago in Upgrading issues - 9
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();

Need help?, Having trouble?
• View our Documentation for Guides, Standards and Functions
• Name and Organize your content correctly in the corresponding Forums for best support results
• Attaching Log Files and Screenshots when reporting issues will help
• Please read and comply with the Code of Conduct

(¯`·._.·(¯`°·._.·°º*[ Project Manager ]*º°·._.·°´¯)·._.·´¯)
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/
How to use it? I have to create my upgrade.php file and paste this code into it. Then upload this file to the server and replace the old file. Then run the update? So act?
Does this work for v8 as well?
See: https://www.php-fusion.co.uk/infusions/forum/viewthread.php?thread_id=39557&pid=206096#post_206047
Or should it be for v8 like this? (not tested!) :

function upgrade_database() {
 dbquery("SET NAMES 'utf8mb4'");
 $result = dbquery("SHOW TABLES");
 while ($row = dbarray($result)) {
 foreach ($row as $key => $table) {
 dbquery("ALTER TABLE ".$table." CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
www.probemyip.com/probe-my-ip-80x15.png
pHp-Fusion.Asia & pHp-Fusion.Fr & pHp-Fusion.Cn are available for a localized support community. Send PB for info.
Wanabo. - Thanks for the answer. I was helped by the method described in the post from your link. Now everything is fine.
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.
Users who participated in discussion: janmol, Falk, Wanabo, zizub