How to fix SQL Syntax error for MySQL database?

Question

I want to upload .csv file data to MySQL table through web form interface. After searching scripts I got one to do the job, but it is giving SQL syntax error during execution. Here I am giving that particular code snippet as of now, but if required I would give the full code.

            if($this->table_exists)
            {
              $sql = "LOAD DATA INFILE '".@mysql_escape_string($this->file_name).
                     "' INTO TABLE '".$this->table_name.
                     "' FIELDS TERMINATED BY '".@mysql_escape_string($this->field_separate_char).
                     "' OPTIONALLY ENCLOSED BY '".@mysql_escape_string($this->field_enclose_char).
                     "' ESCAPED BY '".@mysql_escape_string($this->field_escape_char).
                     "' ".
                     ($this->use_csv_header ? " IGNORE 1 LINES " : "")
                     ."('".implode("','", $this->arr_csv_columns)."')";
              $res = @mysql_query($sql);
              $this->error = mysql_error();
            }

During execution, it is throwing the following error:

Errors

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''temp_18_04_2015_05_44_00' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\\' ' at line 1

The gererated SQL command is as follows

LOAD DATA INFILE '/tmp/phpFzT3qc' 
INTO TABLE 'temp_18_04_2015_06_06_37' 
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '\\' 
ESCAPED BY '\\' 
IGNORE 1 LINES ('SLNO','NAME')

I am a novice programmer, so I am not being able to get the error...

Do not use the deprecated mysql_ API. Use mysqli_*or PDO. — Jens
– Jens, Commented Apr 18, 2015 at 5:56
@Jens. hope I have already shared the SQL command. Anything else you are asking about? — samlancer
– samlancer, Commented Apr 18, 2015 at 5:58
I mean the generated SQL command without the php function. So print out $sql and share it. — Jens
– Jens, Commented Apr 18, 2015 at 5:59
LOAD DATA INFILE '/tmp/phpFzT3qc' INTO TABLE 'temp_18_04_2015_06_06_37' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\\' ESCAPED BY '\\' IGNORE 1 LINES ('SLNO','NAME') @Jens — samlancer
– samlancer, Commented Apr 18, 2015 at 6:07

Jens · Accepted Answer · 2015-04-18 06:04:05Z

2

I think you have to remove the single quotes around the table Name:

$sql = "LOAD DATA INFILE '".@mysql_escape_string($this->file_name).
                     "' INTO TABLE ".$this->table_name.
                     " FIELDS TERMINATED BY '".@mysql_escape_string($this->field_separate_char).
                     "' OPTIONALLY ENCLOSED BY '".@mysql_escape_string($this->field_enclose_char).
                     "' ESCAPED BY '".@mysql_escape_string($this->field_escape_char).
                     "' ".
                     ($this->use_csv_header ? " IGNORE 1 LINES " : "")
                     ."('".implode("','", $this->arr_csv_columns)."')";

If you use the single qoutes it is a string and not a table Name. to escape table names you have to use backticks.

For more Information about the Syntax of LOAD DATA INFILE see the documentation

answered Apr 18, 2015 at 6:04

Jens

69.6k15 gold badges107 silver badges133 bronze badges

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

8 Comments

samlancer Over a year ago

Jens, I think single quotes are allowed as per SQL syntax. I have seen that while running commands in PHPMyadmin.

Jens Over a year ago

@samlancer Single quotes are allowed. that is correct. But only to use it with static strings not with table or column names.

samlancer Over a year ago

No, Jens. I tried after removing the single quotes around the table name, but it did not work. Can you suggest another running script available in web which can import .csv file to MYSQL database using PHP?

samlancer Over a year ago

Sorry! It is showing Errors: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''SLNO','NAME')' at line 1 . I beg your excuse, I put it here again. @Jens

samlancer Over a year ago

The new generated SQL command is LOAD DATA INFILE '/tmp/phpsa08CZ' INTO TABLE temp_18_04_2015_06_18_55 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\\' ESCAPED BY '\\' IGNORE 1 LINES ('SLNO','NAME') @Jens

|

Collectives™ on Stack Overflow

How to fix SQL Syntax error for MySQL database?

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related