[RESOLVED] Updating Database with CSV file apostrophe problem

[rhianonf]

Hi all. 🙂 I am fairly new to PHP but I have managed to solve problems that I have come across so far by reading helpful information in forums, but this problem has me really stumped! 😕

My PHP code allows a user to update a database by uploading a CSV file. However if there are any special characters in a record (such as apostrophes) the database will not be updated properly.

I know I need to escape the special characters, and I have tried reading the CSV file into a string and used 'addslashes' but MySQL calls for it to be put back into an array which I am unsure of.

I am using PHP Version 5.2.10 so I fear that some of the possible solutions will not be available to me. :bemused:

Any suggestions you can offer will be greatly appreciated.

Many thanks in advance!

Rhianon

<?php

//Upload File
if (isset($_POST['submit'])) {
if (is_uploaded_file($_FILES['filename']['tmp_name'])) {
	echo "<h1>" . "File ". $_FILES['filename']['name'] ." uploaded successfully." . "</h1>"; 
}

//Import uploaded file to Database
$row = 1; 
$handle = fopen($_FILES['filename']['tmp_name'], "r");

while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
if ( $row == 1){
$row++; 
} else { 
	$import="REPLACE INTO carstock(stocknumber,make,model) values('$data[0]','$data[1]','$data[2]')";
	mysql_query($import) or die(mysql_error());
	}
}
fclose($handle);	
}
	?>

<form enctype="multipart/form-data" action="vehicle_import.php" method="post">

File to import:<br />

<input size='30' type='file' name='filename'>

<input type="submit" name="submit" value="Upload"></form>

[halojoy]

Maybe you should try
[man]mysql_real_escape_string[/man]
on your $data[] before insert.
It does a better job, than addslashes

[rhianonf]

Hi Halojoy. Thank you for your help. I tried using mysql_real_escape_string on the import function but unfortunately it did not work. I think this was because I am submitting the data as an array not a string. Do you have any other suggestions? Thanks again!

[Weedpacket]

$data = array_map('mysql_real_escape_string', $data);

[rhianonf]

Hi Weedpacket, thank you for your help. 🙂

I have amended the code to include the array_map function on the CSV file. However when I now upload my CSV file using my PHP code, only the first line of code is imported into the database (the field names) and the page continues to load although nothing happens until it times out.

Here is my new code. Do you have any suggestions? Thanks again.

<?php
	//Upload File
	if (isset($_POST['submit'])) {
	if (is_uploaded_file($_FILES['filename']['tmp_name'])) {
		echo "<h1>" . "File ". $_FILES['filename']['name'] ." uploaded successfully." . "</h1>"; 
	$handle = fopen($_FILES['filename']['tmp_name'], "r+");
	$data = fgetcsv($handle, 1000, ",");
	$escape = array_map('mysql_real_escape_string', $data);
	}

//Import uploaded file to Database
$row = 1; 
$handle = fopen($_FILES['filename']['tmp_name'], "r");

while ($escape !== FALSE) {
if ( $row == 1){
$row++; 
} else { 
	$import="REPLACE INTO carstock(stocknumber,make,model) values('$data[0]','$data[1]','$data[2]')";
	mysql_query($import) or die(mysql_error());
	}
}
fclose($handle);	
}
	?>

[Weedpacket]

    $escape = array_map('mysql_real_escape_string', $data); 

        $import="REPLACE INTO carstock(stocknumber,make,model) values('$data[0]','$data[1]','$data[2]')"; 

But you're still using $data in the statement.

[rhianonf]

Hi thank you again for your reply. I have changed the values to $escape, but now only the first lines of my CSV file (the fields) are imported. :bemused:

Do you know what I have done wrong?

Many thanks,

Rhianon

<?php
    //Upload File
    if (isset($_POST['submit'])) {
    if (is_uploaded_file($_FILES['filename']['tmp_name'])) {
        echo "<h1>" . "File ". $_FILES['filename']['name'] ." uploaded successfully." . "</h1>";
    $handle = fopen($_FILES['filename']['tmp_name'], "r+");
    $data = fgetcsv($handle, 1000, ",");
    $escape = array_map('mysql_real_escape_string', $data);
    }

//Import uploaded file to Database
$row = 1;
$handle = fopen($_FILES['filename']['tmp_name'], "r");

while ($escape !== FALSE) {
if ( $row == 1){
$row++;
} else {
    $import="REPLACE INTO carstock(stocknumber,make,model) values('$escape[0]','$escape[1]','$escape[2]')";
    mysql_query($import) or die(mysql_error());
    }
}
fclose($handle);    
}
    ?> 

[johanafm]

    //Upload File
    if (isset($_POST['submit'])) {
    if (is_uploaded_file($_FILES['filename']['tmp_name'])) { 
	// ...
    }

/* Here, the above if check doesn't matter anymore since that block is closed,
    which translates to "do this wether the file exists or not". Move code below into its
    proper place... */

Would be a lot easier to see what belongs in every block if you indented code properly

	if (isset($_POST['submit'])) {
		if (is_uploaded_file($_FILES['filename']['tmp_name'])) {
			echo "<h1>" . "File ". $_FILES['filename']['name'] ." uploaded successfully." . "</h1>";
			$handle = fopen($_FILES['filename']['tmp_name'], "r+");
			$data = fgetcsv($handle, 1000, ",");
			$escape = array_map('mysql_real_escape_string', $data);
		}

	# read one line from file
    $data = fgetcsv($handle, 1000, ",");
	# escape data from said line
    $escape = array_map('mysql_real_escape_string', $data);
    }

# assuming $escape isn't false the first run, it never will be. infinite loop
while ($escape !== FALSE) {
# first pass, increment $row
if ( $row == 1){
$row++;
}
# all other passes
else {
    $import="REPLACE INTO carstock(stocknumber,make,model) values('$escape[0]','$escape[1]','$escape[2]')";
    mysql_query($import) or die(mysql_error());
    }
# where do you update $escape? see comment before while loop...
fgetcsv();
array_map();
}
fclose($handle);    

[rhianonf]

Hi everyone,

Thanks again for helping me solve my problem. Much appreciated! 🙂

I just realised that I have forgotten to post my solution, so here it is:

<?php

//Upload File
if (isset($_POST['submit'])) {
if (is_uploaded_file($_FILES['filename']['tmp_name'])) {
    echo "<h1>" . "File ". $_FILES['filename']['name'] ." uploaded successfully." . "</h1>";
$handle = fopen($_FILES['filename']['tmp_name'], "r");
$data = fgetcsv($handle, 1000, ",");
} 

//Import uploaded file to Database
$row = 1; 
$handle = fopen($_FILES['filename']['tmp_name'], "r");

while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
if ( $row == 1){
$row++; 
} else { 


          //Update Database Values

	$import="UPDATE carstock(stocknumber, make, model ) 
	VALUES('".mysql_real_escape_string($data[0])."',
		   '".mysql_real_escape_string($data[2])."',
		   '".mysql_real_escape_string($data[3])."' )";
	mysql_query($import) or die(mysql_error());
	}
}


fclose($handle);

}
	?>