CODEWITHSUNDEEP
phpmysqlutf-8mysqli
Marcus
Marcus

Reputation: 11

UTF-8 special characters not retrieved from database

I have a MySQL database including words in all kinds of languages, therefore I set the table columns to "utf8_unicode_ci". UTF-8 can include all possible characters and the unicode_ci also has correct sorting (and lower performance). All special character looks OK when I browse the table in phpMyAdmin. The problem is when I SQL SELECT a bunch of names and display them on my web site the characters can't be displayed/are not encoded right (looks like question marks in Chrome, squares in Explorer etc.). I’ve got the right encoding for my site: <meta HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=UTF-8"> I can also display all kinds of characters if I paste them on my page. The problem only shows up when retrieving them from the database. Does the php mysqli_query() change the encoding or does the SELECT query somehow mess it up?

Upvotes: 1

Views: 2632

Answers (3)

Your Common Sense
Your Common Sense

Reputation: 157870

Although @user4035's intentions are all right, both implementation and reasoning are wrong.

  1. do not output any errors unconditionally right into browser. Programmer won't see it, while user has nothing to do with it.
  2. SET NAMES 'UTF8' is not equal to set_charset() function. The former is a particular SQL query which shouldn't be used in favor of the latter function.

Better version would be as follows.

You have to set connection charset using set_charset() method:

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$con=mysqli_connect("host", "user", "pw", "db");
$con->set_charset("utf8");

Upvotes: 2

user4035
user4035

Reputation: 23729

Try using SET NAMES 'UTF8' after connecting to MySQL:

$con=mysqli_connect("host", "user", "pw", "db");
if (!$con)
{
    die('Failed to connect to mySQL: ' .mysqli_connect_errno());
}

/* change character set to utf8 */
if (!$con->set_charset("utf8")) {
    printf("Error loading character set utf8: %s\n", $con->error);
}

As the manual says:

SET NAMES indicates what character set the client will use to send SQL statements to the server... It also specifies the character set that the server should use for sending results back to the client.

Upvotes: 0

Jos&#233; Neves
Jos&#233; Neves

Reputation: 323

Try:

mysql_query("SET NAMES 'utf8'", $con);

before

mysql_select_db("asdasd", $con);

Upvotes: 0

Related Questions