upload a csv file to MySQL database via php

Question

I am making a web page where my non-tech-savy client can upload a csv file with 25,000+ rows. I found one way to do it that uses fgetcsv(), and makes a query that looks like this:

INSERT INTO `usbcData` (first, last, usbc, year) values
("Joshua", "Doom", "1324-1324", "2015");
INSERT INTO `usbcData` (first, last, usbc, year) values
("Turd", "Fergason", "1324-1325", "2014");
INSERT INTO `usbcData` (first, last, usbc, year) values
("Bruce", "Wayne", "1324-1326", "2013");
<Insert 20,000 more INSERTS here.>

The problem is this takes WAY too long. 20-30 minutes too long. So now I am trying to get MySQL's LOAD DATA INFILE to work. I can make it work with phpmyadmin when I include the file from /home/joshua/file.csv (I'm running GNU/Linux on my laptop), but not with my php code. I've tried using the the query with and without the local keyword, the pdo extension, the mysqli extension, and a system call, but none work.

I followed this guide:Error 1148 MySQL The used command is not allowed with this MySQL version to set up my my.cnf to allow the local keyword to work as well as configuring my pdo connection to allow using the local keyword.

[mysqld]
local-infile 

[mysql]
local-infile

Here is the php script, where the user's uploaded csv file is sent. It contains several methods to try to use get MySQL's LOAD DATA INFILE to work:

<?php
ini_set('display_errors', 'On');
error_reporting(E_ALL | E_STRICT);

echo "The file name is ".$_FILES['userfile']['name']."<br>";
echo "The file type is ".$_FILES['userfile']['type']."<br>";
echo "The size of the file is ".$_FILES['userfile']['size']." bytes <br>";
echo "The file is stored on the server as ".$_FILES['userfile']['tmp_name']."<br>";

$file_name = $_FILES['userfile']['name'];
$file_size = $_FILES['userfile']['size'];
$file_tmp = $_FILES['userfile']['tmp_name'];
$file_type = $_FILES['userfile']['type'];

$user = "root";
$pass = "passord";

try {
  $dbh = new PDO('mysql:host=localhost;dbname=usbc', $user, $pass,
               array(PDO::MYSQL_ATTR_LOCAL_INFILE => true,
                     PDO::ATTR_PERSISTENT => true)
);
} catch (Exception $e) {
  die("Unable to connect: " . $e->getMessage());
}


$dbh->query("TRUNCATE TABLE `usbcData`");

if (chmod($file_tmp, 0777)) {
    echo "chmod worked on $file_tmp <br>";
} else {
    echo "chmod didn't work on $file_tmp <br>";
}
$sql = 'load data infile "'.$file_tmp.'" into table `usbcData` ';
$sql .= 'fields terminated by "," ';
$sql .= 'lines terminated by "\n" ';
$sql .= '(first, last, usbc, year) ';
$dbh->query($sql);
//$result = $dbh->query($sql);
echo $sql."<br>";

$sql = 'load data infile "'.$file_name.'" into table `usbcData` ';
$sql .= 'fields terminated by "," ';
$sql .= 'lines terminated by "\n" ';
$sql .= '(first, last, usbc, year) ';
$dbh->query($sql);
//$result = $dbh->query($sql);
echo $sql."<br>";

$sql = 'load data local infile "'.$file_tmp.'" into table `usbcData` ';
$sql .= 'fields terminated by "," ';
$sql .= 'lines terminated by "\n" ';
$sql .= '(first, last, usbc, year) ';
$dbh->query($sql);
//$result = $dbh->query($sql);
echo $sql."<br>";

$sql = 'load data local infile "'.$file_name.'" into table `usbcData` ';
$sql .= 'fields terminated by "," ';
$sql .= 'lines terminated by "\n" ';
$sql .= '(first, last, usbc, year) ';
$dbh->query($sql);
//$result = $dbh->query($sql);
echo $sql."<br>";

$dbh = null;

$mysqli = new mysqli("localhost", "root", "password", "usbc");
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}

$sql = 'load data infile "'.$file_tmp.'" into table `usbcData` ';
$sql .= 'fields terminated by "," ';
$sql .= 'lines terminated by "\n" ';
$sql .= '(first, last, usbc, year) ';
$mysqli->query($sql);
//$result = $dbh->query($sql);
echo $sql."<br>";

$sql = 'load data infile "'.$file_name.'" into table `usbcData` ';
$sql .= 'fields terminated by "," ';
$sql .= 'lines terminated by "\n" ';
$sql .= '(first, last, usbc, year) ';
$mysqli->query($sql);
//$result = $dbh->query($sql);
echo $sql."<br>";

$sql = 'load data local infile "'.$file_tmp.'" into table `usbcData` ';
$sql .= 'fields terminated by "," ';
$sql .= 'lines terminated by "\n" ';
$sql .= '(first, last, usbc, year) ';
$mysqli->query($sql);
//$result = $dbh->query($sql);
echo $sql."<br>";

$sql = 'load data local infile "'.$file_name.'" into table `usbcData` ';
$sql .= 'fields terminated by "," ';
$sql .= 'lines terminated by "\n" ';
$sql .= '(first, last, usbc, year) ';
$mysqli->query($sql);
//$result = $dbh->query($sql);
echo $sql."<br>";




$dbUser = "root";
$dbHost = "localhost";
$dbPass = "password";
$dbName = "usbc";

$sql = "LOAD DATA INFILE '$file_tmp' INTO TABLE `usbcData` FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';";
echo $sql."<br>";
system("mysql -u $dbUser -h $dbHost --password=$dbPass --local_infile=1 -e \"$sql\" $dbName");

