Posted By

sjasliek on 04/13/12


Tagged

fix mysql database utf-8


Versions (?)

UTF8 Database fix


 / Published in: PHP
 

Author: J. van Hemert Fixes encoding when utf-8-encoded data is stored in tables with other (e.g. latinswedishci) encoding. Will convert all columns in all tables to utf8generalci.

  1. <?php
  2. /******************************************/
  3. // db_utf8_fix.php //
  4. // Author: J. van Hemert //
  5. // //
  6. // Fixes encoding when utf-8-encoded data //
  7. // is stored in tables with other (e.g. //
  8. // latin_swedish_ci) encoding. //
  9. // Will convert all columns in all tables //
  10. // to utf8_general_ci. //
  11. // Run from fileadmin folder in TYPO3 //
  12. // installation. //
  13. /******************************************/
  14.  
  15. //Set to TRUE to generate an enormous amount of debug output with
  16. //analysis of table structure.
  17. define("DEBUG", FALSE);
  18. //Set to FALSE to really convert the database
  19. define("SIMULATE", FALSE);
  20.  
  21.  
  22. require_once ('../typo3conf/localconf.php');
  23.  
  24. echo str_repeat(' ', 256);
  25. ?>
  26. <html>
  27. <head>
  28. <style type="text/css">
  29. .normal {
  30. color: black;
  31. }
  32. .okay {
  33. color: green;
  34. }
  35. .label {
  36. color: blue;
  37. }
  38. .error {
  39. color: red;
  40. }
  41. </style>
  42. </head>
  43. <body>
  44. <?php
  45.  
  46. $tables = array();
  47. $typeconv = array(
  48. "char" => "binary",
  49. "text" => "blob",
  50. );
  51. $db = mysql_connect($typo_db_host, $typo_db_username, $typo_db_password, TRUE);
  52. if (!is_resource($db)) {
  53. die("Could not connect to db!: " . mysql_error());
  54. }
  55. if (mysql_select_db($typo_db, $db) === FALSE) {
  56. die("Could not select database!: " . mysql_error());
  57. }
  58.  
  59.  
  60. $sql = "SHOW TABLES;";
  61.  
  62. $db_res = mysql_query($sql, $db);
  63.  
  64. if (!is_resource($db_res)) {
  65. die ("Could not get query result!: " . mysql_error() . "\n" . $sql);
  66. }
  67.  
  68. while ($row = mysql_fetch_array($db_res, MYSQL_NUM)) {
  69. if (DEBUG) var_dump($row);
  70. $tables[] = $row[0];
  71. }
  72.  
  73. foreach ($tables as $table) {
  74. echo "<div><span class=\"label\">".$table.": </span><span class=\"normal\">";
  75. $sql = "SHOW FULL COLUMNS FROM `$table`;";
  76. $db_res = mysql_query($sql, $db);
  77. if (!is_resource($db_res)) {
  78. die ("Could not get table data!: " . mysql_error() . "\n" . $sql);
  79. }
  80. $columns = array();
  81. while ($row = mysql_fetch_assoc($db_res)) {
  82. if (DEBUG) {
  83. echo "column: ";
  84. var_dump($row);
  85. }
  86. $columns[] = $row;
  87. }
  88. foreach ($columns as $column) {
  89. $oldtype = $column['Type'];
  90. if (DEBUG) echo "Original: ".$column['Type']."\n";
  91. $column['Type'] = str_replace(array_keys($typeconv), array_values($typeconv), $column['Type']);
  92. if (DEBUG) echo "modified: ".$column['Type']."\n";
  93. if ($column['Type'] != $oldtype) {
  94. $column['Null'] = (strtolower($column['Null']) == "yes") ? "NULL" : "NOT NULL";
  95. $column['Default'] = (is_numeric($column['Default'])) ? $column['Default'] : ($column['Default'] === "NULL")? $column['Default'] : "'{$column['Default']}'";
  96. /*
  97. $sql = "ALTER TABLE `$table` MODIFY COLUMN `{$column['Field']}` {$column['Type']} {$column['Null']} DEFAULT {$column['Default']} {$column['Extra']};";
  98.   if (DEBUG) {
  99.   echo "$sql\n";
  100.   } else {
  101.   if (!SIMULATE) {
  102.   $db_res = mysql_query($sql, $db);
  103.   if (!is_resource($db_res) && mysql_errno($db) != 0) {
  104.   echo "Could not execute query!: " . mysql_error($db) . "\n" . $sql;
  105.   }
  106.   }
  107.   }
  108. */
  109. $column['Type'] = str_replace(array_values($typeconv), array_keys($typeconv), $column['Type']);
  110. $sql = "ALTER TABLE `$table` MODIFY COLUMN `{$column['Field']}` {$column['Type']} CHARACTER SET utf8 COLLATE utf8_general_ci {$column['Null']} DEFAULT {$column['Default']} {$column['Extra']};";
  111. if (DEBUG) {
  112. echo "$sql\n";
  113. } else {
  114. if (!SIMULATE) {
  115. $db_res = mysql_query($sql, $db);
  116. if (!is_resource($db_res) && mysql_errno($db) != 0) {
  117. echo "Could not execute query!: " . mysql_error($db) . "\n" . $sql;
  118. }
  119. }
  120. echo "."; ob_flush(); flush();
  121. }
  122. }
  123. }
  124.  
  125. $sql = "ALTER TABLE `$table` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;";
  126. if (DEBUG) {
  127. echo "$sql\n";
  128. } else {
  129. if (!SIMULATE) {
  130. $db_res = mysql_query($sql, $db);
  131. if (!is_resource($db_res) && mysql_errno($db) != 0) {
  132. echo "Could not execute query!: " . mysql_error($db) . "\n" . $sql;
  133. }
  134. }
  135. echo "</span><span class=\"okay\"> OK</span></div>"; ob_flush(); flush();
  136. }
  137.  
  138. }
  139. echo "<div><span class=\"label\">DATABASE: </span><span class=\"normal\">";
  140. $sql = "ALTER DATABASE `$typo_db` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;";
  141. if (DEBUG) {
  142. echo "$sql\n";
  143. } else {
  144. if (!SIMULATE) {
  145. $db_res = mysql_query($sql, $db);
  146. if (!is_resource($db_res) && mysql_errno($db) != 0) {
  147. echo "Could not execute query!: " . mysql_error($db) . "\n" . $sql;
  148. }
  149. }
  150. echo "</span><span class=\"okay\"> OK</span></div>"; ob_flush(); flush();
  151. }
  152.  
  153. echo "<div>finished converting tables</div>";
  154. ?>
  155. </body>
  156. </html>

Report this snippet  

You need to login to post a comment.