PHP | Load Data Infile to insert csv data into Mysql with PHP

Problem: How to insert csv data into mysql table with mysql query in php. What problems usually you face with this and how to resolve them.

Description: I have csv file with thousands of records. I want to insert these records in mysql table with php.

Solution: There are many ways to insert csv records into mysql table. You can read each record from csv one by one and insert into mysql. But Load Data Infile is more efficient way to do this. It takes very less time to insert csv record in table instead of other ways to do this in php.

For Example:

Mysql table: user_detail:

CSV File: Test.csv:

Code:

$conn = mysqli_connect('localhost', 'root', ’password’, ’test_db’);
if (!$conn) {
    echo "couldn't connect to database"
    exit;
} else {
    $sql = load data local infile‘ csv file path / Test.csv ' into table 
    user_detail fields terminated by ','
    optionally enclosed by '\"'
    lines
    terminated by '\n'
    IGNORE 1 LINES(Id, first_name, last_name);

    if (mysqli_query($con, $sql)) {
        echo "executed";
    } else {
        echo mysqli_error($con);
    }

}

Common problems:

The command used is not allowed with this MySQL version.

Solution: Set local-infile=1.Initially, it is set to be 0;

1. Open /etc/mysql/my.cnf and change local-infile to
local-infile=1 from local-infile=0;
Step-2: restart your mysql;

2. Access denied for user ‘user’@’%’ (using password: YES).

Solution: You need to grant file permissions for the user.

GRANT FILE ON *.* TO 'user'@'localhost';