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 )

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.

CodeDownload  
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();


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 ..
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!) :

CodeDownload  
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");

Wanabo. - Thanks for the answer. I was helped by the method described in the post from your link. Now everything is fine.
So far though I can't find a single step-by-step guide in how to do this. Please help.
You can run it from any file, Close the site from public. Copy and paste the code after maincore.php includes in news.php for example and access the file from your browser, wait for it to complete.
It can take a very long time and it is not 100% that your server can deal with it.
Make backups before.
Thread Information
Author
Replies
7 posts
Views
3,448 times
Last Post
Last updated on 2 days ago
You can view all discussion threads in this forum.
You cannot set up a bounty in this discussion thread.
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.
You cannot up or down-vote on the post in this discussion thread.
Users who participated in discussion: Falk, janmol, Wanabo, zizub, becaliancu