Double quotes in mysql query!

trying to use double quotes in mysql query

[arlo19]

Hello, my first post here. I have been using PHP for months now, but I might still be considered a newbie :-P

anyways, heres my query for loading a txt file into my table from a browser (using php and a browse file form):

$query = "LOAD DATA LOCAL INFILE '$browse' INTO TABLE `email2` FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n'";

The double quotes are causing the problem. The thing is, Im REALLY confused about all this stuff about addslashes() and stripslashes()! I tried adding a slash before the ", then I get a different error:
Parse error: parse error, unexpected T_CONSTANT_ENCAPSED_STRING

I tried the 2 functions above, with no sucess.
I even tried this too:

$query2 = addslashes($query);
$result = mysql_query(stripslashes($query2)) or print(mysql_error());

and that didnt work.
Please tell me what I need to do in order to keep that double quote in my query, thanks in advance!

[jatar_k]

Welcome to WebmasterWorld arlo19,

$query = "LOAD DATA LOCAL INFILE '$browse' INTO TABLE `email2` FIELDS TERMINATED BY ',' ENCLOSED BY '\"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n'";

try that, I just escaped the individual double quote. also take a look at mysql_escape_string [ca.php.net].

[arlo19]

yes, I have tried that, it says:
You have an error in your SQL syntax near ''' at line 20

heres my whole script (i have commented out stuff that ive tried before) :
<?php
$db = mysql_connect("localhost", "root", "") or die("Could not connect");
mysql_select_db("Cards",$db) or die("Error");

function printform() {
echo "<form method='get' action='email.php' enctype='multipart/form-data'>
<p>
<input type='file' name='browse'>
</p>
<p>
<input type='submit' name='Submit' value='Submit'>
</p>
</form>";
}

if ($Submit) {
echo $browse;
echo ("<p><p>");
$query = "LOAD DATA LOCAL INFILE '$browse' INTO TABLE `email2` FIELDS TERMINATED BY ',' ENCLOSED BY '\"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n'";

echo $query;
echo ("<p><p>");

/*
$query2 = addslashes($query);
echo $query2;
echo ("<p><p>");
$query3 = stripslashes($query2);
echo ("<p><p>");
echo $query3;
echo ("<p><p>");
*/

//$result = mysql_query(stripslashes($query2)) or print(mysql_error());

$result = mysql_query($query) or print(mysql_error());

$query = "SELECT `email` FROM `email2` where 'email'!='' ";
$num = 0;
$result = mysql_query($query) or print(mysql_error());
while ($line = mysql_fetch_array($result, MYSQL_NUM)) {
$num++;
foreach ($line as $col_value) {print "\t\t$col_value;\n";}
}
echo ($num);

}
else { printform(); }

?>

[arlo19]

heres full output upon filling in the form:

C:\\Program Files\\Corex\\CardScan\\phpemail.txt

LOAD DATA LOCAL INFILE 'C:\\Program Files\\Corex\\CardScan\\phpemail.txt' INTO TABLE `email2` FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\' LINES TERMINATED BY ' '

You have an error in your SQL syntax near ''' at line 20

[jatar_k]

have you tried pasting the full query into mysql to see if it works?

[arlo19]

I put this in phpmyadmin, and it worked:

LOAD DATA LOCAL INFILE 'C:\\Program Files\\Corex\\CardScan\\phpemail.txt' INTO TABLE `email2` FIELDS TERMINATED BY ',' ENCLOSED BY '\"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n'

hmm when exactly does mysql take out the \ before the double quote?

[jatar_k]

it's not there for mysql it is for php. It tells php to put a literal " in there.

[arlo19]

i see,

well heres the query im now running:
$result = mysql_query("LOAD DATA LOCAL INFILE 'C:\\Program Files\\Corex\\CardScan\\phpemail.txt' INTO TABLE `email2` FIELDS TERMINATED BY ',' ENCLOSED BY '\"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n'") or print(mysql_error());

and the output:

You have an error in your SQL syntax near ''' at line 2

argh this is frustrating. any ideas?

[vincevincevince]

not sure atm, can you ask it to

echo ("LOAD DATA LOCAL INFILE 'C:\\Program Files\\Corex\\CardScan\\phpemail.txt' INTO TABLE `email2` FIELDS TERMINATED BY ',' ENCLOSED BY '\"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n'");

?

then paste that into myadmin sql box?

[arlo19]

thats what I have been doing vince, not sure what you mean I should do.

[arlo19]

ahh, I figured it out!

$query = "LOAD DATA LOCAL INFILE 'C:\\\Program Files\\\Corex\\\CardScan\\\phpemail.txt' INTO TABLE `email2` FIELDS TERMINATED BY ',' ENCLOSED BY '\"'
ESCAPED BY '\\\' LINES TERMINATED BY '\\r\\n'";

I just added those \ before the r and n. I compared phpmysql query which I know worked, to the echo that vince reminded me about. Then I realized that my echo didnt have \r\n, they were taken out by php before it got to mysql!
i cant believe I didnt notice it.

yep, well thanks for your help ppl!