Import CSV file to Mysql Database in PHP
<p>I tried to import a csv file from another server using <code>LOAD DATA LOCAL INFILE</code> but it gives me an error <code>The user update failed: Can't find file</code>, even the file is exist in the path given. I also tried the <code>LOAD DATA INFILE</code> but <code>User access denied</code></p>

<p>Is there's any other way to import a csv file directly to the file path (without using any submit button) in the mysql database and delete the current data in the dbase table if there's a new csv file uploaded?</p>

<p><strong>EDIT</strong>: I tried also this PHP script, to import the csv file without any button, but the csv file data is not importing</p>

<p>PHP (2nd code):</p>

<pre><code>$mysqli = new mysqli("localhost", "user", "password", "dbasename");
$file = "/var/www/html/files/filename.csv";
$handle = fopen($file,'r');
while (($data = fgetcsv($handle, 10000, ",")) !== FALSE){
     $num = count($data);
     for ($c=0; $c &lt; $num; $c++){
         $col[$c] = $data[$c];
     }

     $col1 = $data[0];
     $col2 = $data[1];
     $col3 = $data[2];
     $col4 = $data[3];
     $col5 = $data[4];

     // SQL Query to insert data into DataBase
     $query = "INSERT INTO dbname.tablename(col1,col2,col3,col4,col5) VALUES('".$col1."','".$col2."','".$col3."','".$col4."','".$col5."')";
     $mysqli-&gt;query($query);
}
fclose($handle);
</code></pre>

<p>Here's the PHP:</p>

<pre><code>$sql = "LOAD DATA LOCAL INFILE 'filename.csv'
       INTO TABLE dbname.tablename
       FIELDS TERMINATED BY ','
       OPTIONALLY ENCLOSED BY '\"' 
       LINES TERMINATED BY '\n' 
       IGNORE 1 LINES;";

$con=mysqli_connect("localhost","user","password","db");
// Check connection
if (mysqli_connect_errno()){
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
};

$result = mysqli_query($con, $sql);

if (mysqli_affected_rows($con) == 1){
  $message = "The data was successfully added!";
} else {
  $message = "The user update failed: ";
  $message .= mysqli_error($con); 
};

echo $message;
</code></pre>