Posted by nsh on 11/22/05 16:43

mailing.database.mysql, comp.lang.php
subject: does "LOAD DATA" EVER work?!? I've tried EVERYTHING!

version info:
my isp is running my web page on a linux box with php ver. 4.4.1
according to phpinfo, the "mysql api client is ver. 4.0.25" - I have no
idea how this relates, if at all, to the mysql engine's version.


background:
I have tried literally everything I can think of, and I can NOT get
load data infile to work under php AT ALL!!!

I have scoured all the newsgroups, and found countless plea's for help
on this subject. NONE of them seem to adaquately answer all my
questions; most suggest trying stuff I've long since tried and failed.

I have actually read the freaking mysql manual, and from the
description, it SOUNDS like I SHOULD be able to transfer a csv file
from my window's pc, to the web server's mysql database, and import it
into a table in 1 single command. I do realize this is a security risk,
and therefore can't be done this way. Someone should re-write the
manual's description of the load data command. The manual refer's to
"client software" - but it never explains what the <blank> it is - so
therefore I assume it to be my web browser :) - afterall, that is the
client I'm using! (yes, there is a reference to mysql client software,
but I can't put that on my users' pc's, nor do I or should I [feel] I
should have to do this).

The csv file is exported from ms access. I manually ftp the file to the
web server.
Here's the command I've been trying hundreds of variations on:

--
LOAD DATA CONCURRENT LOCAL INFILE '/full_server_path/myfile.csv'
REPLACE INTO TABLE `intemp` FIELDS TERMINATED BY ',' OPTIONALLY
ENCLOSED BY '|' LINES TERMINATED BY '\n';
--

(Some fields use double quotes within the field, so I closed the fields
in the pipe symbol. My test file doesn't have any quotes, and I've
proven that the pipe works.)
The full_server_path is determined with php's "getcwd" function.


MY questions:
1) Is there SOME way that this can be done as I interpreted the mysql
manual? Is there a way to upload, and import a file from my pc to the
web server - WITHOUT the use of any special software? (I have users
that will update the database, and they will have nothing more than ms
access, and IE installed).

2) I'm ASSUMING the answer to #1 is No. So obviously I must get the
file to the server somehow, then load data. I've done exactly this in
my testing. after it's ftp'd, there's NO WAY I can get mysql to import
the darn file (thru php).
** - If I use phpmyadmin, and insert the above command into a sql
window & execute it - IT WORKS PERFECTLY!!
- If I use the EXACT same command in php (see sample), I GET
NOTHING! I get NO error messages, and I NEVER get a successful import.
SAMPLE:
__
$qry="LOAD DATA CONCURRENT LOCAL INFILE '/full_server_path/myfile.csv'
REPLACE ";
$qry.="INTO TABLE `intemp` FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED
BY '|' LINES TERMINATED BY '\n'";
$qry.="LINES TERMINATED BY '\\n';";
$rslt=mysql_query($qry,$db) or die(mysql_error());
--

so I guess the question for #2 is - what am I doing wrong?

3) Considering I've spent COUNTLESS hours trying to solve this - on
MULTIPLE occasions;
I'm lead to ask, does "load data" REALLY even work, and why does it
seem so hard to find working examples?

oh - by the way - I've tried relative path's, absolute paths, using
"LOCAL", not using it; and dozens of other permutations.

I'm hoping someone can finally shed full light on the issues with LOAD
DATA...
TIA

[Back to original message]