CODEWITHSUNDEEP
phpmysqlcsvpdo
Shane
Shane

Reputation: 325

PHP: LOAD DATA INFILE syntax errors

I'm trying to import csv data into mysql using PDO and LOAD DATA INFILE (I've also tried LOAD DATA LOCAL INFILE) but I keep getting a syntax error and I have no idea why. The filepath and table names seem to be correct.

here is the import() function I am using - 

function import() {

    $this->db = mydb::getConnection();


    // set the column names for the selected journal table
    if ($this->table = "bsp_journals") {
        $columns = "category, discipline, subject, sourcetype, issn, publicationname, indabstart, indabstop, fulltextstart, fulltextstop, fulltextdelay, peerreviewed";
    }



    try {

        $sql = "LOAD DATA LOCAL INFILE '$this->file'
            INTO TABLE '$this->table'
            FIELDS TERMINATED BY ','
            LINES TERMINATED BY '\\n'
            OPTIONALLY ENCLOSED BY '\"'
            ($columns)";

        $statement = $this->db->prepare($sql);

        $statement->execute();

        $this->return = $this->files['filename']['tmp_name'];

    } catch (PDOException $ex) {

        //throw $ex;
        $this->return = $ex->getMessage() . "<br /></br />" . $sql . "<br /></br />File Name = " . $this->file;

    }



    return $this->return;
}

below is the message I get including the mysql error message and the SQL query

- import result : SQLSTATE[42000]: Syntax error or access violation: 1064 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 ''bsp_journals' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' ' at line 2

LOAD DATA LOCAL INFILE 'files/buh-journals.csv' INTO TABLE 'bsp_journals' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' OPTIONALLY ENCLOSED BY '"' (category, discipline, subject, sourcetype, issn, publicationname, indabstart, indabstop, fulltextstart, fulltextstop, fulltextdelay, peerreviewed)

File Name = files/buh-journals.csv

Upvotes: 1

Views: 4680

Answers (2)

Puya Sarmidani
Puya Sarmidani

Reputation: 319

There is a bug in PDO. I recommend to use mysqli for now.

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562310

INTO TABLE '$this->table'

Don't use single-quotes to quote a table name. Single-quotes are for string literals or date literals.
Either use no quotes, or else back-ticks for delimited identifiers.

INTO TABLE `$this->table`

Re your comment:

You apparently removed quotes from both the filename and the tablename. This is not what I meant. Just remove the string-quotes from the tablename. You do need them for the filename.

Example:

$sql = "LOAD DATA LOCAL INFILE '$this->file'
    INTO TABLE `$this->table`
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\\n'
    OPTIONALLY ENCLOSED BY '\"'
    ($columns)";

Review the syntax documented at http://dev.mysql.com/doc/refman/5.6/en/load-data.html

Notice the presence or absence of quotes around INFILE 'file_name' and INTO TABLE tbl_name:

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name

You don't actually need the back-ticks around the table name, unless your table name contains special characters, whitespace, or reserved words.

You got this error:

General error: 2030 This command is not supported in the prepared statement protocol yet

Right, not all SQL commands are compatible with prepare(). I didn't check that for your case, because we were resolving the syntax error first. You can find a list of the commands that can be prepare()d under the heading SQL Syntax Allowed in Prepared Statements on this page: http://dev.mysql.com/doc/refman/5.6/en/sql-syntax-prepared-statements.html

I linked to the MySQL 5.6 docs, but you should visit the docs for the version of MySQL you use. Because the list of compatible commands changes from release to release.

So you can't use prepare() -- you'll have to use exec() or query() instead for a LOAD DATA INFILE command.

Alternatively, if you're using PDO, you can set the attribute PDO::ATTR_EMULATE_PREPARES to true. That will make PDO fake out MySQL, so prepare() is a no-op and the query is actually sent during execute().

Upvotes: 7

Related Questions