Errors importing CSV file into DB using PDO after upgrade to PHP 5.4

Question

I have a php file that imports a CSV file into my database. Since my hosting provider upgraded to PHP 5.4, I get an error on the line 'LINES TERMINATED BY ', but I am not sure why. Here is my code:

<?php
    require('phpsqlajax_dbinfo.php');
    $databasehost = $db_host; 
    $databasename = $db_name; 
    $databasetable = "tbl_csvImport"; 
    $databaseusername=$db_user; 
    $databasepassword = $db_pass; 
    $fieldseparator = ","; 
    $lineseparator = "\n";
    $csvfile = "doecsv.csv";
    echo $lineseparator;
    if(!file_exists($csvfile)) {
        die("File not found. Make sure you specified the correct path.");
    }

    try {
        $pdo = new PDO("mysql:host=$databasehost;dbname=$databasename", 
            $databaseusername, $databasepassword,
            array(
                PDO::MYSQL_ATTR_LOCAL_INFILE => true,
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
            )
        );
    } catch (PDOException $e) {
        die("database connection failed: ".$e->getMessage());
    }

    $pdo->exec("DELETE FROM `$databasetable`");

    try {
        $affectedRows = $pdo->exec("
            LOAD DATA LOCAL INFILE ".preg_replace('/"[^"]+"/','',$pdo->quote($csvfile))." INTO TABLE `$databasetable`
            FIELDS TERMINATED BY ".$pdo->quote($fieldseparator)."
            OPTIONALLY ENCLOSED BY ".$pdo->quote('"')."
            LINES TERMINATED BY ".$pdo->quote($lineseparator)."IGNORE 1 LINES");
    } catch (Exception $e) {
        die ("CSV Parse Failed: ".$e->getMessage()." | Error on line: ".$e->getLine());
    }

    echo" Success. <br/>";  
?>

Error Code: SQLSTATE[42000]: Syntax error or access violation: 1148 The used command is not allowed with this MySQL version | Error on line: 35

I added a space before ignore and tried it and that didnt help. I have put it back to the original way it was. — ProfessionalAmateur, Oct 13 '14 at 20:46
Did you check phpinfo(); is PDO loaded? After updating PHP sometimes I have to change the load order of the extensions because none of the SQL extensions are loaded. — Laci K, Oct 13 '14 at 20:46
I dont think that link helps me. It is for bringing in local files. I have a different script that runs before to download the csv file into a directory on the server. — ProfessionalAmateur, Oct 13 '14 at 20:59

score 0 · Answer 1 · answered Oct 14 '14 at 16:23

0

I found a workaround that works for me:

http://crewow.com/CSV-Importer-in-MySQL.php

answered Oct 14 '14 at 16:23

ProfessionalAmateur

171
1
7

Errors importing CSV file into DB using PDO after upgrade to PHP 5.4

1 Answers1