Page 1 of 1

Single Quote messes up file load

Posted: Mon Jun 06, 2005 9:06 am
by Perryl7
I am loading a CSV file into a table. All name that do not contain an apostrophe load fine, but one like "O'Reilly" throw warnings (even though they load into the table). I am doing this with a PHP page. Below is my code. Does anyone know how to ignore single quotes on a load command or any other way around this?

Code: Select all

$sql = "LOAD DATA INFILE '" . $current_file . "'
INTO TABLE temp FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY 
'\\r\\n' IGNORE 1 LINES";
		
$result = mysql_query($sql);

Posted: Mon Jun 06, 2005 9:20 am
by malcolmboston

Code: Select all

// on inputting to the database
$current_file = addslashes($current_file);
// on retrieval from the database
$current_file = stripslashes($current_file);

Posted: Mon Jun 06, 2005 9:42 am
by Perryl7
Will the addslashes make the import ignore the single quotes because it is escaping it? Once it is in the database I can retrieve it without a problem using double quotes.

Posted: Mon Jun 06, 2005 9:56 am
by malcolmboston
yes it will.

basically the reason it was "crashing" is because PHP thinks the ' is part of the actual PHP code and not the MySQL code, therefore crashing PHP with a syntax error, escaping it allows the value to be altered for database storage and then returned to normal when retrieving.

Posted: Mon Jun 06, 2005 3:28 pm
by Perryl7
Thanks, that worked.

Posted: Mon Jun 06, 2005 3:32 pm
by John Cartwright