Created
January 17, 2013 09:52
-
-
Save pepebe/4554926 to your computer and use it in GitHub Desktop.
SQL: Repair German "Umlaute" inside a MySQL Database.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <?php | |
| /** | |
| * Alle kaputten Umlaute reparieren bei Umstellung von ISO->UTF8 | |
| * Source: http://xhtmlforum.de/66480-kleines-skript-alle-umlaute-der-datenbank.html | |
| * | |
| * @project - | |
| * @author Boris Bojic <bojic@devshack.biz> | |
| * @copyright Copyright (c) 2011, Boris Bojic (DevShack) | |
| * @version Fri, 23 Dec 2011 13:47:11 +0100 | |
| * @updated - | |
| * | |
| */ | |
| // === [ Content / Charset ] ============================================== | |
| header('Content-Type: text/html; charset=utf-8'); | |
| // PHP auch explizit auf UTF-8 setzen | |
| mb_internal_encoding('UTF-8'); | |
| $db = array(); | |
| $db['host'] = "localhost"; | |
| $db['uname'] = "mysql_user"; | |
| $db['password'] = "mysql_pass"; | |
| $db['database'] = "datenbankname"; | |
| $dbconnect = mysql_connect($db['host'], $db['uname'], $db['password']) or die ("Konnte keine Verbindung zur Datenbank aufnehmen!"); | |
| mysql_select_db($db['database'],$dbconnect) or die ("Fehler beim Auswählen der Datenbank!"); | |
| mysql_set_charset('utf8'); | |
| echo '<pre>'; | |
| function getTables($db){ | |
| $result = mysql_query("SHOW TABLES FROM " . $db['database']); | |
| while($row = mysql_fetch_row($result)){ | |
| $res[] = $row[0]; | |
| } | |
| return $res; | |
| } | |
| function getColumns($table){ | |
| $table = mysql_real_escape_string($table); | |
| $mysqlres = mysql_query("SHOW COLUMNS FROM " . $table); | |
| while($row = mysql_fetch_row($mysqlres)){ | |
| $res[] = $row[0]; | |
| } | |
| return $res; | |
| } | |
| // Alle Tabellen ermitteln | |
| $tablesArray = getTables($db); | |
| // Alle Spalten pro Tabelle ermitteln und durcharbeiten | |
| foreach($tablesArray AS $table){ | |
| $affectedRows = 0; | |
| $spalten = getColumns($table); | |
| echo "Tabelle: " . $table . "<br />"; | |
| foreach($spalten AS $spalte){ | |
| echo "...Spalte: " . $spalte . "<br />"; | |
| $query = ' | |
| UPDATE `' . $table . '` SET | |
| `' . $spalte . '` = REPLACE(`' . $spalte . '`,"ß", "ß"), | |
| `' . $spalte . '` = REPLACE(`' . $spalte . '`, "ä", "ä"), | |
| `' . $spalte . '` = REPLACE(`' . $spalte . '`, "ü", "ü"), | |
| `' . $spalte . '` = REPLACE(`' . $spalte . '`, "ö", "ö"), | |
| `' . $spalte . '` = REPLACE(`' . $spalte . '`, "Ä", "Ä"), | |
| `' . $spalte . '` = REPLACE(`' . $spalte . '`, "Ãœ", "Ü"), | |
| `' . $spalte . '` = REPLACE(`' . $spalte . '`, "Ö", "Ö"), | |
| `' . $spalte . '` = REPLACE(`' . $spalte . '`, "€", "€") | |
| '; | |
| mysql_query($query) OR die(mysql_error() . $query); | |
| $affectedRows += mysql_affected_rows(); | |
| } | |
| echo "Tabelle " . $table . " aktualisiert, Datensätze: " . $affectedRows . "<br /><br />"; | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Didn't work for PHP7 so i've done some changes. What licenes is this? Can we use that for friendica? https://github.com/friendica/friendica
`<?php
/**
*/
// === [ Content / Charset ] ==============================================
header('Content-Type: text/html; charset=utf-8');
// PHP auch explizit auf UTF-8 setzen
ini_set('display_errors', 'On');
mb_internal_encoding('UTF-8');
$db = array();
$db['host'] = 'localhost';
$db['uname'] = 'NAME';
$db['password'] = 'PASSWORT';
$db['database'] = 'DATENBANKNAME';
echo 'huhu';
$dbconnect = mysqli_connect($db['host'], $db['uname'], $db['password']) or die ("Konnte keine Verbindung zur Datenbank aufnehmen!");
mysqli_select_db($dbconnect,$db['database']) or die ("Fehler beim Auswählen der Datenbank!");
mysqli_set_charset($dbconnect,'utf8');
echo '