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 · Accepted Answer · 2015-11-13 08:27:50Z

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, 2015 at 8:27

answered Nov 13, 2015 at 6:49

Zeff Tang

919 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

NubeToAdroit Over a year ago

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?

Zeff Tang Over a year ago

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.

Isaac Bennetch Over a year ago

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.

Community · Accepted Answer · 2017-05-23 12:15:10Z

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.

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related