Ever needed to change collation of table?
When you are upgrading Contao 3.5 installation to Contao 4.4, you may be left with old table collation "utf8_general_ci" instead of new collation "utf8_unicode_ci" used by Contao 4.4. With the following script you can change it in one go.
Code:
<?php
// Database credentials
$strHost = 'host';
$strUser = 'user';
$strPassword = 'password';
$strDatabase = 'database';
// Main Script
$con = mysqli_connect( $strHost, $strUser, $strPassword, $strDatabase);
if (mysqli_connect_errno()) {
echo "Error: Unable to connect to MySQL." . PHP_EOL;
echo "Debugging error : " . mysqli_connect_errno() . PHP_EOL;
exit;
}
$result = $con->query('show tables');
while($tables = mysqli_fetch_array($result)) {
foreach ($tables as $key => $value) {
$con->query("ALTER TABLE $value CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci");
}
}
echo "The collation of your database has been successfully changed!";
Contao 4 with auto parameters
Contao 4 with auto parameters:
PHP Code:
<?php
$parameters = file('../app/config/parameters.yml');
$params = array();
foreach($parameters as $value)
{
if(strpos($value, ': ') === false)
{
continue;
}
$param = explode(': ', trim($value));
$params[$param[0]] = $param[1];
}
// Database credentials
$strHost = $params['database_host'].':'.$params['database_port'];
$strUser = $params['database_user'];
$strPassword = $params['database_password'];
$strDatabase = $params['database_name'];
// Main Script
$con = mysqli_connect( $strHost, $strUser, $strPassword, $strDatabase);
if (mysqli_connect_errno()) {
echo "Error: Unable to connect to MySQL." . PHP_EOL;
echo "Debugging error : " . mysqli_connect_errno() . PHP_EOL;
exit;
}
$result = $con->query('show tables');
while($tables = mysqli_fetch_array($result)) {
#$con->query("ALTER TABLE $tables[0] CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
$con->query("ALTER TABLE $tables[0] CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci");
}
echo "The collation of your database has been successfully changed!";
Set the default collation of your database to utf8[mb4]_unicode_c manually.
If you have problems with utf8mb4_unicode_ci use utf8_unicode_ci instead.
Contao 3 with auto parameters
Contao 3 with auto parameters
PHP Code:
<?php
include 'system/config/localconfig.php';
// Database credentials
$strHost = $GLOBALS['TL_CONFIG']['dbHost'].':'.$GLOBALS['TL_CONFIG']['dbPort'];
$strUser = $GLOBALS['TL_CONFIG']['dbUser'];
$strPassword = $GLOBALS['TL_CONFIG']['dbPass'];
$strDatabase = $GLOBALS['TL_CONFIG']['dbDatabase'];
// Main Script
$con = mysqli_connect( $strHost, $strUser, $strPassword, $strDatabase);
if (mysqli_connect_errno()) {
echo "Error: Unable to connect to MySQL." . PHP_EOL;
echo "Debugging error : " . mysqli_connect_errno() . PHP_EOL;
exit;
}
$result = $con->query('show tables');
while($tables = mysqli_fetch_array($result)) {
#$con->query("ALTER TABLE $tables[0] CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
$con->query("ALTER TABLE $tables[0] CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci");
}
echo "The collation of your database has been successfully changed!";
Set the default collation of your database to utf8[mb4]_unicode_ci manually.
If you have problems with utf8mb4_unicode_ci use utf8_unicode_ci instead.