Special characters in PHP / MySQL

Question

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!!!

Answer 1

I'd just like to provide some more details on the solution proposed by vartec which is (depending on your MySQL installation) the most correct solution to your problem. First of all the character set / encoding issue in MySQL is a somewhat 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 sent 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'");

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

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

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

Answer 2

Issue SET NAMES 'utf8' right after connecting:

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

Answer 3

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);

Answer 4

I found this from somewhere and since then have been using it as whole without thinking too much.

mysql_query("SET character_set_results = 'utf8', character_set_client = 'utf8', character_set_connection = 'utf8', character_set_database = 'utf8', character_set_server = 'utf8'");

Answer 5

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

Answer 6

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()

Answer 7

You can simply do:

$link = mysql_connect("host", "user", "password");
mysql_select_db("database", $link); mysql_query("SET NAMES 'utf8';", $link);

Answer 8

The answer that worked for me is the one that Stefan Gehrig posted:

$mysqli->set_charset("utf8");

I just needed to add that line after having defined the connection and my problems where solved! :)

(I am posting an answer since I cannot comment...).

Answer 9

Turn on unicode encoding in your HTML.

Answer 10

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"

Answer 11

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

Answer 12

The following answer may be valuable to those connecting to their MySQL or Mariadb database the procedural way from PHP using the MySQLi functions like I often do when not connecting via PDO (the PHP Data Objects).

Here is How you get to do it the Procedural way. See below;

<?php

$host = "localhost";
$user = "user";
$passwd = "password";
$database = "database";

$con = mysqli_connect($host, $user, $passwd, $database);
mysqli_set_charset($con, "utf8");

// Check connection
if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  exit();
}

?>

Answer 13

my solution is : do all the above offered but also im working with notepad++ and the file must be saved like this :go to encoding in the manu tab and save the file ->encode in utf8

Answer 14

Interestedly, this code didn't work for me:

$link = mysqli_connect('localhost', 'my_user', 'my_password', 'test');
mysqli_set_charset($link, "utf8");
printf("Current character set: %s\n", mysqli_character_set_name($link));
//shows latin1

However, setting the charset inside a condition works:

if (!mysqli_set_charset($link, "utf8")) {
    printf("Error loading character set utf8: %s\n", mysqli_error($link));
    exit();
} else {
    printf("Current character set: %s\n", mysqli_character_set_name($link));
    //shows utf8
}

My setup:

$ rpm -qa|grep -E "php|maria"
mariadb-5.5.56-2.el7.x86_64
mariadb-server-5.5.56-2.el7.x86_64
mariadb-libs-5.5.56-2.el7.x86_64
php-pdo-5.4.16-45.el7.x86_64
php-cli-5.4.16-45.el7.x86_64
php-5.4.16-45.el7.x86_64
php-common-5.4.16-45.el7.x86_64
php-mysql-5.4.16-45.el7.x86_64

Answer 15

This answer here will come in handy for those connecting to their MySQL or Mariadb database the procedural way from PHP using the MySQLi functions like me.

Here is How you do it the Procedural way folks. See below;

<?php

$host = "localhost";
$user = "user";
$passwd = "password";
$database = "database";

$con = mysqli_connect($host, $user, $passwd, $database);
mysqli_set_charset($con, "utf8");

// Check connection
if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  exit();
}

?>