UTF-8 problem when saving to mysql

Question

My website is using charset iso 8859 1

<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />

when user post chinese character, it will be saved into the database as & # 2 0 3 2 0 ; & # 2 2 9 0 9 ; which will output as the chinese character when retrieved.

I need to set my website to UTF-8

when user post chinese character, it will be saved as some funky character in the mysql, and when retrieved, some characters are correct but some are wrong.

my question is, after i set to UTF-8, how to i make mysql save the text as # 2 0 3 2 0 ; & # 2 2 9 0 9 ; instead of funky chars.

i tried to use htmlentities. it's not working correctly.

my script is using

$message = htmlentities(strip_tags(mysql_real_escape_string($_POST['message']),'<img><vid>')); 

when it's saved to mysql it is like this 你好 when it's retrieved to be display is like this ä½ å¥½ <---- is the funky character that is stored in mysql previously

Answer 1

Try to tell mysql to use utf before executing any query. You can put this query in any file that is used in all files. replace "utf8_swedish_ci" with your collation

$sql='SET NAMES "utf8" COLLATE "utf8_swedish_ci"';
mysql_query($sql);

Answer 2

If you are trying to go to UTF8 after you are already using another encoding, try these steps:

1. Run ALTER TABLE tablename CONVERT TO CHARACTER SET UTF8 on your tables

2. Configure MySQL to default to UTF8 or just run the SET NAMES UTF8 query once you establish a database connection. You only need to run it once per connection since it sets the connection to UTF8.

3. Output a UTF8 header before delivering content to the browser. header('Content-Type: text/html; charset=utf-8');

4. Include the UTF8 content-type meta tag on your page. meta http-equiv="Content-Type" content="text/html; charset=utf-8"

If you do all those steps, everything should work fine.

Answer 3

You should follow ajreal's advice on setting your encodings to UTF-8.

However, from the sound of it you may already have data stored in the database which will have to be converted.

If your website is uniformly iso-8859-1 then most likely Chinese characters are stored as HTML character entities, which means that data is not not stored mis-encoded and converting the character sets should not cause problems. If you carry out the instructions and find that characters appear incorrectly afterwards, it might be because text is stored mis-encoded, in which case there are steps that can be taken to remedy the situation.

Character sets for an existing column may be converted using syntax like

ALTER TABLE TableName MODIFY ColumnName COLUMN_TYPE CHARACTER SET utf8 [NOT NULL]

where COLUMN_TYPE is one of CHAR(n), VARCHAR(n), TEXT and the square brackets indicate that NOT NULL is optional.

Edit

"my question is, after i set to UTF-8, how to i make mysql save the text as # 2 0 3 2 0 ; & # 2 2 9 0 9 ; instead of funky chars."

This might be best tackled in your scripting language rather than in MySQL. If using PHP you might be able to use htmlentities() for this purpose.

Answer 4

User N before your values. Like:

mysql_query ("insert into ".$mysql_table_prefix."links ( url, title) values ( N'$url ', N'$title');

Answer 5

i been seeing lots of encoding related issues the owner should google a bit before posting

general check list:

1. mysql table schema to use utf-8
2. mysql clients connection to use utf-8 mysql --default-character-set=utf8
3. php mysqli_set_charset to utf-8
4. html encoding to utf-8
5. putty, emac clients... to be in utf-8