MySQL character set for ISO-8859-1

Our web server’s character set is ISO-8859-1. This has caused some problems when persons input accented characters or other non-ASCII characters into our database. Well, actually the problems occurred when the information was parsed to a webpage.

I’ve tried fixing this several times, but could not figure out how to set the character set on my MySQL tables to ISO-8859-1. Until today.

It seems the MySQL equivalent of ISO-8859-1 is ‘latin1′. I simply set the tables’ character sets to ‘latin1’, and voilĂ ! Now I have all my accented characters.

Just thought I’d share in case others have the same problem.

By Kim Siever

I am a copywriter and copyeditor. I blog on writing and social media tips mostly, but I sometimes throw in my thoughts about running a small business. Follow me on Twitter at @hotpepper.

12 comments

  1. Thanks for the tip. Somehow I didn’t connect you were also a ColdFusion guru. Good stuff, I’ve been working with it for about 5 years now.

  2. Guru’s a pretty strong word. You have a lot of faith in my ability. :)

  3. Unfortunately, this does not work for such characters as em dashes and curly quotes. I’d be interested in any other solutions out there to get em dashes and curly quotes working.

  4. I am researching a problem we are experiencing with what you describe on this post. Recently our web host helped us solve an encoding problem where characters would not display properly on our Spanish site. The solution was to encode any ColdFusion Templates that would display data from our MySQL database in Spanish using ISO-8859-1. Now, we have a page of the site that uses the ColdFusin MX DateFormat function in order to display events sorted by date. If we do not use the DateFormat function, our dates show up like {ts ‘2007-01-11 00:00:00’} which is not very user friendly for our viewers. However, using the function to format the date makes the Spanish characters appear incorrectly on that page only, even though we are using the correct encoding. I did some trial and error tests and removing the formatting of the date makes the characters display properly again. Do you have any workarounds for this?

  5. All my tables in MySQL are latin1, plus all the CF pages have the following at the very top of the page as instructed by the web host support team:

    Why would the DateFormat function affect the rest of the characters on the record?

  6. I’m not too sure. I’ve never seen this problem. granted, I am trying to think of an instance where I used accented characters and DateFormat on the same page and I cannot.

  7. I have looked everywhere for answers on this and I am starting to think that maybe CF is not the best server technology for us. I am starting to delve into PHP which is also supported by our web host. Maybe I will have better luck with that. Either way, thanks for your time and if I hear back from Ben Forta (author of the two books I use the most on both MySQL and CF, I will post an update here.

  8. That’d be great. Maybe your solution will address my problem in comment #4.

  9. Thanks man, it saved a lot of my time and thanks for keeping this blog alive.

    cheers! ;)

Comments are closed.