str_replace help

[jjk-duffy]

I am importing a CSV file into a mysql db.  I want to replace a character ( ’ )  that sometimes appears in the description column ($col3) with ( ' ).

 

here is my code: 

 

if($import_file){
	$handle = fopen($admin_folder.'csv\\'.$file, "r");
	$count = 0;
	while(($data = fgetcsv($handle, 1000, ",")) !== false){
	        $count = $count + 1;
		if($count != 1){
			list($col1, $col2, $col3, $col4) = $data;
			$col1 = mysql_real_escape_string($col1);
			$col2 = mysql_real_escape_string($col2);
			$col3 = mysql_real_escape_string($col3);
			$col3 = str_replace("’","'",$col3);
			$col4 = mysql_real_escape_string($col4);
			if($col1 != ''){
   				         $sql="INSERT INTO auction_items SET lot='$col1', item='$col2', description='$col3', notes='$col4', auction_id='$aid'";
				         mysql_query($sql);
			}
		}
	}
fclose($handle);
} //end upload of csv data into table

 

I am new to the PHP world and just can't seem to figure out why it won't work.

 

I also tried replacing the offending character when I output the data, but it does not work there either. 

[jjk-duffy]

Forgot to provide an example of the offending text:

 

Altec AT200-AV, 35’ Telescopic Non-Insulated Single-Man Bucket, s/n _____________, mtd behind cab on 2000 Ford E350 Cargo Van, 8 cyl, Auto, A/C (Exempt From Odometer Disclosure)

 

[-Karl-]

$replace = array("’" => '"");
$col3 = str_replace($replace,$col3);

 

Try that.

[jjk-duffy]

$replace = array("’" => '"");
$col3 = str_replace($replace,$col3);

 

Try that.

 

When I add that code, nothing is imported into the description field.  I did change make one change as you had the single and double quotes switched. 

$replace = array("’" => "'");

[ChemicalBliss]

You need to sanitize the string for mysql -after- you replace the offending characters, you can do both on one line:

 

$col3 = mysql_real_escape_string(str_replace("’","'",$col3));

 

-cb-

[jjk-duffy]

You need to sanitize the string for mysql -after- you replace the offending characters, you can do both on one line:

 

$col3 = mysql_real_escape_string(str_replace("’","'",$col3));

 

-cb-

 

Updated the code as suggested and this does not catch the offending character.

 

What is really getting me is that the following will work:

$s = "Altec AT200-AV, 35’ Telescopic Non-Insulated Single-Man Bucket, s/n _____________, mtd behind cab on 1996 Ford E350 Cargo Van, 8 cyl, Auto, A/C (Exempt From Odometer Disclosure)";
$s = str_replace("’","'", $s);
echo ('new string is:  '.$s.'<br/>');

but if I get the text via the file upload or from query of the db, it will not replace the text.

 

[oni-kun]

but if I get the text via the file upload or from query of the db, it will not replace the text.

 

The quote is most likely encoded as an HTML entity and is not caught by the str_replace call. Use var_dump on the output of the file or query and make sure the quote is indeed what you think it is, it is always useful to debug and check inputs before assuming a function does not work.

[jjk-duffy]

but if I get the text via the file upload or from query of the db, it will not replace the text.

 

The quote is most likely encoded as an HTML entity and is not caught by the str_replace call. Use var_dump on the output of the file or query and make sure the quote is indeed what you think it is, it is always useful to debug and check inputs before assuming a function does not work.

OK, ran var_dump and it yields:  string(177) "Altec AT200-AV, 35� Telescopic Non-Insulated Single-Man Bucket, s/n _____________, mtd behind cab on 2000 Ford E350 Cargo Van, 8 cyl, Auto, A/C (Exempt From Odometer Disclosure)"

 

The output is consistent with what is being displayed on the website and the " � " is what I am trying to get rid of.

 

Is this what you were wanting me to determine by running var_dump?

 

Thanks

[oni-kun]

I meant on the input, such as you queries. Notice you are using mysql_real_escape_string prior to replacing, This will not fix the problem with your single quote in col3 and is most likely breaking the SQL statement.

[jjk-duffy]

I meant on the input, such as you queries. Notice you are using mysql_real_escape_string prior to replacing, This will not fix the problem with your single quote in col3 and is most likely breaking the SQL statement.

 

Here is the current version of the code based on the prior input.  I put the var_dump inside the while loop so I could verify what was being placed into the variable.  I also added a $stop variable that further on stops the execution of additional code and allows me to see what is being outputted. 

 

I believe that I am executing the mysql_real_escape_string and str_replace in the proper order. 

 

I have only been working with PHP for a couple of months and I really do appreciate your help.  Sorry if I am not completely grasping what you are telling me, it might take me a bit for the "light to come on"

 

Do find any other issues with the code that are preventing the desired outcome.

i

f($import_file){
					$handle = fopen($admin_folder.'csv\\'.$file, "r");
					$count = 0;
					$stop = 'ABC';
					while(($data = fgetcsv($handle, 1000, ",")) !== false){
						$count = $count + 1;
						if($count != 1){
							list($col1, $col2, $col3, $col4) = $data;
							var_dump($col3);
							$col1 = mysql_real_escape_string($col1);
							$col2 = mysql_real_escape_string($col2);
							//$col3 = mysql_real_escape_string(str_replace("’","'",$col3));
							$find = array("’");
							$replace = array("'");
							$col3 = str_replace($find,$replace,$col3);
							$col3 = mysql_real_escape_string($col3);
							$col4 = mysql_real_escape_string($col4);
							if($col1 != ''){
								$sql="INSERT INTO auction_items SET lot='$col1', item='$col2', description='$col3', notes='$col4', auction_id='$aid'";
								mysql_query($sql);
							}
						}
					}
					fclose($handle);
				} //end upload of csv data into table

[ChemicalBliss]

Those 4 lines do exactly he same as my one line, jsut to let you know.

 

It may be to do with encoding, why do you want this removed? is it special characters you want removed?

Perhaps there is a better way of doing what you want to do.

 

-cb-

[jjk-duffy]

Those 4 lines do exactly he same as my one line, jsut to let you know.

 

It may be to do with encoding, why do you want this removed? is it special characters you want removed?

Perhaps there is a better way of doing what you want to do.

 

-cb-

 

I am open to an alternative method of removing the offending character.    We  want this removed as it is important that we have as accurate of a description as possible for the units that are included in the auction and the owner hates seeing the � on the website.  We attempt to purge the character prior to loading the CSV file but alas some are more diligent than others in that pursuit. 

 

My current alternative method is to edit the database by hand.

[ChemicalBliss]

that character appears because of the encoding you have used on your page.

 

Have a google for utf8 character encoding, on the description insrt script, make sure you encode the string with htmlentities. that should fix it and you wont have to replace anything.

 

so, where/what script isinserting thi description?

 

-cb-