MySQL: Enable LOAD DATA LOCAL INFILE

<?php # MySQL have a function allow us import data from text file, csv file to a table # This function reads rows from a text file into a table at a very high speed # References: https://dev.mysql.com/doc/refman/5.7/en/load-data.html $sql = <<<SQL LOAD DATA LOCAL INFILE 'data.csv' INTO TABLE orders FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\\n' IGNORE 1 LINES (`Payment_date`, `personId`, `playingCurrency`, `playingOriginalAmount`)"; SQL; $conn->query($sql) /* There is an issue when we use that command in PHP with MariaDB or a new MySQL server version PHP script will throw an error like bellow: [PDOException] SQLSTATE[42000]: Syntax error or access violation: 1148 The used command is not allowed with this MariaDB version This issue happened because in MariaDB and new MySQL server, the LOAD DATA LOCAL function was disabled by default and the php5-mysql driver is not compatible. To fix that issue we need to update mysql config file and replace php5-mysql with a native driver 1. Edit mysql config file: sudo nano /etc/mysql/my.cnf Then Add: [mysqld] local-infile = 1 [mysql] local-infile = 1 2. Replace php5-mysql by a native driver apt-get install php5-mysqlnd */ ?>