using mysql LOAD statment in PHP fails, but doing it via command line works

Question

I have a php web page that let's a person upload a file. I want to extract the file name from $_FILE and then load all the data into my database.

My code looks like this:

 $myfile = $_FILE['file']['tmp_file'];
 $executebatchloadsql = 'LOAD DATA LOCAL INFILE "'. $myfile .'" INTO TABLE testtable (fname, lname);
 mysql_query($executebatchloadsql) or die(mysql_error());

But the error message I'm getting says:

The used command is not allowed with this MySQL version

My questions are as follows

Am I supposed to use the tmp_file name or the name?
What do I need to do to get the load data command to work? I've tried to follow the post found at MySQL: Enable LOAD DATA LOCAL INFILE but it's still giving me the same error.

EDIT 1:

This is what my /etc/mysql/my.cnf looks like in part:

[mysqld]

user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
local-infile

The last line is what I added. I also added the same line to the [mysql] section:

[mysql]
local-infile

After making these changes, I restarted sql:

/etc/init.d/mysql restart

But I'm still having the problem

EDIT 2:

I've also tried local-infile=1 as per the same post I mention above. But that doesn't work either.

EDIT 3

I've tried to add the FILE prvil to the user.. like so:

GRANT FILE ON *.* TO root;

and then when i showit grants for root, i can see that its been added. but i'm still getting an error message that the used command is not allowed on my version of mysql. Do I need the "=1" after the infile in my my.cfg file?

EDIT 4:

As a further test, i tried loading the file manually via command line instead of via php and it accepted it. I logged in as root (which is the same id i'm using my app for testing purposes) and then I tried the following command:

mysql> load data local infile '/var/www/testwebsite/abc.csv' into table testtable;
Query OK, 3 rows affected, 48 warnings (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 48

But I can't seem to get the PHP code working. Thanks.!

what version of mysql are you using, and are you using mysql_, mysqli_ or pdo? — Ascherer
– Ascherer, Commented Jan 15, 2013 at 22:39
Does your server allow LOAD DATA and is it configured to do so? — Kermit
– Kermit, Commented Jan 15, 2013 at 22:40
Make sure your MySQL user has the FILE privilege granted. There's also a chance your hosting provider has blocked you doing this command if you're using shared hosting. — hohner
– hohner, Commented Jan 15, 2013 at 22:42
Please see my updated comments next to question 2. also, mysql version is 5.5.28. Also, I'm using mysql_connect() which I guess means I'm not using mysqli or pdo... — dot
– dot, Commented Jan 15, 2013 at 22:47
As Jamie suggested exec command SHOW GRANTS FOR username; and see if the user has GRANT FILE ON *.* OR GRANT ALL PRIVILEGES ON *.* with mysql or whatever IDE you use (phpAdmin, Sequel Pro...) — peterm
– peterm, Commented Jan 15, 2013 at 23:06

MaX · Accepted Answer · 2013-05-22 12:30:40Z

2

After going from MySQL 5.0 to 5.5 I found out that I suddenly have to enable LOCAL INFILE specifically when creating the connection in PHP.

Using mysql:

mysql_connect(server,user,code,false,128); // 128 enables LOCAL INFILE
mysql_select_db(database);

Using mysqli:

$conn = mysqli_init();
mysqli_options($conn, MYSQLI_OPT_LOCAL_INFILE, true);
mysqli_real_connect($conn,server,user,code,database);

answered May 22, 2013 at 12:30

MaX

1,81514 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

using mysql LOAD statment in PHP fails, but doing it via command line works

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related