1

I can insert arabic arabic/hebrew/chinese on my local machine which is: Windows 10 / xampp / PHP 5.6.23 / MySQL 5.7.19

But not on Prod server: Ubuntu 16.04 LTS / nginx / PHP 7.0.22 / MySQL 5.7.20

The collation of the table's field to insert text in is utf8_unicode_ci.

The PHP code to insert in database is just simple:

$sql = $dataBase->prepare('INSERT INTO sa__user_desc(user_id,
                                                     lang,
                                                     description)
                                           VALUES(:user_id,
                                                  :lang,
                                                  :desc)');
$sql->execute(array('user_id' => $theId,
                    'lang'    => 'ar',
                    'desc'    => $value));
$sql->closeCursor();

That's strange because, on the server it's not inserting anything, absolutely no new lines when trying to insert one of the mentioned languages above. But working and inserting new lines when inserting text in English or Deutsch or French for example.

And there is no errors on log file /var/log/nginx/mywebsite.com-error.log ! So that's really strange!

UPDATE

It's working when inserting Chinese characters using phpMyAdmin. So it's not the MySQL database, issue might come from PHP.

SOLUTION

It was a PHP issue: if(str_word_count($value) != 0) { returns 0 when $value contains some Arabic text for PHP 7.0.22 and not 0 for my local install with PHP 5.6.23.

So I changed it to if($value != '') {.

I know I should have the same version of PHP on both DEV and PROD environments but that means I have to take some time to check how to upgrade it on xampp. :P

London Smith
  • 1,622
  • 2
  • 18
  • 39
  • Might be https://dev.mysql.com/doc/refman/5.7/en/charset-server.html ? – Taha Paksu Nov 10 '17 at 12:48
  • Or this: http://php.net/manual/tr/mysqli.set-charset.php – Taha Paksu Nov 10 '17 at 12:49
  • @TahaPaksu, yes but the PHP PDO function to connect to the database has charset=utf8 in it : `$dataBase = new PDO('mysql:host='.$config['DatabaseDomain'].';dbname='.$config['DatabaseName'].';charset=utf8', $config['DatabaseUser'], $config['DatabasePassword']);` – London Smith Nov 10 '17 at 13:05
  • It should be noted that there is no consistent definition of "word length" or "letter length" in unicode, only byte length. Characters generally correspond to letter/word length for latin script, but not necessarily arabic, indian, chinese scripts, etc... So `str_word_count` is a shifty function to use if you're not certain of the end users language. – sapht Nov 10 '17 at 17:21

1 Answers1

1

(This does not necessarily explain the difference in the two servers, but...)

For Chinese, you really need utf8mb4; a number of Chinese characters (the 4-byte ones) are missing from MySQL's utf8. (English, German, French, Arabic, and Hebrew all work in utf8.)

Since you are succeeding with the characters found in latin1, I suspect there is a setting that is different.

See "Best practice" in Trouble with UTF-8 characters; what I see is not what I stored . And, if you get some other symptom, that link may help with it.

Rick James
  • 135,179
  • 13
  • 127
  • 222