Internationalization

Simplified Chinese characters display as question marks

Permalink 1 user found helpful June 27, 2009 at 5:56 AM

I am trying to do a very simple website. Content needs to be in both English and Simplified Chinese.

Whenever I try to enter Chinese characters in the blocks, the output is a series of question marks.

Is this one of the software's limitations, or is it something I'm dong wrong?

Many thanks in advance to anyone who can help me.

David

jump replied on May 20, 2010 at 2:38 pm Permalink Reply

I have the same problem, and I use the 5.4 version.

Could anyone give me some help?

telly replied on Aug 26, 2010 at 8:57 pm Permalink Reply

I have the same problem, and I really want to add Chinese to my site. Could sb. help, please?

okhayat replied on Aug 26, 2010 at 9:00 pm Permalink Reply

Use utf8_unicode_ci and it should work fine.
Change your database encoding.
Run the following SQL replacing DATABASE with your database name:

ALTER DATABASE DATABASE DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci

telly replied on Aug 27, 2010 at 10:05 am Permalink Reply

And how and where can I change the database encoding please?Thank you.

okhayat replied on Aug 27, 2010 at 10:49 am Permalink Reply

Either from the command line:
$ mysql
> use DATABASE;
> ALTER DATABASE DATABASE DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci

or from PHPMyAdmin (usually through cPanel) or locally:
1. Select the Database (from the left)
2. Click on Operations tab (top right)
3. Goto Collation (at the bottom)
4. Select 'utf8_unicode_ci' and click Go

jonacello replied on Nov 7, 2010 at 9:04 am Permalink Reply

Thanks! I was having the same problem, but fixed it in cPanel as you said... I have no idea how to write code, so thanks for the details.

Tip for others: you have to click on phpMyAdmin in cPanel to get to the place where you can change your databases as referenced by okhayat. Actually, once you are logged into phpMyAdmin, I think you can change the collation for all of your databases at once on the main page--but somebody correct me if I'm wrong about that.

ctang replied on May 5, 2011 at 3:57 am Permalink Reply

okhayat, thank you very much for the instruction. it's very helpful.

ablecomms replied on Jun 30, 2011 at 8:31 am Permalink Reply

I am building a Chinese website. I corrected the collation to 'utf8_unicode_ci' according to your instructions in PhpMyAdmin. However, it does not remove the problem. Chinese characters are sitll showed as question marks.

How to do? Thanks in advance.

hillem replied on Aug 13, 2011 at 9:05 pm Permalink Reply

I too need some help. I have followed the steps but still get question marks...

Could this be a problem with my concrete5 theme?

:(

engagingit replied on Sep 13, 2011 at 2:24 am Permalink Reply

I'm doing a site just now that has multiple languages and was having problems getting the chinese characters to be accepted by my database. I changed the collation to utf_general_ci in phpmyadmin but the problem was it didin't change individual fields so didn't work.

I then found this script which i placed as a php file in the root of my website and ran it.

http://stackoverflow.com/questions/105572/a-script-to-change-all-ta...

Please make sure you backup your database before trying it and if it goes wrong then you can restore. If you don't backup you might lose everything!!!

The script worked perfectly for me though.

hillem replied on Oct 16, 2011 at 12:48 pm Permalink Reply

Thanks! This appears to have worked on my website! You are the best.

stephanosm replied on Nov 5, 2011 at 12:23 pm Permalink Reply

where is that? In my cPanel I have few choices. The one on the left it says MySQL databases. When I clicked on it I have Create DB, Modify Db but no operations of collation.

Please help

engagingit replied on Nov 5, 2011 at 3:08 pm Permalink Reply

Do you have access to phpmyadmin?

If not install the package sqlbuddy from Remo as I'm pretty sure it gives you the option to change the collation.

Also make sure you have a backup of the datrabase before you do anything.

Cheers
Al

stephanosm replied on Nov 5, 2011 at 4:04 pm Permalink Reply

I went to the PhpAdmin and it has a drop down box which allows you to change to any collation. So i switch to utf8_unicode_ci . I went back to my page and I can write Greek but not Chinese!

engagingit replied on Nov 5, 2011 at 4:08 pm Permalink Reply

yeah you need to switch the collation on every table within the database. That's why I used the script mentioned above as it does it automatically.

C5 has a lot of tables to do it manually :)

