0

Well, readed tens of threads of insertin JSON to mysql, but not find a solution for this.

SO, i have file that gets JSON over FTP:

    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">

<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
</head>
<?
include ('mysql_php7.php');
mysqli_query ($con,"SET NAMES 'utf8'");


$d = file_get_contents("-FTP ADDRESS-");

echo "<PRE>$d</PRE><br><br>";

mysqli_query($con,"INSERT INTO json (json) VALUES ('$d')") or die(mysqli_error($con)); 



?>

</html>

At his moment, ftp file has succesfully picked up, and used just to test it, the PRE funktion and all showing up nicely, no errors, and all what i need.

Then insertin it to mysql, no problem so far.

But, when checking mysql, there is black ? mark after EVERY Character.

JSON file has also few "ä" character, and mysql insert stop before first of them so it not import full file to mysql.

JSON file is ok, tested it multiple validators in the net, and has good results.

My php file is coded utf w/o BOM with notepad++, ALL mysql tables/columns are utf8 general format, i have headers on php file also to check charset is utf8.

Spended now over 3 days to trying to get this work but cant get it imported to mysql.

And, one thing more. If i manually copy and paste JSON file data to mysql row, its working normally. No ? marks after every character and i can use json_decode after that.

user3530610
  • 13
  • 2
  • 1
    `when checking mysqL`...what tool are you using to check mysql? Just want to rule out an encoding issue in that tool, rather than your PHP code. Also I strongly suggest parameterising your query, then you don't run into issues with escaping quote marks or anything. – ADyson Feb 23 '22 at 11:21
  • 1
    Anyway this is the canonical guide to this sort of thing, for PHP/MySQL apps: [UTF-8 all the way through](https://stackoverflow.com/questions/279170/utf-8-all-the-way-through). Check your setup against all the things in there. – ADyson Feb 23 '22 at 11:22
  • I check mysql data straight from phpmyadmin. – user3530610 Feb 23 '22 at 11:31
  • I have been used that "all way through" guide also. I tryed it when i created new table, and used utf8mb4_general_ci in EVERY possible way in phpmyadmin, in table, all rows and so on... Tested "mysqli_set_charset($con, 'utf8mb4');" line also in my php file, still not work. – user3530610 Feb 23 '22 at 11:33
  • I think you must have missed something, then. It's a very comprehensive guide. Please try also parameterising the input properly though, in case something is getting scrambled due to the raw injection of the data into the SQL string. – ADyson Feb 23 '22 at 11:37
  • I know, that is a good guide. But after my file is in right format, all mysql tables are in the same, and i can print file from ftp correctly in web page, i just cant understand why data is so different in mysql table because there is no any other files or codes between that php file and database. And, im not fully understand what you mean by parameterising the input properly? – user3530610 Feb 23 '22 at 11:43
  • `im not fully understand what you mean by parameterising the input properly`...as in using prepared statements and parameters to inject data into a query, as you should _always_ be doing with any external input, to avoid SQL injection attacks and also to prevent basic syntax errors (e.g. a simple `'` anywhere in your JSON would currently be liable to break your query!). It's a bit worrying if you don't know about this - a step has been missed in your PHP/MySQL education. – ADyson Feb 23 '22 at 11:50
  • http://bobby-tables.com gives an explanation of the risks of SQL injection attacks. https://phpdelusions.net/mysqli contains good examples of writing safe SQL using mysqli. See also the [mysqli documentation](https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php) and this: [How can I prevent SQL injection in PHP?](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) . If you learnt your current technique from a tutorial or book, please don't use it again. – ADyson Feb 23 '22 at 11:50
  • `I check mysql data straight from phpmyadmin`...is it possible that phpMyAdmin isn't set up to show the data with the correct encoding? I mean, I'd be surprised, but it's not impossible. Try downloading the data in a CSV or something, check it's UTF-8 encoded correctly, and view in an editor which supports that correctly (e.g. Notepad++). Or use MySQL Workbench or another tool to connect to the DB and view it. This is just to rule out that actually it's your viewing tool which is the issue, rather than the data itself. – ADyson Feb 23 '22 at 12:32
  • well, founded that the ftp file is ANSI coded, not utf-8. I didnt get it converted utf8, so i tryed "htmlentities($d, ENT_COMPAT,'ISO-8859-1', true);" for file, and then its fully imported to database, but, it adds space between all characters for some reason. S o t h e t e x t i s l i k e t h i s. – user3530610 Feb 23 '22 at 13:06
  • 1
    htmlentities isn't the way to convert an encoding. HTML has nothing to do with the problem. There's no need to HTML-encode the data, and the "encoding" parameter just specifies what text encoding to read the raw data as - it doesn't change the text encoding to anything else. I'm not sure where you got the idea to use that function? I'd expect you can use https://www.php.net/manual/en/function.utf8-encode.php or https://www.php.net/manual/en/function.mb-convert-encoding.php for that purpose. – ADyson Feb 23 '22 at 13:10

0 Answers0