load .csv into mysql problem

[activeco]

Is there anything wrong with this line?

$result = mysql_query('LOAD DATA LOCAL INFILE "file.csv" INTO TABLE table1 FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY "\"" IGNORE 1 LINES') ;

note:first line are description fields

When run, it does nothing!

If I issue similar command from shell using mysql client:
LOAD DATA LOCAL INFILE 'file.csv' INTO TABLE table1 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' IGNORE 1 LINES ;

it loads the file correctly.

I assume it has something to do with use of quotes, but up to now whatever I tried didn't work (from php).

[dreamcatcher]

Try using mysql_error to display an error when the query fails:

$result = mysql_query('LOAD DATA LOCAL INFILE "file.csv" INTO TABLE table1 FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY "\"" IGNORE 1 LINES') or die(mysql_error());

That might give you a clue. :)

dc

[activeco]

Yes, it was already there but deleted, thanks.
This is the message:"The used command is not allowed with this MySQL version" ;

which is strange, as I use the same command from mysql client.?

[activeco]

Obviously "LOCAL" should go out of that command when making a cron job on the server.

When I did that, the server responded: "Access denied for user: '...@localhost' (Using password: YES)".

All credentials were OK.
I heard that "LOAD DATA INFILE" could be disabled on some servers.
Could that be a problem?

[mcibor]

How are you logging into mysql via php and via shell?

Maybe you didn't give yourself permision to do LOAD on the php user? If not, then use GRANT from shell - this may help

If that's not the problem, then I don't know, sorry
Michal CIbor

[activeco]

Here is the PHP code:

<?
$usr = "db_user";
$pwd = "pass";
$db = "db_db1";
$host = "localhost";

$cid = mysql_connect($host,$usr,$pwd);
if (!$cid) { print "ERROR: " . mysqlerror() . "\n"; }

$select=mysql_select_db($db, $cid);

$delete_old=mysql_query('DELETE FROM table1');

$ld=mysql_query('LOAD DATA INFILE \'file.csv\' INTO TABLE table1 FIELDS TERMINATED BY \',\' OPTIONALLY ENCLOSED BY \'"\' IGNORE 1 LINES') or die(mysql_error());

$cl=mysql_close();
?>

/////////////////////////////////////////////////

The working shell command is almost exactly the same, without mysql part of course, except I added LOCAL (load data local infile...) for using my remote, local PC file.

Right now I setup the cron job for updating db from my local pc, but I am not much happy with that solution.