FGETCSV import fails. HOW to remove or supress single quotes in incoming data text?

[kthibeault1965]

😕

My data contains single quotes ' in the midst of text fields to be imported.

Fgetcsv is successfully stripping dual double quotes, but the import fails when it encounters a field with just one single quote.

as you can see, i have tried some tricks to remove the pesky characters with no luck, even using an if statement to set myself up to do something cool. I just dont know what that is. 🙁

how do I EASILY remove or suppress them please??

	$handle = fopen("ds/tb_product.csv", "r");
	while (($data = fgetcsv($handle, 10000, ",")) !== FALSE)
     {
	     //prod_long_description add back into list after debugging after prod_short_description
	    // $fixdesc = str_replace($singlequote, $space, $test);
	    $quotepos = strpos($data[20], $singlequote);
	    if ($quotepos != 0) {
       $import="INSERT into derm_product(id, prod_name,prod_brand_name,prod_upc,prod_size,prod_measure,prod_color,prod_stock,prod_ordered,
       prod_keywords,prod_short_description,prod_long_description,prod_direction,prod_ingredients_active,prod_ingredients_inactive,
       prod_price,prod_msrp_price,prod_ds_brand_id,prod_url) 
	   values('$data[0]','$data[1]','$data[2]','$data[4]','$data[6]','$data[7]','$data[8]','$data[15]',
	   '$data[16]','$data[18]','$data[19]','$data[20]','$data[21]','$data[22]','$data[23]','$data[25]','$data[27]', 
	   '$data[28]','$data[29]')";
	   echo $fixdesc."<br>";
	   echo "|".$singlequote."|<br>";
	   echo "|".$space."|<br>";
	   $fixdesc = str_replace($singlequote, $space, $data[20]);
	  mysql_query($import) or die("Insert of derm product list to table derm_product failed: ".mysql_error());
	    }
      }    

     fclose($handle);

[rulian]

I've never used fgetcsv before,
but it seems that you are not fixing the variable $import before running the query, if yoru data array contains single quotes, then you should filter then before you place them into your sql. otherwise, you'd be replacing the single quotes from the sql as well.
You can use str_replace like you are doing, or preg_replace if you are familiar with regEx.

I suggest you read the line, apply your filters then explode it into an array to use in your sql, that way you can apply your filters universally

[kthibeault1965]

Okay, it isnt pretty, but it works - I had to fix the data variable coming through the while array loop in line, even tho it echos to the screen as

mysql insert errors occured when it tried to read the following text field:

'Look there, it's Mom's kids' - choking on the single quotes

MY php solution involved replacing ' with \' so that the variable would read

Mom\'s kids
however - it actually shows up in the database as
Mom's kids - THE CORRECT WAY.🙂

$handle = fopen("ds/tb_product.csv", "r");
	while (($data = fgetcsv($handle, 10000, ",")) !== FALSE)
     {
	     //prod_long_description add back into list after debugging after prod_short_description
	    // $fixdesc = str_replace($singlequote, $space, $test);
	    $fixname = str_replace($singlequote, "$slash", $data[1]);
	    $fixbrand = str_replace($singlequote, "$slash", $data[2]);
	    $fixcol = str_replace($singlequote, "$slash", $data[8]);
	    $fixkey = str_replace($singlequote, "$slash", $data[18]);
	    $fixldesc = str_replace($singlequote, "$slash", $data[20]);
	    $fixsdesc = str_replace($singlequote, "$slash", $data[19]);
	    $fixdir = str_replace($singlequote, "$slash", $data[21]);
	    $fixinga = str_replace($singlequote, "$slash", $data[22]);
	    $fixingi = str_replace($singlequote, "$slash", $data[23]);
	    $import="INSERT into derm_product(id, prod_name,prod_brand_name,prod_upc,prod_size,prod_measure,prod_color,prod_stock,prod_ordered,
       prod_keywords,prod_short_description,prod_long_description,prod_direction,prod_ingredients_active,prod_ingredients_inactive,
       prod_price,prod_msrp_price,prod_ds_brand_id,prod_url) 
	   values('$data[0]','$fixname','$fixbrand','$data[4]','$data[6]','$data[7]','$fixcol','$data[15]',
	   '$data[16]','$fixkey','$fixsdesc','$fixldesc','$fixdir','$fixinga','$fixingi','$data[25]','$data[27]', 
	   '$data[28]','$data[29]')";
	   //echo $fixldesc."<br>";
	  mysql_query($import) or die("Insert of derm product list to table derm_product failed: ".mysql_error());
	    }    

     fclose($handle);

[rincewind456]

A better way would be to use the reccommended method of [man]mysql_real_escape_string/man as this would also prevent the query failing when you come across another character that needs escaping, as well as against injection attacks.

Something like this

$import="INSERT into derm_product(id, prod_name,prod_brand_name,prod_upc,prod_size,prod_measure,prod_color,prod_stock,prod_ordered, 
       prod_keywords,prod_short_description,prod_long_description,prod_direction,prod_ingredients_active,prod_ingredients_inactive, 
       prod_price,prod_msrp_price,prod_ds_brand_id,prod_url) 
       values('
       ".mysql_real_escape_string($data[0])."',
       '".mysql_real_escape_string($data[1]."',
       '".mysql_real_escape_string($data[2]."',
       '".mysql_real_escape_string($data[4]."',
       '".mysql_real_escape_string($data[6]."',
       '".mysql_real_escape_string($data[7]."',
       '".mysql_real_escape_string($data[8]."',
       '".mysql_real_escape_string($data[15]."', 
       '".mysql_real_escape_string($data[16]."',
       '".mysql_real_escape_string($data[18]."',
       '".mysql_real_escape_string($data[19]."',
       '".mysql_real_escape_string($data[20]."',
       '".mysql_real_escape_string($data[21]."',
       '".mysql_real_escape_string($data[22]."',
       '".mysql_real_escape_string($data[23]."',
       '".mysql_real_escape_string($data[25]."',
       '".mysql_real_escape_string($data[27]."', 
       '".mysql_real_escape_string($data[28]."',
       '".mysql_real_escape_string($data[29].')";