Chinese characters ending up blank in Database

Question

Im making an API for a company that inserts orders via this api into our DB, but I'm running into a problem:

Chinese characters don't get stored into my tables, how to fix this?

I have:
- My test file which uses cURL to post set to utf8 via header*
- The API code which inserts the data set to utf8 via header*
- Stored my phpfile as an UTF-8 encoded file
- mysqli connection set to utf8 ($connection->set_charset("utf8"))
- mysql-database set to utf8_general_ci
- mysql-table set to utf8_general_ci
- mysql-columns set to utf8_general_ci
- I insert the values into a VARCHAR and into a TEXT column (for test purposes)
- Inserted the values directly into the query -> no result
- Inserted the values via iconv("ISO-8859-1","UTF-8", $text)-> weird result
- Inserted the values via iconv("UTF-8","UTF-8", $text)-> no result
- Tested characters like áéíúó, which work perfectly fine
- Set my postvalues for curl via foreach($post as $k=>$v){ $data.= $k.'='.$v;} to send as data
- Set my postvalues for curl via http_build_query($post) to send as data
- Used a query before inserted doing SET CHARACTER SET utf8
- Used a query before inserted doing SET NAMES 'utf8'

When I echo the query before inserting it, I see the chinese characters
When I check the database, it's blank values
(not unexpected:) When I Select it and output it, it's blank

*PHP charset header:

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

I'm out of ideas, anyone?

Any changes you if run this before sending anything to the db `SET NAMES 'utf8'`? — Mihai, Feb 27 '15 at 10:26
Can you show use the table definition?I only see charset nothing about collation — Mihai, Feb 27 '15 at 11:00
Please do `SELECT HEX(col), col ...` so we can see a value that is failing to display correctly. There are several possible things going wrong; this will help in focusing us. See also http://mysql.rjweb.org/doc.php/charcoll — Rick James, Feb 28 '15 at 00:23
Had a similar problem, and yes, the characters just went poof without any trace or notice. In my case it turns out that utf-8 really isn't the whole monty. I needed utf8mb - set in column/table, character set, names, etc. So, make sure your kind of utf8 really contains what you are inserting. — Mantriur, Jan 19 '16 at 22:43

score 0 · Answer 1 · answered Feb 27 '15 at 10:38

0

I have same problem and use this query and my problem solved ("SET CHARACTER SET utf8")

$mysqli->query("SET CHARACTER SET utf8")

answered Feb 27 '15 at 10:38

zheek

742
1
11
22

As you can see, I have a `set_charset()` in the list of things I did. I will try this anyway, but I doubt it :) – Martijn Feb 27 '15 at 10:40

score 0 · Answer 2 · answered Nov 26 '19 at 19:57

Verify that the field has utf8_unicode_ci

 CREATE TABLE `table` (
      `id` int(15) NOT NULL,
      `fieldname` varchar(20) NOT NULL,
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

and the charset is utf8

    mysqli_set_charset($con,"utf8");

score 0 · Answer 3 · answered Nov 26 '19 at 20:01

OOP

public function open_connection() {

    $this->connection = mysqli_connect(DB_SERVER, DB_USER, DB_PASS, DB_NAME);
        if(mysqli_connect_errno()) {
            $msg = "Database connection failed: ";
            $msg .= mysqli_connect_error();
            $msg .= " (" . mysqli_connect_errno() . ")";
            exit($msg);
        }
        // Change character set to utf8
        mysqli_set_charset($this->connection,"utf8");

      }

Chinese characters ending up blank in Database

3 Answers3