Special characters in PHP / MySQL
Solution 1
Changed the HTML charset to ISO-8859-1 fixed the problem! Silly
Solution 2
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.
Solution 3
Issue SET NAMES 'utf8'
right after connecting:
$mysqli = new mysqli("localhost", "user", "password", "database");
$mysqli->query("SET NAMES 'utf8'");
Solution 4
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);
Solution 5
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'");
Related videos on Youtube
Jonathan
Updated on July 09, 2022Comments
-
Jonathan almost 2 years
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!!!
-
Jonathan about 15 yearsI have unicode encoding already: <meta http-equiv="content-type" content="text/html; charset=utf-8" /> And its not working...
-
Jonathan about 15 yearsI 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);
-
Jonathan about 15 yearsCan you explain to me how use SET when connecting using mysqli??
-
vartec about 15 yearswhat's your database encoding?
-
vartec about 15 yearsBTW. if you manually select UTF8 as encoding in browser, does it display OK?
-
Jonathan about 15 yearsbrowser encoding also in utf8. btw this is the character I get: �
-
Jonathan about 15 yearsdatabase is utf8_spanish.. tables are utf8_spanish... browser is in utf8.... btw this is the character I get: �
-
vartec about 15 yearsThen you actually have it encoded in ISO-8859-1 (or ISO-8859-15)
-
Stefan Gehrig about 15 yearsDid 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.
-
Stefan Gehrig about 15 yearsActually 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.
-
Jonathan about 15 yearsYes, 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!
-
Noitidart over 8 yearsDoing
mysql_set_charset('utf8', $conn)
worked for me when I was usingucs2_unicode_ci
on my mysql table field. -
Rick James over 8 yearsThe black diamond (�) is the browser's way of saying wtf. It comes from having latin1 characters, but telling the browser to display utf8 characters. It sounds like Jonathan has latin1 throughout, except for this one place. Sure utf8 is preferred.