ebody ebody - 2 months ago 10
PHP Question

MySQL - LOAD DATA LOCAL INFILE - Problems if DB tablename has hyphen

I use in php a script to import a csv into a database table by

$sqlQueryImport = "LOAD DATA LOCAL INFILE '".$fileNewDataImportInDB."' INTO TABLE " .$sqlTableName
. " CHARACTER SET UTF8"
. " FIELDS TERMINATED BY ';'"
. " ENCLOSED BY '\"'"
. " LINES TERMINATED BY '\n'";


I got always the error:


You have an error in your SQL syntax; check the manual that
corresponds to your MariaDB server version for the right syntax to use
near 'tbl-test' CHARACTER SET UTF8 FIELDS TERMINATED BY ';' ENCLOSED
BY '"' LINES T' at line 1"


To test it i imported the csv directly in phpmyadmin into the DB table and it works.

Then i changed the DB tablename "tbl-test" into "tbl_test" and get no error.

It is not allowed to use "-" in a database tablename? Or must i write a special code for it?

Answer

You can try escaping the table name with backticks. So your LOAD DATA statement would look like this:

LOAD DATA LOCAL INFILE someFile.csv INTO TABLE `tbl-test`
CHARACTER SET UTF8
FIELDS TERMINATED BY ';'
ENCLOSED BY '\"'
LINES TERMINATED BY '\n'

The full code:

$sqlQueryImport = "LOAD DATA LOCAL INFILE '".$fileNewDataImportInDB."' INTO TABLE `" .$sqlTableName
        . "` CHARACTER SET UTF8"
        . " FIELDS TERMINATED BY ';'"
        . " ENCLOSED BY '\"'"               
        . " LINES TERMINATED BY '\n'";
Comments