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

by
on July 29, 2010
(1 minute read)

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.

I’ve got invites for a free bank transfer!

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.

Things I’m buying on Amazon this week

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

Looking for cool gift ideas? TrendLiker.com lists hundreds of nice items and lets you know when they go on sale on Amazon.

Did you enjoy this article?

Grab me a coffee ☕

36 comments

  • Andy says:

    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

  • Xavi Author says:

    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?

  • Alan Anderson says:

    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.

  • Miles Reid says:

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

  • Marc says:

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

  • Andrea says:

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

  • Afonso Marques says:

    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!

  • James says:

    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!

  • Marko says:

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

  • Jon says:

    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?

  • Dan says:

    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!

  • Ted Wilson says:

    I already have a database created and managed with MySQL Workbench, where do it enter the details? How can I use the fix it in Workbench?

    Thanks

  • Ted Wilson says:

    I managed to work it out. Many Thanks Anyway!!!

  • Faissal says:

    yes, SET NAMES ‘utf8’ worked like a charm. thanks a lot!

  • Michael says:

    Holy he-double hockey sticks! SET NAMES ‘utf8’ works. So simple.

  • Matt Sturt says:

    Fantastic advice been having issues with special characters for while while reading from MYSQL database added the mysql_query(“SET NAMES ‘utf8′”); to the connection file and worked a treat!
    Another little job I can tick off thank you!

  • WlaFica says:

    Thanks a lot, works like a charm!

  • Gerardo Flores says:

    Hello, I’m having exactly this problem on a project and followed your instructions, however it didn’t work. I did not only issued SET NAMES but also a couple more, as I found here: http://verens.com/2006/05/05/some-utf8-problems-and-solutions/ but unfortunately it’s not working. I put everything on a connection file that I include on all scripts needed. The collation of all my mysql tables is utf-8_spanish2_ci

  • punita says:

    Thanks for the detailed steps. It works perfectly.

  • juan ignacio says:

    I can’t thank you enough!!!!
    you’re the best!

  • Ricardo says:

    you are a [email protected]$%^$’ genius!!!!! Thanks!!!

  • Amit says:

    I am trying to compare a accented string with a value in the db.example:

    SELECT cities.name, adm1.value, adm1.country FROM cities JOIN adm1 ON adm1.value=cities.adm1 WHERE adm1.country = ‘ES’ AND cities.name like ‘%Pollença%’

    when I execute this in the mysql sql console in phpmyadmin , it returns 2 rows.But running this with php function mysql_query($sql); returns false.

    SO I guess SET NAMES ‘utf8′ will not help here as the query is running perfect when executed directly.

    Any ideas on this??

  • Robert says:

    Something I only found after hours of searching:
    http://php.net/manual/en/function.htmlentities.php

    htmlentities() takes an optional third argument encoding which defines encoding used in conversion. If omitted, the default value for this argument is ISO-8859-1 in versions of PHP prior to 5.4.0, and UTF-8 from PHP 5.4.0 onwards.

    Drove me crazy for two hours until I found this

  • William says:

    Thank You.
    Step #4 was the step I was missing.
    You are a life saver!

  • Dafne says:

    Wow! I was going crazy when every accented character in our database disappeared after a server-wide php update (it’s an Italian website, so LOTS of accented characters!) and step #4 magically(?) solved everything! Thank you, thank you, thank you!

  • Buy Viagra Tablets says:

    Erectile dysfunction which is also known as ED or impotence means that you can not achieve
    and/or maintain an erection hard enough to have sex.

    In some cases the penis can just become partly erect.
    Some men have occasional problems when they can not get an erection when for example due to tiredness, stress or having
    drunk too much alcohol. However, it is important for men who have persistent or recurring
    erection problems to seek the right help.

  • Carlos Diaz says:

    Thanks Xavi, one line, one solution!!! :)
    Gracias from mx

  • Eduardo says:

    Simple but useful!

  • Paul Reichow says:

    Xavi, thanks for this solution…it worked 100% and solved a problem with accented characters that had been bugging me for years.

  • ziad says:

    Hello Xavi
    what are differences if I am using utf_general_ci in my D.B?

  • Doug says:

    Thank you very much! You solved my problem! \o/

  • Benoît (with an accent :p) says:

    Works nice! Thanks for the tip; I think i missed the “set names” command at mySQL connection.
    Cheers

  • Markus says:

    Hello Xavi,

    Step 4 just saved my life. I have been debugging my stuff for almost 3 hours now and couldn’t figure out what the hell was wrong with it.

    Thank you very, very much.

  • sunil saharan says:

    HI,
    I am adding data to the database through API and the data which i need to insert into table is in multi language. But I am not able to insert that data inside mysql table. I have set utf8_general_ci and do all the steps except last one in apache. Which i do not know from where to do. as I am using shared godaddy hosting. I am looking for long for the solution.

  • Ruben says:

    Straightforward solution. Thanks a lot!

  • Guti says:

    i solved it like this:
    1.) on MySQL Server
    set according tables, columns to utf8mb4_bin

    2.) open connection with php like this:

    $dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);

    if(!$dbc){
    die('error connecting to database');
    }
    else{

    //printf("Initial character set: %s\n", $dbc->character_set_name());

    /* change character set to utf8mb4 */
    if (!$dbc->set_charset("utf8mb4")) {
    //printf("Error loading character set utf8mb4: %s\n", $dbc->error);
    exit();
    } else {
    //printf("Current character set: %s\n", $dbc->character_set_name());
    }
    }

    Thanks though!

Follow me for cool new products and interesting findings on graphic design, web development, marketing, startups, life and humor.


/*Twitter*/ !function(d,s,id){var js,fjs=d.getElementsByTagName(s)[0];if(!d.getElementById(id)){js=d.createElement(s);js.id=id;js.src="//platform.twitter.com/widgets.js";fjs.parentNode.insertBefore(js,fjs);}}(document,"script","twitter-wjs"); /*Facebook (function(d, s, id) {var js, fjs = d.getElementsByTagName(s)[0];if (d.getElementById(id)) {return;}js = d.createElement(s); js.id = id;js.src = "//connect.facebook.net/en_GB/all.js#xfbml=1&appId=28624667607";fjs.parentNode.insertBefore(js, fjs);}(document, 'script', 'facebook-jssdk'));*/ /*Google+*/ window.___gcfg = {lang: 'en-GB'};(function() {var po = document.createElement('script'); po.type = 'text/javascript'; po.async = true;po.src = 'https://apis.google.com/js/plusone.js';var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(po, s);})();
Airbnb 25€ discount