php executing mysql query with Chinese character,error in SQL syntax

Question

I am trying to use php to execute mysql query that contains Chinese characters. The following code works on my previous machine.

$sql="
SELECT 
部门,
编码
...
 FROM pb ";
if ($conn->query($sql)){
echo "sueccess";
}
else{
echo "MySQL Query fail". $conn->error;}

However after migrated to a new machine, where I re-built the database, the code stopped working. I get the "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '�门, 大类 FROM pb' at line 20" error.

When I change the query to all English characters, I have no error. My guess is something wrong with the Chinese characters I am using.

The new database is a copy of the previous one, only when I was copying it, I have to create a new MySQL database and use the import query to put the data in. I selected " utf8_general_ci" for the old machine and the new machine.

NOTED: the query works in database directly, when I submit the query through phpMyAdmin. But it does not work in the php file.

Try setting the charset on the connection first: http://php.net/manual/en/mysqlinfo.concepts.charset.php — Ultimater, Apr 08 '18 at 09:05
All it takes, is one wrong charset setting in your application - *everything* needs to be the same charset! I have previously written [**an answer about UTF-8 encoding**](https://stackoverflow.com/a/31899827/4535200) that contains a little checklist, that will cover *most* of the charset issues in a PHP/MySQL application. There's also a more in-depth topic, [**UTF-8 All the Way Through**](https://stackoverflow.com/q/279170/4535200). Most likely, you'll find a solution in either one or both of these topics. — Qirel, Apr 08 '18 at 09:10
It's also recommended that you keep every name-identifier in English, avoid spaces and special characters. — Qirel, Apr 08 '18 at 09:11
Possible duplicate of [UTF-8 all the way through](https://stackoverflow.com/questions/279170/utf-8-all-the-way-through) — Qirel, Apr 08 '18 at 09:11

Bernd Buffen · Answer 1 · 2018-04-09T15:17:15.410

Put the fieldnames in backticks like this:

SELECT 
`部门`,
`编码`
 FROM ch;

sample

MariaDB [test]> select * from ch;
+----+-------------+--------+
| id | 部门        | 编码   |
+----+-------------+--------+
|  1 | my text     |   1234 |
|  2 | second text |   5678 |
+----+-------------+--------+
2 rows in set (0.01 sec)

MariaDB [test]> SELECT 
    -> `部门`,
    -> `编码`
    ->  FROM ch;
+-------------+--------+
| 部门        | 编码   |
+-------------+--------+
| my text     |   1234 |
| second text |   5678 |
+-------------+--------+
2 rows in set (0.00 sec)

MariaDB [test]>

sample 2

<?php
$servername = "127.0.0.1";
$username = "root";
$password = "";
$dbname = "test";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);


// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$sql="SELECT
id,
`部门`,
`编码`
 FROM ch;";

$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo "id: " . $row["id"]. "\t - Col1: " . $row["部门"]. "\tCol2: " . $row["编码"]. "\n";
    }
} else {
    echo "0 results";
}
$conn->close();
?>

Result

$ php ch.php 
id: 1    - Col1: my text    Col2: 1234
id: 2    - Col1: second text    Col2: 5678

the query works in database directly, when I submit the query through phpMyAdmin. But it does not work in the php file. And I tried the backwords slack, it does not make a difference. — Moon Yin, Apr 09 '18 at 07:42
@Moon Yin - look at y answer. i have extend it with a php sample, and it works — Bernd Buffen, Apr 09 '18 at 15:17
thank you for your update! that is what I have. It works on the first machine I developed. But after migrating it to another machine, it stopped working. — Moon Yin, Apr 10 '18 at 08:01

php executing mysql query with Chinese character,error in SQL syntax

1 Answers1