phplist forums

Posted: **9:05pm, Wed 11 Jan, 2012**

I thing that is very good for people to have under the configure menu the ..
changing collation

That for us for unicode languages, we need to change all tables to another COLLATION.

I think that is very simple for the developers to insert the changing collation code.

Code: Select all: <?php $db = mysql_connect('localhost','myuser_mydbuser','mypassword'); if(!$db) echo "Cannot connect to the database - incorrect details"; mysql_select_db('myuser_mydbname'); $result=mysql_query('show tables'); while($tables = mysql_fetch_array($result)) { foreach ($tables as $key => $value) { mysql_query("ALTER TABLE $value COLLATE utf8_general_ci"); }} echo "The collation of your database has been successfully changed!"; ?>

Thanh's in advance.

Posted: **10:48am, Thu 12 Jan, 2012**

As i check, the script before no good changes made. So from phoca.cz we have an excellent tool for changing collation, the tool_phoca_changing_collation.zip.

Here is the code in index.php file. Very very good tool.

Code: Select all: <?PHP /* * @tool Phoca Changing Collation * @Changing collation of database, tables and columns * @Run this script only at your own risk. If you have a big database * @you need to change the script execution time in your php * @copyright (C) Jan Pavelka www.phoca.cz (http://www.phoca.cz) * @license http://www.gnu.org/copyleft/gpl.html GNU/GPL * @based on script from http://php.vrana.cz/ - Author - Jakub Vrana * @license http://creativecommons.org/licenses/by/2.5/ * @Creative Commons Attribution 2.5 Generic */ ?> <!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" xml:lang="en-gb" lang="en-gb"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <meta name="robots" content="index, follow" /> <meta name="keywords" content="phoca, server unzip" /> <meta name="description" content="Phoca Server Unzip tool" /> <meta name="generator" content="www.phoca.cz" /> <title>Phoca Server Unzip tool</title> <style type="text/css"> body {font-family: Arial, sans-serif; font-size: 10px; color: #000000 ;} h1 a {color:#006699;text-decoration:none;} #info {position: relative;float:right; top:10px; right:10px; text-align:right;margin-bottom:10px;} .error {font-weight:bold;color:#c10000} .warning {font-weight:bold;color:#ff8102} .success {font-weight:bold;color:#008040} .window {position:relative;top:10px;left:10px;width:95%;padding:5px;height:300px;overflow:auto;border:1px solid #000;background:#fbfbfb;clear:both;} </style> </head> <body> <div id="info"> <img src="assets/phoca-logo.png" alt="Phoca" /><br /> <a href="http://www.phoca.cz/">www.phoca.cz</a><br /> <a href="http://www.phoca.cz/forum">www.phoca.cz/forum</a> </div> <h1><a href="index.php">Phoca Changing Collation tool</a></h1> <?php function start_db($mysqlhost,$mysqldatabase, $mysqluser, $mysqlpass) { global $conn; $conn = mysql_connect($mysqlhost, $mysqluser, $mysqlpass); if (!$conn) { echo '<a href="index.php" class="back">Back to the main site</a><br />'; die('Database error.'); } $select = mysql_select_db($mysqldatabase, $conn); if (!$select) { echo '<a href="index.php" class="back" >Back to the main site</a><br />'; die('Database error.'); } } function end_db ($conn) { mysql_close($conn); } if ( isset($_POST['host']) && isset($_POST['user']) && isset($_POST['pass']) && isset($_POST['name']) && isset($_POST['col'])) { $mysqlhost = $_POST['host']; $mysqluser = $_POST['user']; $mysqlpass = $_POST['pass']; $mysqldatabase = $_POST['name']; $collation = $_POST['col']; // Change the time ------------------------------------- $changedMaxExecTime = 0; $standardMaxExecTime = ini_get('max_execution_time'); if ($standardMaxExecTime != 0 && $standardMaxExecTime < 120) { set_time_limit(120); $changedMaxExecTime = 1; } // ----------------------------------------------------- start_db($mysqlhost,$mysqldatabase, $mysqluser, $mysqlpass); //Start code from http://php.vrana.cz/ - Author - Jakub Vrana function mysql_convert($query) { echo '<div>' . $query . ' ... <span style="color:#26d92b;">OK</span></div>'; return mysql_query($query); } echo '<div></div>'; echo '<div class="window">'; mysql_convert("ALTER DATABASE $mysqldatabase COLLATE $collation"); $result = mysql_query("SHOW TABLES"); while ($row = mysql_fetch_row($result)) { mysql_convert("ALTER TABLE $row[0] COLLATE $collation"); $result1 = mysql_query("SHOW COLUMNS FROM $row[0]"); while ($row1 = mysql_fetch_assoc($result1)) { if (preg_match('~char|text|enum|set~', $row1["Type"])) { mysql_convert("ALTER TABLE $row[0] MODIFY $row1[Field] $row1[Type] CHARACTER SET binary"); mysql_convert("ALTER TABLE $row[0] MODIFY $row1[Field] $row1[Type] COLLATE $collation" . ($row1["Null"] ? "" : " NOT NULL") . ($row1["Default"] && $row1["Default"] != "NULL" ? " DEFAULT '$row1[Default]'" : "")); } } } echo '</div>'; mysql_free_result($result); //End code from http://php.vrana.cz/ - Author - Jakub Vrana end_db($conn); echo '<p> </p><a href="index.php" class="back">Back to the main page</a>'; // Set back the time -------------------- if ($changedMaxExecTime == 1) { set_time_limit($standardMaxExecTime); } // -------------------------------------- } else { ?> <h2>Change database collation (DATABASE, TABLES, COLUMNS)</h2> <form action="index.php" method="post"> <table> <tr><td>Database Host</td><td><input type="text" name="host" value="localhost" /></td></tr> <tr><td>Database User</td><td><input type="text" name="user" value="username" /></td></tr> <tr><td>Database Password</td><td><input type="password" name="pass" value="password" /></td></tr> <tr><td>Database Name</td><td><input type="text" name="name" value="database name" /></td></tr> <tr><td>Database Collation</td><td><input type="text" name="col" value="utf8_general_ci" /></td></tr> <tr><td></td><td><input type="submit" value="Submit" /></td></tr> </table> </form> <?php } ?> </body> </html>

phplist forums

changing collation

changing collation

Re: changing collation