how to load data from local file in php with pdo driver

Question

I have a function that needs to load data into a mysql db from a csv file, so I wrote some code:

try {

    $pdo = new PDO('mysql:host=localhost;dbname=borsino_ittico',
                   'XXXXX', 'XXXXX', 
                    array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4",
                          PDO::MYSQL_ATTR_LOCAL_INFILE => true, 
                          PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION)
                  );


} 
catch (PDOException $e) {
       $mex= "database connection failed: ".$e->getMessage();
       return $mex;
    }

other code

try {
    $affectedRows = $pdo->exec("
    LOAD DATA LOCAL INFILE ".$pdo->quote($file)." INTO TABLE ".
            $pdo->quote($dbTable)." FIELDS TERMINATED BY ';'
            LINES TERMINATED BY '\n,\\n'");

    $mex= "Loaded a total of $affectedRows records from this csv file.\n";
    return $mex;

} 
catch (PDOException $e) {
        $mex= "ATTENZIONE, ERRORE CARICAMENTO DATI: ".$e->getMessage();
        return $mex;
}

Actually this doesn't seem to works and threw an exception:

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 ''asta' FIELDS TERMINATED BY ';' LINES TERMINATED BY ' ,\n'' at line 1

First question is about $file (the resource): should be in absolute/relative form (likes c:\xxx\csvfile.csv or ..\xxx\csvfile.csv) or I've to expose it with a function likes $csvFile=fopen($file,"r");

Then, should I use pdo native function likes $pdo->quote() for insert arguments (like the file,table name, separator, lines terminated by) on the $affectedRows query?

How can I force the esecution of the query even with some errors on some row?

Answer 1

You're quoting your table name, producing

... INTO table 'asta' ...

'-quotes turn things into a strings. So asta is no longer a table name, it's just a string with the letters a, s, etc...

Identifiers must be quoted with backticks:

... INTO table `asta` ...
               ^----^---