This article provides the solution for the issue with LOAD DATA INFILE statement. PHP provides a lots of options to import data from text files. I faced this issue while I was trying to import data from a text file (data.txt) into mysql table using "LOAD DATA INFILE" statement with MySQLi Extension, but getting an error  " The used command is not allowed with this MySQL version ".

Below is the data(data.txt) and PHP code used for importing data from text file, The purpose of given code is to import data from data.text file into Users table under the fileds (Name, Email, Password).

data.txt

Amit amit@test.com 2e75636563fsda7f4b4085c115

Rahul amit@test.com 6s75636563fsda7f4b4085c124

Ravi amit@test.com 8f75636563fsda7f4b40uh4524

Ram ram@yahoo.com 2e756hg893fsda7f4b4085ct25

test.php

<?php

//create connection link

$con = mysqli_connect("localhost","root","123456","test");

//if connection failed

if (mysqli_connect_errno())
{
   echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$query = "CREATE TABLE IF NOT EXISTS `Users` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(100) NOT NULL,
  `Email` varchar(150) NOT NULL,
  `Password` varchar(255) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1";

//if table created successfully

if(mysqli_query($con,$query)){

    $insert_query = "LOAD DATA LOCAL INFILE 'path of text file'
       INTO TABLE tbl_Users
       FIELDS TERMINATED BY ' '
       LINES TERMINATED BY '\\n'(Name,Email,Password);";
    if (mysqli_query($con, $insert_query)) {
        echo "Affected rows: " . mysqli_affected_rows($con);
    } else {
        echo "Error: " . $insert_query . "<br>" . mysqli_error($con);
    }

} else {

    echo "Error in table creation";

}

?>

After searching a lot on google, I found this Security Issues with LOAD DATA LOCAL. By default MYSQL_OPT_LOCAL_INFILE option is set to 0(false).

Solution : The solution for above defined problem is to set MYSQL_OPT_LOCAL_INFILE true using mysqli_options.

Change the  $con = mysqli_connect("localhost","root","123456","test"); line with code given below :

$con = mysqli_init();
mysqli_options($con, MYSQLI_OPT_LOCAL_INFILE, true);
mysqli_real_connect($con, "localhost","root","123456", "test");