Location: PHPKode > scripts > MySQL Charset correction tool > mysql-charset-correction-tool-v1-0/convert.php
<?
/**********************************************/
// MySQL Charset correction tool v1.0
// By Global Internet d.o.o.
// www.global.ba
// 04/09/2009 - Programming by Mario Mijic
/**********************************************/
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>MySQL - Charset correction script</title>
<style type="text/css">
<!--
body,td,th {
	font-family: Arial, Helvetica, sans-serif;
	font-size: 12px;
	color: #000;
}
body {
	background-color: #FFF;
	margin-left: 0px;
	margin-top: 0px;
	margin-right: 0px;
	margin-bottom: 0px;
}
.output {
	font-family: "Courier New", Courier, monospace;
	font-size: 12px;
	padding-left:20px;
	padding-right:20px;
}
.title {
	font-family: Arial, Helvetica, sans-serif;
	font-size: 16px;
	font-weight: bold;
	text-decoration: none;
}
-->
</style></head>

<body>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
  <tr>
    <td height="25" align="center" valign="middle">&nbsp;</td>
  </tr>
  <tr>
    <td height="50" align="center" valign="middle" class="title">MySQL - Charset correction tool v1.0</td>
  </tr>
  <tr>
    <td height="25" align="center" valign="middle">&nbsp;</td>
  </tr>
  <tr>
    <td align="center" valign="middle"><form id="main_form" name="main_form" method="post" action="convert.php">
      <table width="500" border="0" cellspacing="0" cellpadding="0">
        <tr>
          <td width="200" height="25" align="left" valign="middle">MySQL host</td>
          <td width="300" height="25" align="left" valign="middle"><label>
            <input name="host" type="text" id="host" style="width:300px" value="<? if ($_REQUEST['host'] == "") {echo "localhost";} else {echo $_REQUEST['host'];} ?>" />
          </label></td>
        </tr>
        <tr>
          <td width="200" height="25" align="left" valign="middle">MySQL port</td>
          <td width="300" height="25" align="left" valign="middle"><label>
            <input name="port" type="text" id="port" style="width:300px" value="<? if ($_REQUEST['port'] == "") {echo "3306";} else {echo $_REQUEST['port'];} ?>" />
          </label></td>
        </tr>
        <tr>
          <td width="200" height="25" align="left" valign="middle">MySQL database</td>
          <td width="300" height="25" align="left" valign="middle"><label>
            <input name="database" type="text" id="database" style="width:300px" value="<? echo $_REQUEST['database']; ?>" />
          </label></td>
        </tr>
        <tr>
          <td width="200" height="25" align="left" valign="middle">MySQL username</td>
          <td width="300" height="25" align="left" valign="middle"><input name="username" type="text" id="username" style="width:300px" value="<? echo $_REQUEST['username']; ?>" /></td>
        </tr>
        <tr>
          <td width="200" height="25" align="left" valign="middle">MySQL password</td>
          <td width="300" height="25" align="left" valign="middle"><input name="password" type="password" id="password" style="width:300px" value="<? echo $_REQUEST['password']; ?>" /></td>
        </tr>
        <tr>
          <td width="200" height="25" align="left" valign="middle">Desired charset</td>
          <td width="300" height="25" align="left" valign="middle"><label>
            <select name="charset" id="charset" style="width:300px">
              <option value="big5" <? if ($_REQUEST['charset'] == "big5") {echo "selected=\"selected\"";} ?>>Big5 Traditional Chinese</option>
              <option value="dec8" <? if ($_REQUEST['charset'] == "dec8") {echo "selected=\"selected\"";} ?>>DEC West European</option>
              <option value="cp850" <? if ($_REQUEST['charset'] == "cp850") {echo "selected=\"selected\"";} ?>>DOS West European</option>
              <option value="hp8" <? if ($_REQUEST['charset'] == "hp8") {echo "selected=\"selected\"";} ?>>HP West European</option>
              <option value="koi8r" <? if ($_REQUEST['charset'] == "koi8r") {echo "selected=\"selected\"";} ?>>KOI8-R Relcom Russian</option>
              <option value="latin1" <? if ($_REQUEST['charset'] == "latin1") {echo "selected=\"selected\"";} ?>>ISO 8859-1 West European</option>
              <option value="latin2" <? if ($_REQUEST['charset'] == "latin2") {echo "selected=\"selected\"";} ?>>ISO 8859-2 Central European</option>
              <option value="swe7" <? if ($_REQUEST['charset'] == "swe7") {echo "selected=\"selected\"";} ?>>7bit Swedish</option>
              <option value="ascii" <? if ($_REQUEST['charset'] == "ascii") {echo "selected=\"selected\"";} ?>>US ASCII</option>
              <option value="ujis" <? if ($_REQUEST['charset'] == "ujis") {echo "selected=\"selected\"";} ?>>EUC-JP Japanese</option>
              <option value="sjis" <? if ($_REQUEST['charset'] == "sjis") {echo "selected=\"selected\"";} ?>>Shift-JIS Japanese</option>
              <option value="hebrew" <? if ($_REQUEST['charset'] == "hebrew") {echo "selected=\"selected\"";} ?>>ISO 8859-8 Hebrew</option>
              <option value="tis620" <? if ($_REQUEST['charset'] == "tis620") {echo "selected=\"selected\"";} ?>>TIS620 Thai</option>
              <option value="euckr" <? if ($_REQUEST['charset'] == "euckr") {echo "selected=\"selected\"";} ?>>EUC-KR Korean</option>
              <option value="koi8u" <? if ($_REQUEST['charset'] == "koi8u") {echo "selected=\"selected\"";} ?>>KOI8-U Ukrainian</option>
              <option value="gb2312" <? if ($_REQUEST['charset'] == "gb2312") {echo "selected=\"selected\"";} ?>>GB2312 Simplified Chinese</option>
              <option value="greek" <? if ($_REQUEST['charset'] == "greek") {echo "selected=\"selected\"";} ?>>ISO 8859-7 Greek</option>
              <option value="cp1250" <? if ($_REQUEST['charset'] == "cp1250") {echo "selected=\"selected\"";} ?>>Windows Central European</option>
              <option value="gbk" <? if ($_REQUEST['charset'] == "gbk") {echo "selected=\"selected\"";} ?>>GBK Simplified Chinese</option>
              <option value="latin5" <? if ($_REQUEST['charset'] == "latin5") {echo "selected=\"selected\"";} ?>>ISO 8859-9 Turkish</option>
              <option value="armscii8" <? if ($_REQUEST['charset'] == "armscii8") {echo "selected=\"selected\"";} ?>>ARMSCII-8 Armenian</option>
              <option value="utf8" <? if ($_REQUEST['charset'] == "utf8" or $_REQUEST['charset'] == "") {echo "selected=\"selected\"";} ?>>UTF-8 Unicode</option>
              <option value="ucs2" <? if ($_REQUEST['charset'] == "ucs2") {echo "selected=\"selected\"";} ?>>UCS-2 Unicode</option>
              <option value="cp866" <? if ($_REQUEST['charset'] == "cp866") {echo "selected=\"selected\"";} ?>>DOS Russian</option>
              <option value="keybcs2" <? if ($_REQUEST['charset'] == "keybcs2") {echo "selected=\"selected\"";} ?>>DOS Kamenicky Czech-Slovak</option>
              <option value="macce" <? if ($_REQUEST['charset'] == "macce") {echo "selected=\"selected\"";} ?>>Mac Central European</option>
              <option value="macroman" <? if ($_REQUEST['charset'] == "macroman") {echo "selected=\"selected\"";} ?>>Mac West European</option>
              <option value="cp852" <? if ($_REQUEST['charset'] == "cp852") {echo "selected=\"selected\"";} ?>>DOS Central European</option>
              <option value="latin7" <? if ($_REQUEST['charset'] == "latin7") {echo "selected=\"selected\"";} ?>>ISO 8859-13 Baltic</option>
              <option value="cp1251" <? if ($_REQUEST['charset'] == "cp1251") {echo "selected=\"selected\"";} ?>>Windows Cyrillic</option>
              <option value="cp1256" <? if ($_REQUEST['charset'] == "cp1256") {echo "selected=\"selected\"";} ?>>Windows Arabic</option>
              <option value="cp1257" <? if ($_REQUEST['charset'] == "cp1257") {echo "selected=\"selected\"";} ?>>Windows Baltic</option>
              <option value="binary" <? if ($_REQUEST['charset'] == "binary") {echo "selected=\"selected\"";} ?>>Binary pseudo charset</option>
              <option value="geostd8" <? if ($_REQUEST['charset'] == "geostd8") {echo "selected=\"selected\"";} ?>>GEOSTD8 Georgian</option>
            </select>
          </label></td>
        </tr>
        <tr>
          <td width="200" height="25" align="left" valign="middle">&nbsp;</td>
          <td width="300" height="25" align="left" valign="middle"><label>
            <input type="submit" name="start" id="start" value="Start" />
            <input type="reset" name="Reset" id="button" value="Reset" />
          </label></td>
        </tr>
      </table>
      <br />
      Copyright&nbsp;&copy;&nbsp;Global&nbsp;Internet&nbsp;d.o.o.,&nbsp;2009.&nbsp;<a href="http://www.global.net.ba/">www.global.net.ba</a>
    &nbsp;-&nbsp;Programming&nbsp;by&nbsp;Mario&nbsp;Mijić
    </form></td>
  </tr>
  <tr>
    <td height="25" align="center" valign="middle">&nbsp;</td>
  </tr>
  <tr>
    <td height="25" align="left" valign="middle" class="output">
