-1

I'm trying to import a product csv using PHP to MySQL. When i try to import I got strange characters which also not add properly to database table.

Product Name on CSV File

Alva Cook'on Braadpan Lavendelblauw ovaal 24 cm – 4,2 Liter

Product name i got when import function execute

Alva Cook'on Braadpan Lavendelblauw ovaal 24 cm – 4,2 Liter

my code is like

while (($emapData = fgetcsv($filepath, 10000, ",")) !== FALSE)
{
    $pd_name=  ($emapData[1]);
}

i used this function as a solution this shows product name correctly on import page

$pd_name=mb_convert_encoding(($emapData[1]), 'UCS-2LE', 'UTF-8');

Output ~ Alva Cook'on Braadpan Lavendelblauw ovaal 24 cm – 4,2 Liter

But when i use mysql_real_escape_string as below for insert the name to db table, it added lots of slashes and not work properly.

$pd_name=mysql_real_escape_string(mb_convert_encoding(($emapData[1]), 'UCS-2LE', 'UTF-8'));


Output ~ A\0l\0v\0a\0 \0C\0o\0o\0k\0\'\0o\0n\0 \0B\0r\0a\0a\0d\0p\0a\0n\0 \0L\0a\0v\0e\0n\0d\0e\0l\0b\0l\0a\0u\0w\0 \0o\0v\0a\0a\0l\0 \02\04\0 \0c\0m\0 \0 \04\0,\02\0 \0L\0i\0t\0e\0r\0

SQL database table supports UTF and when we add products normally using form we don't face this problem, anyone know how to fix this issue. Thank You

Kamlesh Gupta
  • 505
  • 3
  • 17
Suneth Kalhara
  • 1,116
  • 4
  • 16
  • 40
  • use utf8_encode(); hope it will work – Kamlesh Gupta Sep 01 '16 at 17:19
  • You need to use the same charset throughout your ENTIRE rendering chain. PHP itself doesn't about the charset of a string. A string is a string, containing a bunch of bytes. What those bytes represent is irrelevant to PHP Itself. It's when you start processing that string that the actual representation matters. If you convert to utf-8 as you're doing, but you haven't told the output/display environment to expect UTF, then you will get "mangled" characters. – Marc B Sep 01 '16 at 17:20
  • @KamleshGupta it doesn't work mate – Suneth Kalhara Sep 01 '16 at 17:27
  • @abra I'm not sure why this is duplicate, becoz i can show the output on site correctly but i cannot save this using my sql, that question doesn't have correct answer – Suneth Kalhara Sep 01 '16 at 17:31
  • @MarcB do you know any alternative for mysql_real_escape_string(mb_convert_encoding(($emapData[1]), 'UCS-2LE', 'UTF-8')); please, becoz without mysql_real_escape_string it works good but i need to add mysql_real_escape_string for escape ' chars – Suneth Kalhara Sep 01 '16 at 17:33
  • actually in your string contain some garbage character which is not support sql server...if you remove or replace with space so may be it work – Kamlesh Gupta Sep 01 '16 at 17:34
  • – seems have issue i'll try to use - instead of that – Suneth Kalhara Sep 01 '16 at 17:39
  • it worked after reverse functions $pd_name=mb_convert_encoding(mysql_real_escape_string($emapData[1]), 'UCS-2LE', 'UTF-8'); also the long dash – sign was a garbage character, we used - instead of that. other other lathin and German characters worked fine after above fix, hope this comment helpful for someone since i cannot post this as an answer because question marked as duplicated – Suneth Kalhara Sep 11 '16 at 12:42

1 Answers1

-1

You should start with the fact that the function mysql_real_escape_string has been deprecated:

Warning This extension was deprecated in PHP 5.5.0, and it was removed in PHP 7.0.0. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include: mysqli_real_escape_string() PDO::quote()

Then you should use mysqli_real_escape string in combination with mysqli_set_charset

Oficial PHP Documentation:

Rodrigo Mata
  • 1,779
  • 2
  • 14
  • 23
  • Site have hundreds of mysql functions. so I'm don't have time to find and convert those all to mysqli, we will consider about later, anyway will this solve issue ? – Suneth Kalhara Sep 01 '16 at 17:26