Reputation: 5007

Character Encoding & Databases

I am having a big problem with character encoding accross my domains. The big thing for me really is that I don't understand it. I set all my websites to be utf-8 using the meta tag:

<meta charset="UTF-8">

Which seemed to have solved a few problems a while back. Now I am seeing problems with between the website and the database, when a user enters their first or last name, and it has an accent in it, it doesnt display correctly. However I ran the following test.

I created a test table called 'test' (imaginative I know)

I wrote a very tiny script to take the value from a text box and put it into this table and then display the contents of this table each time this page displays, so I could see what is going on.

Here are some screen shots, first, from the output of the page:

The <code>var_dump</code> of the database results I get from tabe 'test'

And then a screenshot of the database itself: Quick view of phpMyAdmin table <code>test</code>

So the type of column first is VARCHAR(50), I just left the settings as I would do normally, and the character encoding was latin_swedish1 or something. After id 4, I changed it utf8_bin, but that still didnt make a difference.

The problem is, the data still display okay on the website, but looks terrible in the database. Is that a problem, is this how it should be done? I think the problems I the users are complaining about are when it is put into emails and PDFs etc, which I don't think I set character encoding on.

Any help and advice would be greatly welcomed.

Upvotes: 1

Answers (4)

Anas Jaghoub

Reputation: 138

Make sure to have the charset is utf8 when you created the table. create table my_table ( id int primary key, ..... ) engine=innodb charset=utf8;

and make sure that your connection is setting the charset for utf8. it depends on each framework you're using.

You can set internal character to utf-8 /* Set internal character encoding to UTF-8 */ mb_internal_encoding("UTF-8"); check http://php.net/manual/en/function.mb-internal-encoding.php

Upvotes: 1

Pedram marandi

Reputation: 1614

also add your character encoding to database connection if you have mysqli connection use : mysqli::set_charset

if you use PDO follow this:

PDO_MYSQL DSN

Upvotes: 0

GMasucci

Reputation: 2882

When the3 column type was assigned, how was it assigned? I would ensure that it was along the lines of VARCHAR(n) CHARSET utf8 as that would make your column type correct for the UTF8 standard, more normally I see the UCS2 (VARCHAR(n) CHARSET ucs2) which can then cause problems later down the line.

you can set this using NVARCHAR ( see http://dev.mysql.com/doc/refman/5.0/en/charset-national.html) since sql 5.

To change the type of a column on its own you can use this type of command:

Alter table tablenamehere MODIFY columnidhere newdatatypehere;

for example

Alter table mytabelforphp MODIFY oldvarcharcolumnId nvarchar(1024);

Let me know if you need more info:)

Upvotes: 1