fetch chinese letter in sql to display in php

Question

I'm trying to retrieve chinese letters from mysql database and display on php. I have tried everything on the internet but it still shows me "?" on the website. Here is my code that related to this question:

<html class="fsvs" lang="zh-Hans" >
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />

In php part:

$conn = mysqli_connect( $host, $user, $pass, $db );
if (!$conn) {
 die('Database connection failed.');
}
//get gan char
mysql_query("SET character_set_results=utf8", $conn);
$query_gan = "SELECT * FROM gan WHERE gan_id = '$gan_id'";
$result_gan = mysqli_query($conn,$query_gan);
$row_gan = mysqli_fetch_array($result_gan);

thanks in advance :D

score 0 · Accepted Answer · answered Oct 08 '16 at 05:29

When I had a similar issue I firstly displayed the encoding of my text in php using

echo mb_detect_encoding ( $text );

It shows the encoding of the text coming from my query. This showed me that I was getting ASCII from mysql_query when Chinese or Russian characters were in my database.

The change I made was with the following addition after the mysql_connect

mysql_set_charset('UTF8');

My database is utf8_unicode_ci collation and I can see chinese characters.

So, if mb_detect_encoding is now giving you UTF8 for your text then you would be able to show chinese characters.

The next step is to make sure what you pass to the browser has the correct header...

header('Content-Type: text/html; charset=UTF-8');

Put the above at the top of your code in php to make sure the browser is expecting your encoding.

Now that should the question, however ideally you should be using PDO or mysqli rather than mysql_connect method. In this case the equivalent procedural style commands are..

$link = mysqli_connect('localhost', 'my_user', 'my_password', 'test');
mysqli_set_charset($link, "utf8");

Where $link is the equivalent to your connection to the database.

OMG!! it works !!! thank you sooooo much! i was gonna give up after putting the header, then I tried to replace mysqli_set_charset('UTF8') to ($link, "UTF8") it works!!! — Youyou Gao, Oct 08 '16 at 10:15

score 0 · Answer 2 · edited May 23 '17 at 10:34

Do not mix mysql_* and mysqli_* calls; use only the latter.

https://stackoverflow.com/a/38363567/1766831 explains what to do about ?. In particular, it points out

The bytes to be stored are not encoded as utf8mb4 (alias UTF-8 outside MySQL). Fix this.
The column in the database is CHARACTER SET utf8mb4. Fix this.
Also, check that the connection during reading is UTF-8.

Your <meta ...> is good.

Inside MySQL, use utf8mb4 (not utf8) for Chinese.

The question marks already in the table cannot be turned back into Chinese characters.

fetch chinese letter in sql to display in php

2 Answers2