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 )


Print Track
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();

4 replies

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

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.
author Falk
forumUpgrading issues - 9
replies5 posts
viewed2961 times
activeLast updated on 5 months ago

Related

Change "articles" to "games"?
Started by
I
icarus1337 - 6 posts
Upgrade from MySQL 4.1 to MySQL 5.0
Started by
L
Lory - 8 posts
Upgrade from php-fusion 5 to php-fusion 7
Started by
A
aimrock - 2 posts
How to change news.php file to index.php or home.php file
Started by
P
prashant - 2 posts
How to change news categories to articles categories in news?
Started by
D
dawido171 - 10 posts
Change orders in title sitename - pagetitle to pagetitle - sitename
Started by
R
Ramage - 2 posts

Hot Questions

foc_in_gaura
Upgrading error
foc_in_gaura - 52 posts
heavencore
Advice on upgrade path
heavencore - 24 posts
S
V6.01.13
sadamyne - 30 posts
O
Error with upgrade database
omikron20 - 15 posts
Falk
C
Upgrade V7 > V9
Costa1337 - 7 posts
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: Falk, janmol, Wanabo, zizub