stephanosm replied on Nov 5, 2011 at 4:41 pm Permalink Reply

Is this the code?
How should I name my file?
and from below code what do I change? db1 to my DBname? Anything else? Sorry but I am not a php programmer :-)

<?php // your connection mysql_connect("localhost","root","***"); mysql_select_db("db1");  // convert code $res = mysql_query("SHOW TABLES"); while ($row = mysql_fetch_array($res)) {     foreach ($row as $key => $table)     {         mysql_query("ALTER TABLE " . $table . " CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci");         echo $key . " =&gt; " . $table . " CONVERTED<br />";     } } ?>

engagingit replied on Nov 5, 2011 at 4:56 pm Permalink Reply

no, the code is as follows

<?php
function MysqlError()
{
        if (mysql_errno())
        {
                echo "<b>Mysql Error: " . mysql_error() . "</b>\n";
        }
}
$username = "root";
$password = "";
$db = "database";
$host = "localhost";
$target_charset = "utf8";
$target_collate = "utf8_general_ci";
echo "<pre>";

Viewing 15 lines of 127 lines. View entire code block.

 
<?php
 
function MysqlError()
{
        if (mysql_errno())
        {
                echo "<b>Mysql Error: " . mysql_error() . "</b>\n";
        }
}
 
$username = "root";
$password = "";
$db = "database";
$host = "localhost";
 
$target_charset = "utf8";
$target_collate = "utf8_general_ci";
 
echo "<pre>";
 
$conn = mysql_connect($host, $username, $password);
mysql_select_db($db, $conn);
 
$tabs = array();
$res = mysql_query("SHOW TABLES");
MysqlError();
while (($row = mysql_fetch_row($res)) != null)
{
        $tabs[] = $row[0];
}
 
