Mysql Character Sets

Hey all,

I have an application that sits on a internal clients desktop sending data to and from a server, some stuff gets saves into the database. Everything client side uses UTF8. When the desktop app sends data to the server, the server application connects to the database, it seems to save into the database fine however when i’m viewing some of that data on a website the encoding is incorrect. It display áéóí etc as ‘?’ - I have specified the encoding for the website as UTF8, using PHP headers and also in the <head> tags in HTML.

So I have a few questions.

  1. If you don’t specify a character set when you connect to a mysql database where does it get the default character set from for that particular connection? Or does it just use whatever the character set is on the table you insert into?
  2. Can I store the UTF8 string sent from the client in a table/database with a character of latin1? or will it give me problems?
  3. Should if specify the character set to UTF8 EVERYTIME i make a connection to the database to ensure it uses that encoding?

The database/table is set as latin1, It’s too late for me to change it as there is other applications which use the same database and other tables so i don’t know if changing the charsets will cause data issues.

From inserting the data to viewing it, something is going wrong and its hard for me to track it down.

Client Desktop->Server App->Database<-Website<-Client Browser(or admin)

Hope that all makes sense.

Thanks in advance.

Steve

Create the tables with UTF-8.

Thanks, If that’s the only way to do it then i will.

Can you answer these?

  1. If you don’t specify a character set when you connect to a mysql database where does it get the default character set from for that particular connection? Or does it just use whatever the character set is on the table you insert into?
  2. Can I store the UTF8 string sent from the client in a table/database with a character of latin1? or will it give me problems?

Thanks.

No I can’t. I always set tables to UTF-8. And it would be logical (to me) that if you create a table with latin1, then the characters accepted by that table are the ones specified by latin1. But I could be completely wrong :slight_smile:

Let’s see what others have to say about this. @r937; for example

thanks for axing, but sorry, i don’t mess around with the complexities of character sets and/or encodings

There are default settings for character sets and collations at four levels: server, database, table, and column.

http://dev.mysql.com/doc/refman/5.0/en/charset-column.html

So yeah, you can store the UTF8 string sent from the client in a table/database with a character of latin1.

Thanks Guys. Sorted it out, changed column char sets to UTF8 - Converted all data from latin1, all seems fine except when returning results from jquery script. Just a header i guess.

Ty