0

I am having a problem on what I think is a MySQL issue, when I trying fetch a row my the database(mysql) with a column value of ( हिंदी 한국어 اللغة Ø§Ù„Ø¹Ø±Ø¨ÙŠØ ), the result row seems exists and truthy on if condition statements. But when I var_dump it, it show empty, not null.

for example: (php/mysql)

Input Data:

  • id -> 1
  • name -> Bob
  • address -> हिंदी 한국어 اللغة العربية

Stored Data on Table

  • [column]id = 1
  • [column]name = "Bob"
  • [column]address = "हिंदी 한국어 اللغة العربيØ"

Expected Data after being fetched from mysql

  • id -> "1"
  • name -> "Bob"
  • address -> \u00e0\u00a4\u00b9\u00e0\u00a4\u00bf\u00e0\u00a4\u201a\u00e0\u00a4\u00a6\u00e0\u00a5\u20ac \u00ed\u2022\u0153\u00ea\u00b5\u00ad\u00ec\u2013\u00b4 \u00d8\u00a7\u00d9\u201e\u00d9\u201e\u00d8\u00ba\u00d8\u00a9 \u00d8\u00a7\u00d9\u201e\u00d8\u00b9\u00d8\u00b1\u00d8\u00a8\u00d9\u0160\u00d8

$mysql_result_row = array(
    "id" => 1, 
    "name" => "Bob",
    "address" => );

echo json_encode($mysql_result_row) // (shows nothing)
echo gettype($mysql_result_row) // array
echo $mysql_result_row['name'] // Bob
echo $mysql_result_row['address'] // (everything stops working, no php nor mysql error logs)

Notes

  1. The sample above have 3 different set of language / character set.
  2. हिंदी is Hindi
  3. 한국어 is Korean
  4. اللغة Ø§Ù„Ø¹Ø±Ø¨ÙŠØ is Arabic
  5. Don't ask why a single column has 3 different language because I do not know
  6. If only one of those languages on that address column, it works, no problem. It only stops working if those 3 combined
Ray Romero
  • 1
  • 1
  • It seems to work here http://sandbox.onlinephpfunctions.com/code/d1a3f58958240af1e1f017a3470a83514bd7347b – Jay Blanchard Jan 28 '20 at 15:23
  • @Ray Romero welcome to SO, what is the collation you are using in the database for the particular field type – Boopathi D Jan 28 '20 at 15:29
  • @DroidDev used to be utf8_general_ci then I convert it to utf8_unicode_ci, still the same. – Ray Romero Jan 28 '20 at 15:38
  • @JayBlanchard the $mysql_result_row should come from mysql query using `select ...` – Ray Romero Jan 28 '20 at 15:38
  • It does, doesn't it? You performed a select and an array was returned. – Jay Blanchard Jan 28 '20 at 15:39
  • what is the mysql version you are using? – Boopathi D Jan 28 '20 at 15:40
  • @JayBlanchard yes, my sample maybe confusing, but in my understanding assuming the database column has a value of stated above, the result value from query would be like this ( \u00e0\u00a4\u00b9\u00e0\u00a4\u00bf\u00e0\u00a4\u201a\u00e0\u00a4\u00a6\u00e0\u00a5\u20ac \u00ed\u2022\u0153\u00ea\u00b5\u00ad\u00ec\u2013\u00b4 \u00d8\u00a7\u00d9\u201e\u00d9\u201e\u00d8\u00ba\u00d8\u00a9 \u00d8\u00a7\u00d9\u201e\u00d8\u00b9\u00d8\u00b1\u00d8\u00a8\u00d9\u0160\u00d8 ). But since that issue I am experiencing I am unable to verify that – Ray Romero Jan 28 '20 at 15:50
  • @DroidDev Database client version: libmysql - mysqlnd 5.0.11-dev - 20120503 – Ray Romero Jan 28 '20 at 15:51
  • In the example I provided the data is returned as described, so I am under where your issue it. – Jay Blanchard Jan 28 '20 at 15:51
  • @RayRomero could you please post the insert data here? – Boopathi D Jan 28 '20 at 16:35
  • It's Mojibake for `हिंदी 한국어 اللغة العربي' , with the Korean coming first. See "Mojibake" in https://stackoverflow.com/questions/38363566/trouble-with-utf8-characters-what-i-see-is-not-what-i-stored We'll need to see the HEX to determine whether it might be "double encoding" instead. – Rick James Jan 29 '20 at 04:48
  • @DroidDev updated the original post to include the input data, stored data on the mysql db, which I believe is the correct values. The issue is when fetching the data from the database, the php variable that is holding row becomes shady, then echoing/assigning the values of id and name has no issue, but echoing/assigning the value of address, then php server breaks with no error logs. Is it likely a mysql version or/and configuration? that the mysql is not encoding properly if multiple language on a single column/string? – Ray Romero Jan 29 '20 at 09:20
  • @RayRomero Please ckeck the collation as `utf8_general_ci` for the column you are storing the language data. Also add the `` in `` of your html. Finally add **charset=utf8** in your PDO connection as `$conn = new PDO("mysql:host=$servername;dbname=test;charset=utf8", $username, $password);`. Hope you will get resolved by this : ) – Boopathi D Jan 29 '20 at 10:08
  • @DroidDev Already tried them except the – Ray Romero Jan 29 '20 at 10:22
  • @RayRomero did you add charset=utf8 in db connection? – Boopathi D Jan 29 '20 at 10:25
  • yes, already did that, and it is utf8 by default. i also tried changing the table collation on different types – Ray Romero Jan 29 '20 at 11:48

1 Answers1

0

Finally found a work around from this reference: [https://dev.mysql.com/doc/refman/8.0/en/charset-connection.html][1]

Solutions:

  • Prepend an N on values involving the non-english values. Example on MySQL INSERT/UPDATE: "INSERT INTO ... () VALUES('$id', '$name', N'$address')"
  • Before querying the INSERT/UPDATE/SELECT query, I first query this: "SET CHARACTER SET utf8"

Although the problem on the '$mysql_result_row' as stated on the question is still a mystery, it is still like partially corrupted variable because it is holding a character it cannot handle properly.

Ray Romero
  • 1
  • 1