2

I have a script that reads a CSV file and creates a query to insert into MYSQL:

if (($handle = fopen($_FILES['csvfile']['tmp_name'], "r")) !== FALSE) {
    $counter=0;
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) { 


$nullCol = "NULL";


        $sql_q="INSERT INTO $dbupload ($db_cols) VALUES ";

            $sql_q2="('".implode("','",$data)."'),"; 
            $sql_q2=rtrim($sql_q2,",");
            $sql_q2=str_replace('\'\'',$nullCol,$sql_q2);
            $sql_q2=str_replace(' ','',$sql_q2);
            $sql_q2=str_replace('','',$sql_q2); //There is a weird hidden character between ''

            $sql_q.=$sql_q2;
            $sql_q.=" ON DUPLICATE KEY UPDATE ";

            $col_ar=explode(",",$db_cols);

                foreach($col_ar as $col){
                $sql_q.="$col=VALUES($col),";
                }
            $sql_q=rtrim($sql_q,",");
            $insert_data=$connection->new_query($sql_q,true);

    $counter++;
  }     
    fclose($handle);

}

Everything works great, except that on the first value and the last value there is always a character that appears on the query and is invisible to any text editor, only phpmyadmin was able to show: 

'•'

I noticed because my first value is an int and '•10' returned incorrect int value", but my query says '10', on a regular file UTF8 shows as '10' NOTHING!!!! But, If convert it to ANSI I can finally see it as '10' so the weird character is ''

If you look at my code I added a line that has a comment: 

//There is a weird hidden character between ''

I was able to copy/paste in the string replace function the weird, character, and even if it is invisible to the eye, the script was able to detect it and remove it.

My theory is that it comes from the CSV conversion, but how can I solve this issue without using a str_replace() with a hidden copy/paste value.

Thanks

multimediaxp
  • 9,348
  • 13
  • 49
  • 80
  • You can use the `ord` function to view the numeric values of characters. Also, if you have a file, try using a hex editor (or `hexdump`) to view its contents byte-by-byte. – Nate C-K Mar 30 '19 at 03:13
  • 1
    Looks like BOM to me (Byte Order Mark). See if the file was saved with it (notepad++ can tell you this), you can do `preg_replace('/[^[:print:]]/', '', $str)` to remove it. – ArtisticPhoenix Mar 30 '19 at 03:17

0 Answers0