CODEWITHSUNDEEP
phpmysql
samlancer
samlancer

Reputation: 133

How to fix SQL Syntax error for MySQL database?

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...

Upvotes: 2

Views: 1933

Answers (1)

Jens
Jens

Reputation: 69450

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

Upvotes: 2

Related Questions