Error Handling when running a CSV Import to MySQL using PHP

Question

I'm trying to automate the import of a CSV file into MySQL. I thought I could run a PHP file as a cronjob on my server as PHPMyAdmin doesn't have Events enabled (and can't be due to webhost).

I've been trying with LOAD DATA Syntax as seems popular when I google the issue and come across the fifty million threads on Stack Overflow. However, I'm having trouble debugging my own configuration and frustrated that I can't find a way for it to show me errors to help work out what I'm doing wrong.

I know my CSV file variable is correct as I have a code to check that and it passes (I've also purposely made it fail and it fails, so definitely working):

$file = fopen($csvfile,"r");

if (!$file) {
        echo "<h1>bachHa'!</h1><p>Error opening data file.</p>\n";
        exit;
}

So, I know I've located my CSV file and I'm passing that correctly to LOAD DATA, but after that, who knows, all I know is I don't get anything in my database after it runs:

$import = <<<eof
    LOAD DATA LOCAL INFILE '$csvfile'
     INTO TABLE test_upload
     FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
     LINES TERMINATED BY '\n'
     IGNORE 1 LINES
    (response_id,date_submitted,status,q1,q5_text,q2,q3,q4_1,q4_2,q4_3,q4_4,q4_5,StoreName);
eof;

$conn->query($import);

The column headers are accurate, my datafile is aligned with them, empty columns are allowed NULL.

I've looked through the manual but can't find a way to grab the errors to display in my PHP file, echo'ing $import looks fine and $conn doesn't echo anything (not sure if it should, I'm not a programmer and just trying to bodge my way through this).

Example of the csv file:

"Response ID","Date Submitted","Status","Q01","Q05","Q02","Q03","Q04_01","Q04_02","Q04_03","Q04_04","Q04_05","StoreName"
"62","2019/05/06 16:43:59","Complete","8","","8","No","Very good","Good","Very good","Good","Very good","Store A"
"63","2019/05/06 16:45:36","Complete","10","I had a very nice experience with a lady called Karen she was very helpful","10","Yes","Very good","Good","Good","Good","Very good","Store C"

Any help is greatly received!

Answer 1

Thanks to @jpheldson for the help with $conn->errorInfo() to get the error message, as suspected in the above comments my provider doesn't allow LOAD DATA LOCAL INFILE on shared hosting (nor Events in PHPMyAdmin) so had to find a different way around my problem:

include '/php_include/db_details.php';
session_start();
try {
    $conn = new PDO($dsn, $username, $password);
} catch (PDOException $e) {
    echo '<h1>bachHa\'!</h1><p>Connection failed: ' . $e->getMessage(). '</p>';
}

//set import data file:
foreach (glob("/ftp/raw_survey/*.csv") as $csvfile)

//check we can open it
$file = fopen($csvfile,"r");

if (!$file) {
        echo "<h1>bachHa'!</h1><p>Error opening data file.</p>\n";
        exit;
}

//push it into MySQL
$file = fopen($csvfile, "r");
        fgetcsv($file, 10000, ',', '"'); //cheat to ignore header
        while (($column = fgetcsv($file, 10000, ',', '"')) !== FALSE) {

            $sqlInsert = "INSERT INTO test_upload(response_id,date_submitted,status,q1,q2,q3,q4_1,q4_2,q4_3,q4_4,q4_5,q5_text,StoreName)
                   values ('" . $column[0] . "','" . $column[1] . "','" . $column[2] . "','" . $column[3] . "','" . $column[4] . "','" . $column[5] . "','" . $column[6] . "','" . $column[7] . "','" . $column[8] . "','" . $column[9] . "','" . $column[10] . "','" . htmlentities($column[11], ENT_QUOTES, 'UTF-8') . "','" . $column[12] . "')";
            mysqli_real_escape_string(q5_text);
            $result = $conn->query($sqlInsert);

            if (! empty($result)) {
                $type = "Qapla'";
                $message = "CSV Data uploaded into the database";
            } else {
                $type = "bachHa'";
                $message = "Problem in Importing CSV Data";
            }
        }
//var_dump($conn->errorInfo());

//close file now we're done with it
fclose($file);

//transfer from temp upload to main data
        if (! empty($result)) {        
            $sqlUpdate = "INSERT INTO data (response_id,date_submitted,status,q1,q2,q3,q4_1,q4_2,q4_3,q4_4,q4_5,q5_text,StoreName)
                SELECT u.response_id,u.date_submitted,u.status,u.q1,u.q2,u.q3,u.q4_1,u.q4_2,u.q4_3,u.q4_4,u.q4_5,u.q5_text,u.StoreName
                FROM test_upload AS u
                WHERE u.`status` = 'Complete' AND u.store_list IS NOT NULL AND u.response_id NOT IN (SELECT response_id FROM data)";
            $result2 = $conn->query($sqlUpdate);

            if (! empty($result2)) {
                $type = "Qapla'";
                $message = "CSV Data uploaded & transferred to main dataset";
            } else {
                $type = "bachHa'";
                $message = "Data uploaded but not imported";
            }
        }

// Verify Load
$sql = "SELECT StoreName AS 'STORE', COUNT(*) AS 'RECORDS' FROM test_upload WHERE `status` = 'Complete' AND store_list IS NOT NULL AND response_id NOT IN (SELECT response_id FROM data) GROUP BY StoreName ORDER BY StoreName";
$verify = $conn->query($sql);

//clear temp
        if (! empty($result2)) {       
            $sqlClear = "DELETE FROM test_upload";
            $result3 = $conn->query($sqlClear);

            if (! empty($result3)) {
                $type = "Qapla'";
                $message = "CSV Data uploaded, transferred to main dataset & temp table cleared";
            } else {
                $type = "bachHa'";
                $message = "Data imported but temp not cleared";
            }
        }   

//disconnect SQL
$conn = null;       
//delete csv to prevent it being re-imported in the future
unlink($csvfile);
?>
<h1><?php echo($type); ?></h1><p><?php echo($message); ?></p>
<table>
        <?php while($row = $verify->fetch(PDO::FETCH_ASSOC)) {
            echo "<tr><td>" . $row['STORE']. '</td><td>'. $row['RECORDS']. '</td></tr>';
        }?>
</table>

This works perfectly fine when opening in the browser and called as a cronjob, so, job done, example placed above to help anyone who comes across this in the future and needs a similar alternative. Using PDO, htmlentities on the open text and mysqli_real_escape_string so hopefully this is SQL injection proof - if I've missed anything please feel free to tell me why I'm stupid!