Special characters in PHP / MySQL

Hi, I have in the database words that include special character (in Spanish mostly, like tildes). In the database everything is saved and shown correctly with PHPmyAdmin, but when I get the data (using PHP) and display it in a browser, I get a weird character, like a "?" with a square... I need a general fix so I don't need to escape each character every time, and also I would be able to insert special Spanish characters from a PHP form into the database...

The HTML is correct:

<meta http-equiv="content-type" content="text/html; charset=utf-8" />

All tables and databas are set to utf8_spanish

The character I get: �

Any suggestions???

Thanks!!!

From stackoverflow

• Turn on unicode encoding in your HTML.

  MrValdez : kgiannakakis got it right while I was editing.

  Jonathan : I have unicode encoding already: And its not working...

• You probably need to set the content-type header properly:

  header('Content-type: text/html; charset=utf-8');
  

  This question may also help.

  Jonathan : I ahve the correct content-type already, and its not working

  kgiannakakis : Meta header in the html isn't enough. You also need to send the correct HTTP header.

  Jonathan : btw this is the character I get: �

  kgiannakakis : You can send the correct http header using the directive in my answer.

  bobince : Meta header in the HTML is, generally speaking, enough. Proper HTTP headers are still desirable, but won't affect this problem.

• Issue SET NAMES 'utf8' right after connecting:

  $mysqli = new mysqli("localhost", "user", "password", "database");
  $mysqli->query("SET NAMES 'utf8'");
  

  Jonathan : what???? can you explain a little bit please?

• Have MySQL translate it automatically

  $conn = mysql_connect('host', 'user', 'password');
  mysql_set_charset('utf8',$conn);
  

  http://es.php.net/manual/en/function.mysql-set-charset.php

  EDIT: from your comment I gather, that this is actually encoded in latin1 so

  mysql_set_charset('latin1_spanish_ci',$conn);
  

  Jonathan : I have added it, but still nothing changed. $conn = new mysqli('localhost', $user, $pwd, 'maindb') or die ('Cannot open database'); return $conn; mysql_set_charset('utf8',$conn);

  vartec : what's your database encoding?

  vartec : BTW. if you manually select UTF8 as encoding in browser, does it display OK?

  Jonathan : everything is utf8_spanish...

  Jonathan : browser encoding also in utf8. btw this is the character I get: �

  vartec : Then you actually have it encoded in ISO-8859-1 (or ISO-8859-15)

  Jonathan : Thanks, ISO-8859-1 worked for me!

• When dealing with special characters I always take care of the following:

  • Database, table and field character sets are all set to utf8_general_* or utf8_unicode_*
  • I make sure my editor saves PHP files with the right character set
  • I set default_charset in php.ini to UTF-8 or
  • I send a Content-Type: text/html; charset=UTF-8 header
  • The charset in the META tag is UTF-8 (this is overriden by the Content-Type HTTP header)
  • When connecting to MySQL I issue the following queries:
    • SET NAMES utf8
    • SET CHARACTER SET utf8
    • SET COLLATION_CONNECTION="utf8_general_ci"/"utf8_general_ci"
  Jonathan : Can you explain to me how use SET when connecting using mysqli??

  Ionuț G. Stan : Just use mysqli_query($query), where $query is one of those SET... queries.

  Stefan Gehrig : Actually it's not relevant what encoding is used on the server side as long as the connection charset is able to represent all the characters used in the server-, database, table- oder column-encoding.

• I'd just like ro provide some more details on the solution proposed by vartec which is (depending on your MySQL installation) the most correct soultion to your problem. First of all the character set / encoding issue in MySQL is a somehow complex subject which is extensively covered in the MySQL manual Chapter 9.1 "Character Set Support". In your case especially 9.1.4. "Connection Character Sets and Collations" will be most relevant.

  To make it short: MySQL must know which character set / encoding your client application (talking from the database persoective that's your PHP script) is expecting as it'll transcode all the string data from the internal character set / encoding defined at server-, database-, table- or column-level into the connection character set / encoding. You're using UTF-8 on the client side so must tell MySQL that you use UTF-8. This is done by the MySQL command SET NAMES 'utf8' which must be send as the first query on opening a connection. Depending on your installation and on the MySQL client library you use in the PHP script this can be done automatically on each connect.

  If you use PDO it's just a matter of setting a configuration parameter

  $db = new PDO($dsn, $user, $password);
  $db->setAttribute(PDO::MYSQL_ATTR_INIT_COMMAND, "SET NAMES 'utf8'");
  

  I hope that will help to make the whole thing more understandable.

  EDIT:

  Using mysqli changing the client character set / encoding is even more simple:

  $mysqli = new mysqli("localhost", "user", "password", "db");
  $mysqli->set_charset("utf8");
  

• Are you sure you have UTF8 data in your database to begin with?

  Jonathan : database is utf8_spanish.. tables are utf8_spanish... browser is in utf8.... btw this is the character I get: �

• Changed the HTML charset to ISO-8859-1 fixed the problem! Silly

  Stefan Gehrig : Did you read my answer to your problem? Changing the character set of your HTML page will require to recode your static HTML content to ISO-8859-1 charset. Using the correct MySQL client encoding will be the most simple solution possible.

  Jonathan : Yes, I read your answer and worked on it, but it didn't get to fix the problem. I used the mysqli option but nothing changed... I don't have so much code to modify in the HTML so its a good option for me just to change to ISO-8859-1.. Thank you very much!

• holy crap.. this was driving me insane. i was trying to do the same thing. i added a utf-8 encoding HTML header.. mysqli_set_charset() saved me, hours later, but I am glad I have it working now

  another function to try for encoding special characters and accents is htmlentities()

• mysql_set_charset worked for me. thank you so much

• thank you so much, working fine