Foreign characters in MySQL and the browser

Ange52

Hi everyone,

I'm hoping to do some work with foreign characters on my database, but I need to learn a little bit more about what is happening first. In the database, character sets are being saved to represent single foreign characters. For example, something like Ä« would be in the database, but would show up in the browser as è.

What is this called? And is there any listing out there that displays which sets turn into which foreign characters?

Any help pointing me in the right direction would be appreciated.

Thanks,
Ange52

MarkR

Sounds like you have a problem with encoding inconsistency between your MySQL database and application.

My guess is that your application is using UTF-8, but your database is not. This is a bad situation and should not be allowed to happen as it will create subtle problems later.

I recommend that you correctly set your application for UTF-8. You can do this by:

Create all tables as UTF-8 in the database
After connecting, use "SET NAMES utf8" to ensure that your connection is utf8
Send appropriate headers to ensure that all your web pages are utf8
Do internal string handling in a utf8-safe way
When talking to any external application (including mail), ensure that it is aware of what encoding you're trying to use.

Etc.

(NB: All of this assumes MySQL 4.1+)

If you're going to use utf8 you must do so entirely consistently throughout your application i.e. in all layers. Otherwise, bad things will happen.

Mark

Ange52

Hey Mark,

Thanks for the post. And inconsistency is definitely a problem here. The application I'm using was created by a friend of mine, and I still don't have full understanding of how information is being passed back and forth and stored.

I have started working on my test version to make sure that everything is in UTF-8 in all different places. This is getting tricky already, however, since the application involves Flash, PHP, MySQL, and XML -- and variables must be preserved going back and forth through all these different mediums.

In the meanwhile, do you know anything about the character sets that I talked about above? Although there might be an inconsistency between layers of my app, the database is very consistent with these values standing in for foreign characters. I would like to learn more about these for reference when improving the system and also when doing database backups, etc.

Thanks,
Ange52

MarkR

I'm guessing here, but I'd say that the database has been set for latin1 when the app was in fact using utf8. Fixing this may not be easy.

My best guess as to how to fix it would be:

Dump the schema and data separately from the existing database.
Modify the schema so it's in utf8 throughout, and re-create the tables
Change the data dump file, so that rather than being in latin1, which it will presumably claim to be, it says utf8, but WITHOUT altering the encoding of the rest of the file (This could be tricky as your editor may do this without asking you).
Reload the data from this dump file.

If my hunch is right, this SHOULD work, but I can't guarantee it.

Try it on your dev server first, obviously!

Mark