Hi im migrating SMF 2.0.9 to Mybb 1.8.4
The default language on both forums is UTF-8 with hebrew.
i ran the merge system (the latest one ) and every thing went ok ... but
every text(posts and fourms names and pm ) are garbled.
but if i look at the sql tables in both MYBB and SMF the text looked the same (ie not modfied)
i tried running the merge system with convert UTF and dont convert and the result is still the same .
Do you have any idea whats going on ?
Can you look at the SMF tables with eg phpMyAdmin and check whether it says "utf8_unicode_ci" as collation?
it says utf8_general_ci
i wrote a simple php connect and select from both tables and it looks ok , the weird thing all i did was
echo "id: " . $row["pmid"]. " - Name: " . $row["message"]. " " . $row["subject"]. "<br>";
and for the smf it showd the correct encoding ,
and in the mybb output i had to set the encoding and then i saw the hebrew correctly
PS : thanks for the quick reply
any ideas any one ?
btw if i look at the text from mysql console it looks like proper hebrew
Have you set the correct encoding when installing the mybb forum? (You can change it in "inc/config.php")
yes its in config['database']['encoding'] = 'utf8';
so everything seems to be at utf-8 , could it be either utf8-binary ?
No, "utf8_unicode_ci" (or simply "utf8" in the config file) should be correct. Can you send me a database dump so I can test it locally?
i was able to fix the issue looking at some issues with other systems it looks like the DB is UTF8 and the text is saved at UTF8 but the smf was using latin1 so its stored as utf but transferd as latin1 so when the smf pulls the data it takes it as latin1 and display it , i wrote a small php to convert it correctly
took some code from online source and modified it :
<?php
$conn = new mysqli('localhost', '', '', 'TESTDB1');
//$conn->query("SET NAMES LATIN1") or die(mysql_error()); #uncomment this if you already set UTF8 names somewhere
// get all tables in the database
$tables = array();
$ex_tables=array();
$ex_tables[]="smf_tapatalk_push";
$query = $conn->query("SHOW TABLES");
while ($t = mysqli_fetch_row($query)) {
if(in_array($t[0], $ex_tables)) {
echo "excluding" . $t[0] ;}
else{
$tables[] = $t[0];}
}
// you need to set explicit tables if not all tables in your database are latin1 charset
// $tables = array('mytable1', 'mytable2', 'mytable3'); # uncomment this if you want to set explicit tables
// duplicate tables, and copy all data from the original tables to the new tables with correct encoding
// the hack is that data retrieved in correct format using latin1 names and inserted again utf8
print_r($tables);
foreach ($tables as $table) {
$temptable = $table . '_temp';
$conn->query("CREATE TABLE $temptable LIKE $table") ;
echo $table;
$conn->query("ALTER TABLE $temptable CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci") ;
$query = $conn->query("SELECT * FROM `$table`") ;
$conn->query("SET NAMES UTF8");
while ($row = mysqli_fetch_row($query)) {
$values = implode("', '", $row);
$conn->query("INSERT INTO `$temptable` VALUES('$values')") ;
}
$conn->query("SET NAMES LATIN1") ;
}
// drop old tables and rename temporary tables
// this actually should work, but it not, then
// comment out this lines if this would not work for you and try to rename tables manually with phpmyadmin
foreach ($tables as $table) {
$temptable = $table . '_temp';
$conn->query("DROP TABLE `$table`") ;
$conn->query("ALTER TABLE `$temptable` RENAME `$table`") ;
}
// now you data should be correct
// change the database character set
$conn->query("ALTER DATABASE DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci") ;
// now you can use "SET NAMES UTF8" in your project and mysql will use corrected data
?>