Mysql dealing with non-English char input, fine with phpMyAdmin command box, not via PHP

Question

My database deals with Chinese and Japanese characters.

When I insert rows on the phpMyAdmin command box, it works beautifully.

But problem occurs when I set up a query input on my website, the query is fetched by a php file, like:

    $query = $_POST['query'];
    $result = $dbc->query($query);

The non-English characters just become rubbish in database, like

  ID      column1
  1666    ä½ å¥½å•Šå•Šå•Š

I checked that the php file receives the characters fine, the problem should come from Mysql. All charset is utf-8.

I am new to mysql, please let me know if you may need more info.

Thank you in advance.

Zeff Tang · Answer 1 · 2015-11-13T08:27:50.487

0

column's collation set to utf8_general_ci

More information : https://dev.mysql.com/doc/refman/5.7/en/charset-applications.html

Add this line into your config.inc.php in PHPMyAdmin

$cfg['DefaultCharset'] = 'utf8';
$cfg['DefaultConnectionCollation'] = 'utf8_general_ci';

edited Nov 13 '15 at 08:27

answered Nov 13 '15 at 06:49

Zeff Tang

91
9

All column's collations are utf8-general-ci. But Server connection collation is utf8mb4-unicode-ci, and I can't seem to change it, could it be the problem? – NubeToAdroit Nov 13 '15 at 07:39
what's your query when you var dump it in php? if the value that se into query are correct, you may try insert few line into your config.inc.php in PHPMyAdmin. find on the editted answer. – Zeff Tang Nov 13 '15 at 08:27
Hi Zeff Tang, the DefaultCharset option was removed in 2010 and is no longer used, and DefaultConnectionCollation is set to that value by default. Aside from that, the poster reports that phpMyAdmin is fine but his/her application is having trouble, so this wouldn't help the application anyway. – Isaac Bennetch Nov 24 '15 at 21:32

score 0 · Answer 2 · edited May 23 '17 at 12:15

Since phpMyAdmin works expected, it sounds like your application is at fault here.

Perhaps you could clarify what you mean when you wrote "php file receives the characters fine" -- how did you determine that? The way I read it, you can create the proper characters from your application and they're displayed correctly in the database through phpMyAdmin, but when you try to display it through your application it shows gibberish. In that case it seems the portion of the application that retrieves and displays the data is at fault.

Regardless, there are many answers here about having gibberish characters, the best place to start is UTF-8 all the way through and you could also read more at Special characters in PHP / MySQL or Problem with PHP and Mysql UTF-8 (Special Character). Finally, even thugh it's off the Stackoverflow network, the phpMyAdmin wiki covers this pretty well also.

As a hint, most of the time I see this issue it's caused by not issuing SET NAMES (or using an equivalent function) when setting up the MySQL connection from your PHP script.

Mysql dealing with non-English char input, fine with phpMyAdmin command box, not via PHP

2 Answers2