Issues with accents and strange characters in PHP/MySQL (Solved)

29 Jul 2010

This post was last updated on October 2010, it may be out of date or inaccurate.

Usually, when creating a website in PHP and MySQL, there’s a problem when introducing accents and strange characters, typically from foreign languages like Spanish or French: these get changed into áóñ and similar strange stuff.
This happened to me a while ago and I freaked out and started tweaking everything, changing charsets and converting everything to HTML safe like ó but none of these worked or I had such a big database already that it would take ages to change everything manually.

The problem here is that the charset of special characters is not the same in the MySQL database, the PHP language compiler and the Apache server.

So I started to investigate and after some research I’ve made up a step-by-step list of all the essential things you should check and do in order to solve this. 98% of the people that have tried this have been successful.

Step by Step Guide on How to Solve the Encoding Issues with Weird Characters

  1. When creating the database in MySQL make sure all the string fields have utf8_spanish_ci charset and the charset of the tables is
    utf_unicode_ci (you can change it later in phpMyAdmin going to Operations > Collation)
  2. Make sure you specify a Content-type in all your HTML files, inside the <head> tag: 
    <meta http-equiv="Content-type" content="text/html; charset=utf-8" />
  3. And in those files you output without HTML headers (XML, AJAX/JSON calls, APIs…) put this in the PHP: 
    header("Content-Type: text/html;charset=utf-8");
  4. When establishing the connection between PHP and MySQL send this query before any other: 
    mysql_query("SET NAMES 'utf8'");
  5. Remove the DefaultCharset in Apache or modify it
Following these steps will solve the weird accents problem in your server. If it still doesn’t work post a comment below and we’ll help you.

Photo by Eduardo

               
       

Comments

11 comments to Issues with accents and strange characters in PHP/MySQL (Solved)

  • Avatar Andy

    Hello Xavi,

    Thanks for such a great post.

    How ever I tried to fix the issue with your article, But I did’t get success.
    Here are the two URL which shows very strange characters.
    Data is coming from Database.

    http://mob.immobelgica.be/nl/result/0%28new%29/All_Properties-verkopen-Belgi%C3%83%C2%AB.html

    http://mob.immobelgica.be/nl/search/handel-kantoor/te-huur/belgiã«/west-vlaanderen/brugge/62046/

    Please please please help me. I have already thrown the arms, but after I read your article, my hope are once again alive.

    Please let me know how you can help me.

    Thanks
    Andy

    November 25th, 2010 at 4:22 pm
  • Avatar Xavi

    Hi Andy,

    Have you followed all the steps in the article?
    Make sure the characters in the database are saved correctly.
    Are you using a CMS or you coded the website from scratch?

    November 25th, 2010 at 6:39 pm
  • Avatar Alan Anderson

    Hi Xavi,
    I have followed your instructions almost to the letter. Table is set to utf8_unicode_ci (not utf_unicode_ci), the problem field which is a varchar(255) is set to utf8_spanish_ci. I have added mysql_query(“SET NAMES ‘utf8′”); as the first line of the script which makes the connection to the database. But I don’t know how I could follow your step 5: “Remove the DefaultCharset in Apache or modify it”.
    My problem is that acute and grave accents and some other characters are shown as a � symbol.

    November 29th, 2010 at 12:20 am
  • Avatar Miles Reid

    This was an excellent article – Xavi – it solved my problem perfectly!
    Thank you.
    Miles

    July 15th, 2011 at 11:39 am
  • Avatar Marc

    Thnx a lot for this article! With mysql_query(“SET NAMES ‘utf8′”); i managed to fix my issue.

    July 29th, 2011 at 1:55 pm
  • Avatar Andrea

    Thanks very much to who wrote this article. He solved me a problem very clearly.
    Great !!!

    August 30th, 2011 at 4:33 pm
  • Avatar Afonso Marques

    THANK YOU! THANK YOU! THANK YOU! THANK YOU!

    What a !”#$%&/(/&%$# headache I got during the day trying to figure what was wrong with PHP, MySQL, what ever…

    Database with accents, collations OK, head ok, but…
    This command mysql_query(“SET NAMES ‘utf8′”) did the trick!

    September 7th, 2011 at 6:27 pm
  • Avatar James

    Me too – big thanks for showing up in Google when I was desperate, and for the SET Names tip which solved a problem I’d been tearing my hair out over!

    December 14th, 2011 at 1:50 pm
  • Avatar Marko

    Thank you very much i fixed my problem! Thank you thank you thank you!

    January 31st, 2012 at 5:07 am
  • Avatar Jon

    Hi,
    what about importing data from a txt file??

    I’ve created a simple random generation and I get the odd ‘bits’ The php fix works where you’ve also got a data base connection but what happens if you have none?

    February 2nd, 2012 at 1:39 pm
  • Avatar Dan

    Thanks!! Tried mysql_query(“SET NAMES ‘utf8′”); first, hoping that the database was already setup right and it worked! Thank you so much for this!

    February 2nd, 2012 at 2:46 pm

Comment Form