How to change collation of database, table, column mysql

Author: 

pe


Description: 

How to change the collation for all the tables to UTF-8 in a MySQL database?

Such changes cannot be performed through the cPanel phpMyadmin feature or any other feature we are providing. In order to change the collation for all the tables in a certain database you should create a custom script, put it in convert.php, put it in the root of your server and execute it on the server. It should contain




Kind:

PHP


PHP:

<?php
//Database Connection
$host = 'localhost';
$db_name = 'onder21_sandbox';
$db_user =  'onder21_sandbox';
$db_pass = 'mNSaRHg5x';

$con = mysql_connect($host,$db_user,$db_pass);

if(!$con) { echo "Cannot connect to the database ";die();}

  mysql_select_db($db_name);

  $result=mysql_query('show tables');

  while($tables = mysql_fetch_array($result)) {
    foreach ($tables as $key => $value) {
    mysql_query("ALTER TABLE $value CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci");
  }
}

echo "The collation of your database has been successfully changed!";
?>