Instantly share code, notes, and snippets.

Embed
What would you like to do?
SQL: Repair German "Umlaute" inside a MySQL Database.
<?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 />";
}
@pepebe

This comment has been minimized.

Show comment
Hide comment
@pepebe

pepebe Jan 17, 2013

Works great. As usual don't forget to backup your database before running this script.

Owner

pepebe commented Jan 17, 2013

Works great. As usual don't forget to backup your database before running this script.

@Webmeteor

This comment has been minimized.

Show comment
Hide comment
@Webmeteor

Webmeteor Nov 17, 2015

Für kleine Datenbanken ist das Script super.
Hat man eine Menge Daten, dann wird der Server in die Knie gezwungen. Dann kann man doch meist nur Spalte für Spalte einzeln ändern.

Webmeteor commented Nov 17, 2015

Für kleine Datenbanken ist das Script super.
Hat man eine Menge Daten, dann wird der Server in die Knie gezwungen. Dann kann man doch meist nur Spalte für Spalte einzeln ändern.

@ApeMonkeys

This comment has been minimized.

Show comment
Hide comment
@ApeMonkeys

ApeMonkeys commented Nov 27, 2015

Thx

@Wucasch

This comment has been minimized.

Show comment
Hide comment
@Wucasch

Wucasch Mar 9, 2016

Dude you are awesome, really, thank you very much!

Wucasch commented Mar 9, 2016

Dude you are awesome, really, thank you very much!

@astoeffer

This comment has been minimized.

Show comment
Hide comment
@astoeffer

astoeffer Sep 12, 2016

Hi,

thanks for that script. I test it and it works great.
But I need a correction only for one table and two columns.

But I'm not a developer. Ist ist possible to customise your script for that purpose?

Thx

astoeffer commented Sep 12, 2016

Hi,

thanks for that script. I test it and it works great.
But I need a correction only for one table and two columns.

But I'm not a developer. Ist ist possible to customise your script for that purpose?

Thx

@IL-Michi

This comment has been minimized.

Show comment
Hide comment
@IL-Michi

IL-Michi Oct 14, 2016

perfekt thx

IL-Michi commented Oct 14, 2016

perfekt thx

@hoergen

This comment has been minimized.

Show comment
Hide comment
@hoergen

hoergen Jul 3, 2017

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 '

';

function getTables($db){
global $dbconnect;
$result = mysqli_query($dbconnect,"SHOW TABLES FROM " . $db['database']);

while($row = mysqli_fetch_row($result)){ 
    $res[] = $row[0]; 
} 

return $res; 

}

function getColumns($table){
global $dbconnect;
$table = mysqli_real_escape_string($dbconnect,$table);

$mysqlres = mysqli_query($dbconnect,"SHOW COLUMNS FROM " . $table); 
while($row = mysqli_fetch_row($mysqlres)){ 
    $res[] = $row[0]; 
} 

return $res; 

}

// Alle Tabellen ermitteln
$tablesArray = getTables($db);

echo 'nach getTables';

// 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 . '`, "€", "€") 
    '; 
 
    mysqli_query($dbconnect,$query) OR die(mysqli_error($dbconnect) . $query); 
    $affectedRows += mysqli_affected_rows(); 
 
} 

 
echo "Tabelle " . $table . " aktualisiert, Datensätze: " . $affectedRows . "<br /><br />"; 

}
`

hoergen commented Jul 3, 2017

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 '

';

function getTables($db){
global $dbconnect;
$result = mysqli_query($dbconnect,"SHOW TABLES FROM " . $db['database']);

while($row = mysqli_fetch_row($result)){ 
    $res[] = $row[0]; 
} 

return $res; 

}

function getColumns($table){
global $dbconnect;
$table = mysqli_real_escape_string($dbconnect,$table);

$mysqlres = mysqli_query($dbconnect,"SHOW COLUMNS FROM " . $table); 
while($row = mysqli_fetch_row($mysqlres)){ 
    $res[] = $row[0]; 
} 

return $res; 

}

// Alle Tabellen ermitteln
$tablesArray = getTables($db);

echo 'nach getTables';

// 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 . '`, "€", "€") 
    '; 
 
    mysqli_query($dbconnect,$query) OR die(mysqli_error($dbconnect) . $query); 
    $affectedRows += mysqli_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