-1

I have a problem for days with MySQL and PHP.

I load data from a CSV and write it to a database. Unfortunately, umlauts are not displayed correctly, so a ü is written to the database as u00fc, for example.

I use MySQL because it can not be otherwise, after establishing the MySQL connection, I execute the following commands

mysql_set_charset('UTF8', $newsystem);
mysql_query("SET CHARACTER SET 'UTF8'", $newsystem);

The collation is set to utf8_general_ci.

To enter the data, the data is converted using json_encode.

The array is created as follows:

$jsonOrderDaten = array('id' => $daten[11], 'bezeichnung' => $daten[12], 'stueckzahl' =>$daten[14], 'preis' => $daten[15], 'mwst_satz' => $daten[16]);

Und der MySQL Eintrag erfolgt so:

$insertquery = "INSERT INTO `e_paket` (`paket_id`, `ebay_verkaufsnr`, `adress_daten`, `bestell_daten`, `ebay_order`, `status`) VALUES ('$packid', '$ebayorderid', '$jsonKD', '$jsonOD', 1, 0)";

mysql_query($insertquery ,$newsystem);

Can someone help me with my problem?

Best regards, Pascal

Pascal
  • 1
  • 1
  • 2
    mysql_* functions are deprecated you should be using mysqli_* functions or PDO instead. And you have SQL injection in your code https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1.. – Raymond Nijland Feb 21 '18 at 12:00
  • is the character encoding of your file utf8? you can find it using an advanced editor such as notepad++ or sublime text. check it out, probably it has a different character set and that's why, when mysql converts it in utf8, you get the code. – senape Feb 21 '18 at 12:01

3 Answers3

0

The problem here is the JSON encoding. All special unicode characters get escaped (e.g. german umlauts).

To fix this you could set the JSON_UNESCAPED_UNICODE flag on the encode method like this:

$jsonOD = json_encode($jsonOrderDaten ,JSON_UNESCAPED_UNICODE);

Please note that you should watch out for SQL-Injection!

Manuel Otto
  • 6,410
  • 1
  • 18
  • 25
0

I think using "LOAD DATA LOCAL INFILE" will solve your problem. It is more faster than normal insertion. Please https://dev.mysql.com/doc/refman/5.7/en/load-data.html for more details.

sarath s rajendran
  • 398
  • 4
  • 19
0

You could also do mysql_query ("SET NAMES UTF8");

Also, not only the connection has character encoding, but also the database, the table and each column have their own default encoding. Check them if they are set to something different.

Mysqli, injection, prepared_statements, the others have already commented on them.

kry
  • 362
  • 3
  • 13