<?
if ($_POST['start'] and $_REQUEST['database'] != "" and $_REQUEST['username'] != "" and $_REQUEST['password'])
{
	$username = $_REQUEST['username'];
	$password = $_REQUEST['password'];
	$database = $_REQUEST['database'];
	$host = $_REQUEST['host'];
	$port = $_REQUEST['port'];
	$charset = $_REQUEST['charset'];
	
	if ($host == "") {$host = "localhost";}
	if ($port == "") {$port = "3306";}
	
	//MySQL reserved vords
	$reserved = array ('accessible', 'add', 'all', 'alter', 'analyze', 'and', 'as', 'asc', 'asensitive', 'before', 'between', 'bigint', 'binary', 'blob', 'both', 'by', 'call', 'cascade', 'case', 'change', 'char', 'character', 'check', 'collate', 'column', 'condition', 'constraint', 'continue', 'convert', 'create', 'cross', 'current_date', 'current_time', 'current_timestamp', 'current_user', 'cursor', 'database', 'databases', 'day_hour', 'day_microsecond', 'day_minute', 'day_second', 'dec', 'decimal', 'declare', 'default', 'delayed', 'delete', 'desc', 'describe', 'deterministic', 'distinct', 'distinctrow', 'div', 'double', 'drop', 'dual', 'each', 'else', 'elseif', 'enclosed', 'escaped', 'exists', 'exit', 'explain', 'fetch', 'float', 'float4', 'float8', 'for', 'force', 'foreign', 'from', 'fulltext', 'grant', 'group', 'having', 'high_priority', 'hour_microsecond', 'hour_minute', 'hour_second', 'if', 'ignore', 'in', 'index', 'infile', 'inner', 'inout', 'insensitive', 'insert', 'int', 'int1', 'int2', 'int3', 'int4', 'int8', 'integer', 'interval', 'into', 'is', 'iterate', 'join', 'key', 'keys', 'kill', 'leading', 'leave', 'left', 'like', 'limit', 'linear', 'lines', 'load', 'localtime', 'localtimestamp', 'lock', 'long', 'longblob', 'longtext', 'loop', 'low_priority', 'master_ssl_verify_server_cert', 'match', 'mediumblob', 'mediumint', 'mediumtext', 'middleint', 'minute_microsecond', 'minute_second', 'mod', 'modifies', 'natural', 'no_write_to_binlog', 'not', 'null', 'numeric', 'on', 'optimize', 'option', 'optionally', 'or', 'order', 'out', 'outer', 'outfile', 'precision', 'primary', 'procedure', 'purge', 'range', 'read', 'read_write', 'reads', 'real', 'references', 'regexp', 'release', 'rename', 'repeat', 'replace', 'require', 'restrict', 'return', 'revoke', 'right', 'rlike', 'schema', 'schemas', 'second_microsecond', 'select', 'sensitive', 'separator', 'set', 'show', 'smallint', 'spatial', 'specific', 'sql', 'sql_big_result', 'sql_calc_found_rows', 'sql_small_result', 'sqlexception', 'sqlstate', 'sqlwarning', 'ssl', 'starting', 'straight_join', 'table', 'terminated', 'then', 'tinyblob', 'tinyint', 'tinytext', 'to', 'trailing', 'trigger', 'undo', 'union', 'unique', 'unlock', 'unsigned', 'update', 'usage', 'use', 'using', 'utc_date', 'utc_time', 'utc_timestamp', 'values', 'varbinary', 'varchar', 'varcharacter', 'varying', 'when', 'where', 'while', 'with', 'write', 'xor', 'year_month', 'zerofill', 'false', 'true');
	
	//MySQL Connect
	$dbconn = mysql_connect("$host:$port", "$username", "$password")
	or die (mysql_error());
	
	echo "<br /><strong>MySQL connection opened.</strong><br /><hr />";
	
	//MySQL select database
	mysql_select_db($database)
	or die (mysql_error());
	
	echo "<br /><strong>Selected MySQL database $database to be converted to $charset charset.</strong><br /><hr />";
	
	echo "<br /><strong>Starting to convert columns in database $database.</strong><br /><hr /><br />";
	
	//Get MySQL tables
	$tab = mysql_query("SHOW TABLES FROM $database")
	or die (mysql_error());
	
	echo "<strong>Found ". mysql_num_rows($tab) ." table/s.</strong><br /><hr />";

	while (list ($tables) = mysql_fetch_array($tab))
	{
		//Get fields from tables
		$fil = mysql_query("SHOW COLUMNS FROM $tables WHERE TYPE LIKE 'TINYTEXT' OR TYPE LIKE 'TEXT' OR TYPE LIKE 'MEDIUMTEXT' OR TYPE LIKE 'LONGTEXT'")
		or die (mysql_error());
		
		echo "<br /><strong>Found ". mysql_num_rows($fil) ." columns for conversion in table $tables.</strong><br />";
		
		if (mysql_num_rows($fil) <= 0)
		{
			echo "&nbsp;&nbsp;&nbsp;- Nothing to be converted...";
		}
		
		while (list ($fields, $types) = mysql_fetch_array($fil))
		{
			$check_type = explode ("(", $types);
			//Double check for correct types to avoid errors
			if ($check_type[0] == "tinytext" or $check_type[0] == "text" or $check_type[0] == "mediumtext" or $check_type[0] == "longtext")
			{
				echo "&nbsp;&nbsp;&nbsp;- Processing column <strong>$fields</strong> of type <strong>$check_type[0]</strong>";
				
				$check_len = explode (")", $check_type[1]);
				$length = $check_len[0];

				if (in_array($fields, $reserved))
				{
					echo "&nbsp;(<strong>Warning: </strong>column name is MySQL reserved word)...";
				}
				else
				{
					echo "...";	
				}
				
				//auto execute
				if ($check_type[0] == "tinytext")
				{
					mysql_query ("ALTER TABLE `$tables` MODIFY `$fields` TINYBLOB;")
					or die (mysql_error());
					
					mysql_query ("ALTER TABLE `$tables` MODIFY `$fields` TINYTEXT CHARACTER SET $charset;")
					or die (mysql_error());
					echo "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<strong>DONE!</strong><br />";
				}
				elseif ($check_type[0] == "text")
				{
					mysql_query ("ALTER TABLE `$tables` MODIFY `$fields` BLOB;")
					or die (mysql_error());
					
					mysql_query ("ALTER TABLE `$tables` MODIFY `$fields` TEXT CHARACTER SET $charset;")
					or die (mysql_error());
					echo "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<strong>DONE!</strong><br />";
				}
				elseif ($check_type[0] == "mediumtext")
				{
					mysql_query ("ALTER TABLE `$tables` MODIFY `$fields` MEDIUMBLOB;")
					or die (mysql_error());
					
					mysql_query ("ALTER TABLE `$tables` MODIFY `$fields` MEDIUMTEXT CHARACTER SET $charset;")
					or die (mysql_error());
					echo "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<strong>DONE!</strong><br />";
				}
				elseif ($check_type[0] == "longtext")
				{
					mysql_query ("ALTER TABLE `$tables` MODIFY `$fields` LONGBLOB;")
					or die (mysql_error());
					
					mysql_query ("ALTER TABLE `$tables` MODIFY `$fields` LONGTEXT CHARACTER SET $charset;")
					or die (mysql_error());
					echo "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<strong>DONE!</strong><br />";
				}
				else
				{
					echo "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<strong>WRONG COLUMN TYPE - NOTHING TO BE DONE!</strong><br />";
				}
			}
		}
		echo "<hr />";
	}
	
	echo "<br /><strong>Conversion complete</strong><br /><hr />";
	
	//MySQL close connestion
	mysql_close ($dbconn);
	
	echo "<br /><strong>MySQL connection closed</strong><br /><hr />";
}
?>
    </td>
  </tr>
  <tr>
    <td height="25" align="center" valign="middle">&nbsp;</td>
  </tr>
  <tr>
    <td height="25" align="center" valign="middle">&nbsp;</td>
  </tr>
</table>
</body>
</html>
Return current item: MySQL Charset correction tool