Skip to content

Instantly share code, notes, and snippets.

@CodeBrauer

CodeBrauer/fix_umlaute.php

Forked from pepebe/gist:4554926
Last active January 31, 2024 07:48
Show Gist options
  • Save CodeBrauer/99f8b268892d54a55e27dc21d1bb3f63 to your computer and use it in GitHub Desktop.
Save CodeBrauer/99f8b268892d54a55e27dc21d1bb3f63 to your computer and use it in GitHub Desktop.
Download ZIP
SQL: Repair German "Umlaute" inside a MySQL Database. (mysqli version / PHP7 supported)
Raw
fix_umlaute.php
<?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 <[email protected]>
* @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'] = "";
$db['password'] = "";
$db['database'] = "";
$dbconnect = ($GLOBALS["___mysqli_ston"] = 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!");
((bool)mysqli_set_charset($GLOBALS["___mysqli_ston"], "utf8"));
echo '<pre>';
function getTables($db){
$result = mysqli_query($GLOBALS["___mysqli_ston"], "SHOW TABLES FROM " . $db['database']);
while($row = mysqli_fetch_row($result)){
$res[] = $row[0];
}
return $res;
}
function getColumns($table){
$table = mysqli_real_escape_string($GLOBALS["___mysqli_ston"], $table);
$mysqlres = mysqli_query($GLOBALS["___mysqli_ston"], "SHOW COLUMNS FROM " . $table);
while($row = mysqli_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 . '`,"â", "â"),
`' . $spalte . '` = REPLACE(`' . $spalte . '`,"ã", "ã"),
`' . $spalte . '` = REPLACE(`' . $spalte . '`,"ù", "ù"),
`' . $spalte . '` = REPLACE(`' . $spalte . '`,"ú", "ú"),
`' . $spalte . '` = REPLACE(`' . $spalte . '`,"û", "û"),
`' . $spalte . '` = REPLACE(`' . $spalte . '`,"Ù", "Ù"),
`' . $spalte . '` = REPLACE(`' . $spalte . '`,"Ú", "Ú"),
`' . $spalte . '` = REPLACE(`' . $spalte . '`,"Û", "Û"),
`' . $spalte . '` = REPLACE(`' . $spalte . '`,"Ãœ", "Ü"),
`' . $spalte . '` = REPLACE(`' . $spalte . '`,"ò", "ò"),
`' . $spalte . '` = REPLACE(`' . $spalte . '`,"ó", "ó"),
`' . $spalte . '` = REPLACE(`' . $spalte . '`,"ô", "ô"),
`' . $spalte . '` = REPLACE(`' . $spalte . '`,"è", "è"),
`' . $spalte . '` = REPLACE(`' . $spalte . '`,"é", "é"),
`' . $spalte . '` = REPLACE(`' . $spalte . '`,"ê", "ê"),
`' . $spalte . '` = REPLACE(`' . $spalte . '`,"ë", "ë"),
`' . $spalte . '` = REPLACE(`' . $spalte . '`,"À", "À"),
`' . $spalte . '` = REPLACE(`' . $spalte . '`,"Â", "Â"),
`' . $spalte . '` = REPLACE(`' . $spalte . '`,"Â","Ã"),
`' . $spalte . '` = REPLACE(`' . $spalte . '`,"Ä", "Ä"),
`' . $spalte . '` = REPLACE(`' . $spalte . '`,"Ã…", "Å"),
`' . $spalte . '` = REPLACE(`' . $spalte . '`,"Ç", "Ç"),
`' . $spalte . '` = REPLACE(`' . $spalte . '`,"È", "È"),
`' . $spalte . '` = REPLACE(`' . $spalte . '`,"É", "É"),
`' . $spalte . '` = REPLACE(`' . $spalte . '`,"Ê", "Ê"),
`' . $spalte . '` = REPLACE(`' . $spalte . '`,"Ë", "Ë"),
`' . $spalte . '` = REPLACE(`' . $spalte . '`,"ÃŒ", "Ì"),
`' . $spalte . '` = REPLACE(`' . $spalte . '`,"ÃŽ", "Î"),
`' . $spalte . '` = REPLACE(`' . $spalte . '`,"Ñ", "Ñ"),
`' . $spalte . '` = REPLACE(`' . $spalte . '`,"Ã’", "Ò"),
`' . $spalte . '` = REPLACE(`' . $spalte . '`,"Ó", "Ó"),
`' . $spalte . '` = REPLACE(`' . $spalte . '`,"Ô", "Ô"),
`' . $spalte . '` = REPLACE(`' . $spalte . '`,"Õ", "Õ"),
`' . $spalte . '` = REPLACE(`' . $spalte . '`,"Ø", "Ø"),
`' . $spalte . '` = REPLACE(`' . $spalte . '`,"Ã¥", "å"),
`' . $spalte . '` = REPLACE(`' . $spalte . '`,"æ", "æ"),
`' . $spalte . '` = REPLACE(`' . $spalte . '`,"ç", "ç"),
`' . $spalte . '` = REPLACE(`' . $spalte . '`,"ì", "ì"),
`' . $spalte . '` = REPLACE(`' . $spalte . '`,"í" , "í"),
`' . $spalte . '` = REPLACE(`' . $spalte . '`,"î", "î"),
`' . $spalte . '` = REPLACE(`' . $spalte . '`,"ï", "ï"),
`' . $spalte . '` = REPLACE(`' . $spalte . '`,"ð", "ð"),
`' . $spalte . '` = REPLACE(`' . $spalte . '`,"ñ", "ñ"),
`' . $spalte . '` = REPLACE(`' . $spalte . '`,"õ", "õ"),
`' . $spalte . '` = REPLACE(`' . $spalte . '`,"ø", "ø"),
`' . $spalte . '` = REPLACE(`' . $spalte . '`,"ý", "ý"),
`' . $spalte . '` = REPLACE(`' . $spalte . '`,"ÿ", "ÿ"),
`' . $spalte . '` = REPLACE(`' . $spalte . '`,"€","€")
';
mysqli_query($GLOBALS["___mysqli_ston"], $query) OR die(mysqli_error($GLOBALS["___mysqli_ston"]) . $query);
$affectedRows += mysqli_affected_rows($GLOBALS["___mysqli_ston"]);
}
echo "Tabelle " . $table . " aktualisiert, Datensätze: " . $affectedRows . "<br /><br />";
}
@Vulcanraven91
Copy link

Added more special characters:

        `' . $spalte . '` = REPLACE(`' . $spalte . '`,"ü", "ü"),
        `' . $spalte . '` = REPLACE(`' . $spalte . '`,"ä", "ä"), 
        `' . $spalte . '` = REPLACE(`' . $spalte . '`,"ö", "ö"), 
        `' . $spalte . '` = REPLACE(`' . $spalte . '`,"Ö", "Ö"),
        `' . $spalte . '` = REPLACE(`' . $spalte . '`,"ß", "ß"),
        `' . $spalte . '` = REPLACE(`' . $spalte . '`,"Ã ", "à"),
        `' . $spalte . '` = REPLACE(`' . $spalte . '`,"á", "á"),
        `' . $spalte . '` = REPLACE(`' . $spalte . '`,"â", "â"),
        `' . $spalte . '` = REPLACE(`' . $spalte . '`,"ã", "ã"),
        `' . $spalte . '` = REPLACE(`' . $spalte . '`,"ù", "ù"),
        `' . $spalte . '` = REPLACE(`' . $spalte . '`,"ú", "ú"),
        `' . $spalte . '` = REPLACE(`' . $spalte . '`,"û", "û"),
        `' . $spalte . '` = REPLACE(`' . $spalte . '`,"Ù", "Ù"),
        `' . $spalte . '` = REPLACE(`' . $spalte . '`,"Ú", "Ú"),
        `' . $spalte . '` = REPLACE(`' . $spalte . '`,"Û", "Û"),
        `' . $spalte . '` = REPLACE(`' . $spalte . '`,"Ãœ", "Ü"),
        `' . $spalte . '` = REPLACE(`' . $spalte . '`,"ò", "ò"),
        `' . $spalte . '` = REPLACE(`' . $spalte . '`,"ó", "ó"),
        `' . $spalte . '` = REPLACE(`' . $spalte . '`,"ô", "ô"),
        `' . $spalte . '` = REPLACE(`' . $spalte . '`,"è", "è"),
        `' . $spalte . '` = REPLACE(`' . $spalte . '`,"é", "é"),
        `' . $spalte . '` = REPLACE(`' . $spalte . '`,"ê", "ê"),
        `' . $spalte . '` = REPLACE(`' . $spalte . '`,"ë", "ë"),
        `' . $spalte . '` = REPLACE(`' . $spalte . '`,"À", "À"),
        `' . $spalte . '` = REPLACE(`' . $spalte . '`,"Â", "Â"),
        `' . $spalte . '` = REPLACE(`' . $spalte . '`,"Â","Ã"),
        `' . $spalte . '` = REPLACE(`' . $spalte . '`,"Ä", "Ä"),
        `' . $spalte . '` = REPLACE(`' . $spalte . '`,"Ã…", "Å"),
        `' . $spalte . '` = REPLACE(`' . $spalte . '`,"Ç", "Ç"),
        `' . $spalte . '` = REPLACE(`' . $spalte . '`,"È", "È"),
        `' . $spalte . '` = REPLACE(`' . $spalte . '`,"É", "É"),
        `' . $spalte . '` = REPLACE(`' . $spalte . '`,"Ê", "Ê"),
        `' . $spalte . '` = REPLACE(`' . $spalte . '`,"Ë", "Ë"),
        `' . $spalte . '` = REPLACE(`' . $spalte . '`,"ÃŒ", "Ì"),
        `' . $spalte . '` = REPLACE(`' . $spalte . '`,"ÃŽ", "Î"),
        `' . $spalte . '` = REPLACE(`' . $spalte . '`,"Ñ", "Ñ"),
        `' . $spalte . '` = REPLACE(`' . $spalte . '`,"Ã’", "Ò"),
        `' . $spalte . '` = REPLACE(`' . $spalte . '`,"Ó", "Ó"),
        `' . $spalte . '` = REPLACE(`' . $spalte . '`,"Ô", "Ô"),
        `' . $spalte . '` = REPLACE(`' . $spalte . '`,"Õ", "Õ"),
        `' . $spalte . '` = REPLACE(`' . $spalte . '`,"Ø", "Ø"),
        `' . $spalte . '` = REPLACE(`' . $spalte . '`,"Ã¥", "å"),
        `' . $spalte . '` = REPLACE(`' . $spalte . '`,"æ", "æ"),
        `' . $spalte . '` = REPLACE(`' . $spalte . '`,"ç", "ç"),
        `' . $spalte . '` = REPLACE(`' . $spalte . '`,"ì", "ì"),
        `' . $spalte . '` = REPLACE(`' . $spalte . '`,"í" , "í"),
        `' . $spalte . '` = REPLACE(`' . $spalte . '`,"î", "î"),
        `' . $spalte . '` = REPLACE(`' . $spalte . '`,"ï", "ï"),
        `' . $spalte . '` = REPLACE(`' . $spalte . '`,"ð", "ð"),
        `' . $spalte . '` = REPLACE(`' . $spalte . '`,"ñ", "ñ"),
        `' . $spalte . '` = REPLACE(`' . $spalte . '`,"õ", "õ"),
        `' . $spalte . '` = REPLACE(`' . $spalte . '`,"ø", "ø"),
        `' . $spalte . '` = REPLACE(`' . $spalte . '`,"ý", "ý"),
        `' . $spalte . '` = REPLACE(`' . $spalte . '`,"ÿ", "ÿ"),
        `' . $spalte . '` = REPLACE(`' . $spalte . '`,"€","€")

@CodeBrauer
Copy link
Author

@Vulcanraven91 thank you! I updated the script.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment