Foreign characters turn into garbage in mysql

Question

I am in the U.S. I have the following line in my web page:

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

And my MYSQL table is MyISAM latin1_swedish_ci

But when someone fills out a form with a foreign character it gets stored in MySql as garbage. An example would be an e with accent over it, etc. - something not normally used in American English. Even if someone uses a weird apostrophe, its get turned into garbage:

nation's

gets turned into:

nation’s

I'm using a regular apostrophe there but you get the idea. Foreign characters turn into 3 garbage characters. Please help! TIA

Answer 1

Either change your document's header to

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

or - better - change your tables' character set to UTF-8. To do that is not entirely trivial, just changing the tables' collation won't do the trick. This SO question might give some pointers.

Answer 2

It seems to me the data being entered into the database is utf8. To put that into the table it has to be converted to latin1 (since that is the characterset of your column) and this can yield one up to three latin1 characters.

Pekka just beat me to it - his solution should work to remedy this problem.

Answer 3

I can remember same case in SQlServer hope it will be helpful with this issue.

It has two data types varchar [8 bit] and NVarChar [16 bit].

Now using varchar you cannot store 16 bit scripts characters like Chines, Indic etc you have to use NVarchar i.e. data type which can store the 16 bit characters.