Retrieve Arabic script from mysql 5.7.28

Question

I work on an old website with mysql and php. The database is utf8 and collation latin1_swedish_ci. I changed one table and its columns to utf8_general_ci and was able to put arabic script into the db. Then I added

mysql_query("SET NAMES 'utf8' COLLATE 'utf8_general_ci'");
mysql_query("SET CHARACTER SET utf8");

to the php code.

Now I was able to get some arabic out. But somme characters are missing.

I can't find someone with the same problem and I am at the end of my capabilities.

To answer your questions:

Inside the head I got this:

<meta http-equiv="content-type" content="text/html; charset=utf-8" />

The database holds the right information in the right way: على بعد حوالي 1 كم شمال قبة الهواء، على الضفة الغربية لنهر النيل، صادف العمال جبانة غير معروفة أثناء توسع قرية غرب أسوان النوبية. سُرقت بعض مقابر هذة الجبانة بعد وقت قصير من اكتشافها. حتى أنه في بعض الحالات تم استخدام معدات كبيرة مثل اللوادر لأقتلاع العناصر المعمارية، ومنها على سبيل المثال عتب مدخل من أحدى المقابر. وفي مطلع عام 2013 كانت هناك تقارير متزايدة في الصحافة المصرية والدولية حول اكتشاف هذة المقابر وبداية تدميرها.

This is the same text as above. Only a few arabic characters are weird. Some come out correctly.

So it seems to be happening when I query the database.

if(!@mysql_connect($_SESSION['hostname'],$_SESSION['username'],$_SESSION['password'])){
    echo("<p>Zugangsdaten falsch.xxxx</p>");
  exit();
}
if(!@mysql_select_db($_SESSION['dbname'])){
       echo("<p>Verbindung zum Datenbankserver zur Zeit nicht mÃ¶glich.</p>");
        exit();
}

mysql_query("SET NAMES 'utf8' COLLATE 'utf8_general_ci'");
    mysql_query("SET CHARACTER SET utf8");
    $alle_texte=@mysql_query("SELECT folge, id, bild01, bild02, bild03, bild04, bild05, bild06, bild07, bild08, bild09, bildclass, ".$text." as text, ".$dat_h2.", ".$dat_h3.", ".$dat_h4.", ".$dat_h5.", ".$dat_h6.", link01, link02, link03, link04, link05, link06, link07, link08, link09, link10  FROM ".$db_tabelle." WHERE folge >= ".$folge_min." AND zeigen = '1' AND folge <= ".$folge_max." ORDER BY folge");

BTW, after I put in the "SET NAMES 'utf8' COLLATE 'utf8_general_ci'" I got rid of the "????" for all the arabic characters. This problem seems to be qell documented. But I only got half way to the solution, it seems.

Could it be that you don't have your META properly set on your HTML page? — Carlos Alves Jorge, Jun 21 '20 at 19:57
Yes as @CarlosAlvesJorge mentioned you might be having encoding issue if you are passing data to PHP and displaying in HTML view. You can try connecting to Database via any Mysql Client and check for the sane data. — Vijay, Jun 21 '20 at 20:11
Does this answer your question? [UTF-8 all the way through](https://stackoverflow.com/questions/279170/utf-8-all-the-way-through) — Dharman, Jun 21 '20 at 20:15
Is this what is actually stored in the database? It looks as if some bytes of the UTF-8 encoded string had been replaced by " and ' characters. — Joni, Jun 21 '20 at 22:21
Why is one sample displayed left-to-right, and the other is right-to-left? — Rick James, Jun 25 '20 at 03:26
Do not use the `mysql_*` interface of PHP. Migrate to `mysqli_*` or `PDO`. — Rick James, Jun 25 '20 at 03:28

score 1 · Answer 1 · edited Jun 25 '20 at 12:25

The database is utf8 and collation latin1_swedish_ci. I changed one table and its columns to utf8_general_ci and was able to put arabic script into the db. Then I added

Some of that is relevant, but there are missing pieces. Please provide

SHOW CREATE TABLE before the change.
The SQL statement(s) you used to make the change.
SHOW CREATE TABLE after the change.

The settings on the database are not relevant.

Also, please provide this for a small amount of Arabic text:

SELECT col, HEX(col) FROM ... WHERE ...

For example, if the column has على بعد حوالي, the HEX will say

D8B9 D984 D989 D8A8 D8B9 D8AF D8AD D988 D8A7 D984 D98A

(I added the spacing to emphasize that Arabic is D8xx or D9xx -- 2 bytes per Arabic character.)

If, instead, the hex is something like

C398 C2B9 C399 E2809 EC399 E280B0 ...

Then the data it the table is already messed up.

Since your output has some Arabic and some black diamonds, I suspect things are even worse off. I discuss black diamonds in Trouble with UTF-8 characters; what I see is not what I stored , but your use case may be worse than the cases that it handles.

Retrieve Arabic script from mysql 5.7.28

1 Answers1