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.
Forgot Password?
Navigation
  1. Home
  2. Discussion Forum
  3. PHPFusion 9 Support
  4. Upgrading issues - 9
  5. How to change scrambled characters in the database after v7 to v9 upgrade ( Latin_xx to UTF-8 )

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

Last updated on 5 years ago
Track threadPrint
F
FalkSuper Admin
Posted 8 years ago
#1

0

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.

Code Download source
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 N/A on 23-04-2018 14:50, 7 years ago
J
janmolVeteran Member
Posted 8 years ago
#2

0

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/
Z
zizubMember
Posted 6 years ago
#3

0

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?
W
WanaboSenior Member
Posted 6 years ago
#4

0

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

Code Download source
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");
Edited by Wanabo on 11-02-2019 11:59, 6 years ago
Z
zizubMember
Posted 6 years ago
#5

0

Wanabo. - Thanks for the answer. I was helped by the method described in the post from your link. Now everything is fine.
Edited by zizub on 11-02-2019 13:48, 6 years ago
B
becaliancuNewbie
Posted 5 years ago
#6

0

So far though I can't find a single step-by-step guide in how to do this. Please help.
F
FalkSuper Admin
Posted 5 years ago
#7

0

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.
You can view all discussion threads in this forum.
You cannot 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.
You cannot set up a bounty in this discussion thread.
Moderator: Support Team
Users who participated in discussion: Falk, janmol, Wanabo, zizub, becaliancu