PHP/MYSQL - Syntax error relating to a mysql query through as php variable

Question

I am having a problem with syntax errors in the following variable:

$uploadQuery = "
LOAD DATA LOCAL INFILE '".$docRoot."/../../includes/dbUploads/".$fileToUpload."' 
INTO TABLE `promotions` 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
ESCAPED BY '\\' 
LINES TERMINATED BY '\r\n'  
";

I know that it has something do do with the escaping of the ' characters in the LOAD DATA... line. But I am stumped when it comes to what exactly the problem is, or how to reword this query in the correct manner.

So, My question is this:

How do i reword the stated variable in the correct manner, as to have no syntax errors relating to it.

If anyone has any suggestions or input with this, it would be greatly appreciated.

Thank You!

What is your error exactly? Also, did you try to echo $uploadQuery instead of running it from php, and then run it manually and see how can be fixed? — bpgergo
– bpgergo, Commented Nov 9, 2011 at 20:32

jli · Accepted Answer · 2011-11-09 20:29:27Z

3

Missing an escape character, this is the correct string:

$uploadQuery = "
LOAD DATA LOCAL INFILE '".$docRoot."/../../includes/dbUploads/".$fileToUpload."' 
INTO TABLE `promotions` 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '\"' 
ESCAPED BY '\\' 
LINES TERMINATED BY '\\r\\n'  
";

Note the extra \ on the 5th line. It was treating the " as a string terminator. Also another problem (that doesn't cause a syntax error) is on the 7th line, you need to escape the backslashes.

P.S. the markup analyzer even picked it up :P

Edit: you probably also need to change line 7 to ESCAPED BY '\\\\' since this reduces to ESCAPED BY '\\' after PHP parses it.

answered Nov 9, 2011 at 20:29

jli

6,6432 gold badges31 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Craig van Tonder Over a year ago

Thanks alot! I guess i did not "know" after all... The root of my problem that :)

imkingdavid Over a year ago

By the way, you don't have to concatenate variables to a string if you are using double quotes ". For instance, if you say echo "$foo" it will work just like if you said echo $foo. Just note that in some cases you will need to wrap the variable in { and } so that it can be differentiated from the rest of the string's text.

Craig van Tonder Over a year ago

@imkingdavid if i do not concatenate them, it produces more syntax errors for some reason? But generally i do not.

jli Over a year ago

You have to insert the variable into the string like: "ABC $var".

imkingdavid Over a year ago

@BlackberryFan what errors? You should be able to just put, for instance, LOAD DATA INFILE '{$docRoot}/../../includes/dbUploads/{$fileToUpload}' INTO TABLE etc. as long as the string itself is wrapped in " so you don't have to escape the '

|

Collectives™ on Stack Overflow

PHP/MYSQL - Syntax error relating to a mysql query through as php variable

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related