[SOLVED] Need Help With fgetcsv

[dlebowski]

Below is my code for fgetcsv.  If a column in my import contains an apostrophe ' , then it errors out.  It breaks my column separation.  Does anyone know how I could remove apostrophes prior to being imported or can I some how write the query to ignore the apostrophe.  Thanks in advance!

 

if(isset($_POST['submit3']))
   {
     $fileupload=$_POST['fileupload'];
     $filename="$fileupload";
     $auctiondate=$_POST['auctiondate'];
     $handle = fopen("$filename", "r");

while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)
     {
       $column1=$_POST['column1'];
       $column2=$_POST['column2'];
       $column3=$_POST['column3'];
       $column4=$_POST['column4'];
       $column5=$_POST['column5'];
       $column6="$auctiondate";
       $onlineonsite=ONSITE;
       $lotpaymentexempt=NO;
       
       $import="INSERT into test(LotID,LotNumber,LotTitle,SellingPrice,Buyer,LotPaymentExempt,OnlineOnsite,AbsenteeBid,SellerNumber,LotAuctionDate) values('', '$data[$column1]','$data[$column2]', '$data[$column3]','', '$lotpaymentexempt', '$onlineonsite', '$data[$column4]', '$data[$column5]', '$column6')";
       mysql_query($import) or die(mysql_error());
     }
     fclose($handle);

[php_tom]

Try this:

$column1=addslashes($_POST['column1']);
$column2=addslashes($_POST['column2']);
$column3=addslashes($_POST['column3']);
$column4=addslashes($_POST['column4']);
$column5=addslashes($_POST['column5']);

 

Hope that helps.

[dlebowski]

Thanks for the quick reply.  It still isn't getting past that.  It is breaking on this line if I manually remove the apostrophe in "Torch's" prior to the import, it works:

 

1, "Snap-On Torch's Bit Sockets", 8 Pieces, 2, "0000-00-00"

[php_tom]

Not sure what to say... if you call addslashes() on all the input you're goin to put into the query, it shouldn't complain... can you make the script echo the INSERT SQL statement before trying to do it(e.g., echo $import;), the post what it echoed?

[dlebowski]

1, "Snap-On Torch's Bit Sockets", 8 Pieces, 2, "0000-00-00"

 

What is happening is when it gets to the line above, it sees the ' in Torch's as an enclosure and it then creates an extra column. 

[php_tom]

Right and if you call addslashes on it, this will become "... Torch\'s ..." which will fix your problem. And you can always do stripslashes again when you SELECT from the table...

[dlebowski]

Is there anything I can run that will strip the apostrophe?  I don't want them in there anyway.

[dlebowski]

Strip slashes will only remove them when I call the data again using a SELECT query correct?  What about just removing any apostrophe at all from my import before it goes in.  If that is possible, that would probably work for me.

[php_tom]

Sure, you could do that... you could use regex functions, but I don't like regex. Try this instead:

str_replace("'", "", $theText);

 

Hope that helps.

[dlebowski]

I was looking at this earlier, but I'm not sure where to put that in my code.  I will give it a whirl.

 

Ryan

[Hypnos]

<?php
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
       $column1=$_POST['column1'];
       $column2=$_POST['column2'];
       $column3=$_POST['column3'];
       $column4=$_POST['column4'];
       $column5=$_POST['column5'];
       $column6="$auctiondate";
       $onlineonsite=ONSITE;
       $lotpaymentexempt=NO;
       
       $import="INSERT into test(LotID,LotNumber,LotTitle,SellingPrice,Buyer,LotPaymentExempt,OnlineOnsite,AbsenteeBid,SellerNumber,LotAuctionDate)";
       $import .=" values('', '" . cleancvs($data[$column1]) . "','" .  cleancvs($data[$column2]) . "',"; 
       $import .= " '" . cleancvs($data[$column3]) . "','', '$lotpaymentexempt', '$onlineonsite', '" . cleancvs($data[$column4]) . "', '" . cleancvs($data[$column5]) . "', '$column6')";
       mysql_query($import) or die(mysql_error());
}
fclose($handle);

function cleancvs($input) {
return(str_replace("'", '', $input));
//OR
//	return(mysql_real_escape_string($input));
}

 

Replace str_replace with mysql_real_escape_string if you want to keep the single quotes.

 

The issue here is not the CVS. It's the SQL. And the slashes added from addslashes or mysql_real_escape_string will not be inputted. That's just a way of telling MySQL that they are apart of the input (and you aren't trying to quote a string). You do NOT need to use stripslashes on output.

[dlebowski]

I went ahead and implemented the changes.  It doesn't error out now, but nothing imports.  I will keep playing with the code.  Thanks for helping me out.

[Hypnos]

I went ahead and implemented the changes.  It doesn't error out now, but nothing imports.  I will keep playing with the code.  Thanks for helping me out.

You may want to try echoing $import before it hits mysql_query. It's probably another problem with the SQL statement.

[dlebowski]

I fixed it.  Actually it appears to work perfect.  Thanks to both of you for helping me through this. 

 

Ryan