// now, fix tables
foreach ($tabs as $tab)
{
        $res = mysql_query("show index from {$tab}");
        MysqlError();
        $indicies = array();
 
        while (($row = mysql_fetch_array($res)) != null)
        {
                if ($row[2] != "PRIMARY")
                {
                        $indicies[] = array("name" => $row[2], "unique" => !($row[1] == "1"), "col" => $row[4]);
                        mysql_query("ALTER TABLE {$tab} DROP INDEX {$row[2]}");
                        MysqlError();
                        echo "Dropped index {$row[2]}. Unique: {$row[1]}\n";
                }
        }
 
        $res = mysql_query("DESCRIBE {$tab}");
        MysqlError();
        while (($row = mysql_fetch_array($res)) != null)
        {
                $name = $row[0];
                $type = $row[1];
                $set = false;
                if (preg_match("/^varchar\((\d+)\)$/i", $type, $mat))
                {
                        $size = $mat[1];
                        mysql_query("ALTER TABLE {$tab} MODIFY {$name} VARBINARY({$size})");
                        MysqlError();
                        mysql_query("ALTER TABLE {$tab} MODIFY {$name} VARCHAR({$size}) CHARACTER SET {$target_charset}");
                        MysqlError();
                        $set = true;
 
                        echo "Altered field {$name} on {$tab} from type {$type}\n";
                }
                else if (!strcasecmp($type, "CHAR"))
                {
                        mysql_query("ALTER TABLE {$tab} MODIFY {$name} BINARY(1)");
                        MysqlError();
                        mysql_query("ALTER TABLE {$tab} MODIFY {$name} VARCHAR(1) CHARACTER SET {$target_charset}");
                        MysqlError();
                        $set = true;
 
                        echo "Altered field {$name} on {$tab} from type {$type}\n";
                }
                else if (!strcasecmp($type, "TINYTEXT"))
                {
                        mysql_query("ALTER TABLE {$tab} MODIFY {$name} TINYBLOB");
                        MysqlError();
                        mysql_query("ALTER TABLE {$tab} MODIFY {$name} TINYTEXT CHARACTER SET {$target_charset}");
                        MysqlError();
                        $set = true;
 
                        echo "Altered field {$name} on {$tab} from type {$type}\n";
                }
                else if (!strcasecmp($type, "MEDIUMTEXT"))
                {
                        mysql_query("ALTER TABLE {$tab} MODIFY {$name} MEDIUMBLOB");
                        MysqlError();
                        mysql_query("ALTER TABLE {$tab} MODIFY {$name} MEDIUMTEXT CHARACTER SET {$target_charset}");
                        MysqlError();
                        $set = true;
 
                        echo "Altered field {$name} on {$tab} from type {$type}\n";
                }
                else if (!strcasecmp($type, "LONGTEXT"))
                {
                        mysql_query("ALTER TABLE {$tab} MODIFY {$name} LONGBLOB");
                        MysqlError();
                        mysql_query("ALTER TABLE {$tab} MODIFY {$name} LONGTEXT CHARACTER SET {$target_charset}");
                        MysqlError();
                        $set = true;
 
                        echo "Altered field {$name} on {$tab} from type {$type}\n";
                }
                else if (!strcasecmp($type, "TEXT"))
                {
                        mysql_query("ALTER TABLE {$tab} MODIFY {$name} BLOB");
                        MysqlError();
                        mysql_query("ALTER TABLE {$tab} MODIFY {$name} TEXT CHARACTER SET {$target_charset}");
                        MysqlError();
                        $set = true;
 
                        echo "Altered field {$name} on {$tab} from type {$type}\n";
                }
 
                if ($set)
                        mysql_query("ALTER TABLE {$tab} MODIFY {$name} COLLATE {$target_collate}");
        }
 
        // re-build indicies..
        foreach ($indicies as $index)
        {
                if ($index["unique"])
                {
                        mysql_query("CREATE UNIQUE INDEX {$index["name"]} ON {$tab} ({$index["col"]})");
                        MysqlError();
                }
                else
                {
                        mysql_query("CREATE INDEX {$index["name"]} ON {$tab} ({$index["col"]})");
                        MysqlError();
                }
 
                echo "Created index {$index["name"]} on {$tab}. Unique: {$index["unique"]}\n";
        }
 
        // set default collate
        mysql_query("ALTER TABLE {$tab}  DEFAULT CHARACTER SET {$target_charset} COLLATE {$target_collate}");
}
 
// set database charset
mysql_query("ALTER DATABASE {$db} DEFAULT CHARACTER SET {$target_charset} COLLATE {$target_collate}");
 
mysql_close($conn);
echo "</pre>";

Place this in a file called dbupdate.php on your web server. Change the variables username, password and database at the start of the script to what your database expects, i.e. what you used when you installed C5.

browse to the script in your browser, i.e.http://www.yousite.com/dbupdate.php...

stephanosm replied on Nov 5, 2011 at 5:21 pm Permalink Reply

I did it but when i check it in my browser I get the error:
Mysql Error: Access denied for user 'oushuie1'@'localhost' (using password: NO)

in dbupdate.php I change the following to:

$username = "admin";
$password = "MyAdminPasswd1234";
$db = "oushuie1_cnc1";
$host = "localhost";

jamesjhg replied on Nov 20, 2011 at 5:26 am Permalink Reply

Where do i paste this on my web server? public_html?
any help would be appreciated.
I'm having problems showing chinese simplified characters.

engagingit replied on Nov 21, 2011 at 3:27 am Permalink Reply

yeah just place it in public_html and the go towww.www.yoursite.com/scriptname.php...

obvioulsy yoursite.com changes to your url and scriptname changes to what you have called the script in public_html

I would get rid of the script once you have ran it and it's all working.

okhayat replied on Dec 26, 2011 at 2:41 am Permalink Reply

I've recently face the issue again, but not with text coming from the DB. The theme PHP files had BOM character saved with the text. When I removed it using:
tail --bytes=+4 file.php
it fixed the issue for some sites, but not the others.
Hope this might help someone who gets stuck with the same issue.

Forums

Internationalization

Simplified Chinese characters display as question marks

Code

Post Reply

Delete Post

Mark Post as Spam

Destroy Spammer

Sign In?