Russian chars in MySQL are Ok but no in the forum

[ntldr1962]

Hello!

we are developing a forum, then we have found this error:

we set up in the MySQL (via phpmyadmin) the encoding for the MySQL into UTF-8 because we are importing information in Russian language, then we have found this mistakes:

1.- in the MySQL database we can read fine the text but not in the forum (see images please)

2.- In the admincp language english we set up also the encoding in UTF-8

3.- but in theforum we see weird signs like ????

what did i forgot?

any help would be appreciated

[Christos Teriakis]

Originally Posted by ntldr1962

Hello!

we are developing a forum, then we have found this error:

we set up in the MySQL (via phpmyadmin) the encoding for the MySQL into UTF-8 because we are importing information in Russian language, then we have found this mistakes:

1.- in the MySQL database we can read fine the text but not in the forum (see images please)

2.- In the admincp language english we set up also the encoding in UTF-8

3.- but in theforum we see weird signs like ????

what did i forgot?

any help would be appreciated

I don't think that you used the right way, especially if you have already data in your database.

1. Using COLLATE utf8_general_ci is not the right choice. Better use utf8_unicode_ci
2. Changing it from phpMyAdmin is also not correct, especially when you're also changing charset eg from iso-8859-1 to utf8 avoid to use phpMyAdmin.
3. The best way is to do the changes from the command line if you've shell access.
4. Finally, after conversion you need to change the data from your backup sqldump (hope you know that you've to get a full database backup before such actions), by using iconv

Chris

[ntldr1962]

ok let me try to perform the actions that you recommend =)

thanks in advance

[ntldr1962]

Ready! we solved it thank you!

at the end we used a php script to convert the Database, and also "DONT FORGET" to put in languages in the Admincp "utf8" not UTF-8

here is my simple script:

<?php


// Fill in your configuration below
$db_server = 'server';
$db_user = 'user';
$db_password = 'pass';
$db_name = 'db';
$char_set = 'utf8';



header('Content-type: text/plain');

$connection = mysql_connect($db_server, $db_user, $db_password) or die(mysql_error() );

$db = mysql_select_db($db_name) or die( mysql_error() );

$sql = 'SHOW TABLES'; $result = mysql_query($sql) or die( mysql_error() );

while ( $row = mysql_fetch_row($result) )
{
$table = mysql_real_escape_string($row[0]);
$sql = "ALTER TABLE $table DEFAULT CHARACTER SET $char_set COLLATE utf8_unicode_ci";
mysql_query($sql) or die( mysql_error() );

print "$table changed successfully.\n";
}


// Update the Collation of the database itself
$sql = "ALTER DATABASE CHARACTER SET $char_set;";
mysql_query($sql) or die( mysql_error());

print "Database collation has been updated successfully.\n";

// close the connection to the database
mysql_close($connection);

?>

[thincom2000]

Altering the DEFAULT collation of your existing tables is not enough. You also need to alter the collation of each column in the table, because it will still keep the old collation. The default is only used for new columns that are added. Learned this the hard way myself.