$sql = "LOAD DATA INFILE '$file_name' INTO TABLE `usbcData` FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';";
echo $sql."<br>";
system("mysql -u $dbUser -h $dbHost --password=$dbPass --local_infile=1 -e \"$sql\" $dbName");

$sql = "LOAD DATA local INFILE '$file_tmp' INTO TABLE `usbcData` FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';";
echo $sql."<br>";
system("mysql -u $dbUser -h $dbHost --password=$dbPass --local_infile=1 -e \"$sql\" $dbName");

$sql = "LOAD DATA local INFILE '$file_name' INTO TABLE `usbcData` FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';";
echo $sql."<br>";
system("mysql -u $dbUser -h $dbHost --password=$dbPass --local_infile=1 -e \"$sql\" $dbName");

?>

The output looks like this:

The file name is MonthlyCSVDataFileforIndianaYouth.csv
The file type is text/csv
The size of the file is 759494 bytes 
The file is stored on the server as /tmp/phpT9cyBK
/tmp/phpT9cyBK is writable
chmod worked on /tmp/phpT9cyBK 
load data infile "/tmp/phpT9cyBK" into table `usbcData` fields terminated by "," lines terminated by "\n" (first, last, usbc, year) 
load data infile "MonthlyCSVDataFileforIndianaYouth.csv" into table  `usbcData` fields terminated by "," lines terminated by "\n" (first, last,  usbc, year) 
load data local infile "/tmp/phpT9cyBK" into table `usbcData` fields terminated by "," lines terminated by "\n" (first, last, usbc, year) 
load data local infile "MonthlyCSVDataFileforIndianaYouth.csv" into table `usbcData` fields terminated by "," lines terminated by "\n" (first, last, usbc, year) 
load data infile "/tmp/phpT9cyBK" into table `usbcData` fields terminated by "," lines terminated by "\n" (first, last, usbc, year) 
load data infile "MonthlyCSVDataFileforIndianaYouth.csv" into table `usbcData` fields terminated by "," lines terminated by "\n" (first, last, usbc, year) 
load data local infile "/tmp/phpT9cyBK" into table `usbcData` fields terminated by "," lines terminated by "\n" (first, last, usbc, year) 
load data local infile "MonthlyCSVDataFileforIndianaYouth.csv" into table `usbcData` fields terminated by "," lines terminated by "\n" (first, last, usbc, year) 
LOAD DATA INFILE '/tmp/phpT9cyBK' INTO TABLE `usbcData` FIELDS TERMINATED BY ',' LINES TERMINATED BY ' ';
LOAD DATA INFILE 'MonthlyCSVDataFileforIndianaYouth.csv' INTO TABLE `usbcData` FIELDS TERMINATED BY ',' LINES TERMINATED BY ' ';
LOAD DATA local INFILE '/tmp/phpT9cyBK' INTO TABLE `usbcData` FIELDS TERMINATED BY ',' LINES TERMINATED BY ' ';
LOAD DATA local INFILE 'MonthlyCSVDataFileforIndianaYouth.csv' INTO TABLE `usbcData` FIELDS TERMINATED BY ',' LINES TERMINATED BY ' ';

Any help would be nice. :)

If MySQL is running on `localhost`, you don't need the `LOCAL` option, since the local machine is the same as the server. You only need that when you're trying to load a file to a remote MySQL server. — Barmar, Jun 30 '15 at 18:10

score 0 · Answer 1 · answered Jun 30 '15 at 18:09

0

LOCAL keyword is not needed if you try to send your file through scripting language, such as PHP. Try to run your query without LOCAL.

If that doesn't work, try to copy your file somewhere and use the new path in LOAD DATA INFILE, and finally delete the file.

answered Jun 30 '15 at 18:09

Robo Robok

21,132
17
68
126

Removing the local keyword doesn't seem to help. Some of my original queries did not have the local keyword. But thanks for letting me know that local is not needed when you are using php to upload a file. I've also copied the file to /tmp/data.csv, and tried the query on it, but that failed as well :( – Joshua Jun 30 '15 at 18:31
Try to copy to some other folder, for example in your project root. – Robo Robok Jun 30 '15 at 21:04

score 0 · Answer 2 · answered Jul 01 '15 at 15:19

I finally got it working!!! I took Robo's advice and tried copying the file into my working directory with

$file_copy = getcwd()."/uploaded-file.csv";
copy ($file_tmp, $file_copy);

At first that didn't work, because Apache/php were running under the user "http", which did not have permission to write to my working directory. So I added the user http to the group joshua, because all of my directories under /home/joshua have the group joshua.

#add user http to group joshua
sudo gpasswd -a http joshua

Then I logged out and logged back in, just to make sure that the permissions were updated. Then I tried using this MySQL query to upload the correct file:

$sql  = "load data CONCURRENT infile \"$file_copy\" ";
$sql .= "into table `usbcData` ";
$sql .= "fields terminated by \",\" ";
$sql .= "lines terminated by \"\n\" ";
$sql .= "(first, last, usbc, year) ";
echo $sql."<br>";
$dbh->query($sql);

But when I ran the php script, the table usbcData table was not updated. I tried running the sql query in phpmyadmin and it gave me a permissions error. Apparently my MySQL implementation was running under the user "mysql", and it too did not have the correct permissions to access files that had the group joshua. So I added mysql to the group joshua

#add user mysql to group joshua
sudo gpasswd -a mysql joshua

I logged out and logged back in again, and it worked! Woo hoo! Thank God for stackoverflow, because I wouldn't have a job otherwise. P.S. Robo if I had enough rep, I'd vote up your answer.

upload a csv file to MySQL database via php

2 Answers2