does "LOAD DATA" EVER work?!? I've tried EVERYTHING!

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
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.

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'

(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.
$qry="LOAD DATA CONCURRENT LOCAL INFILE '/full_server_path/myfile.csv'
$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

Re: does "LOAD DATA" EVER work?!? I've tried EVERYTHING!

I wonder if you are being caught out by different sorts of single  
if you cut the code from phpMyadmin that it gives when exporting and  
look _carefully_ are the quote characters identical.  (I never knew  
there were two single quote characters before.)

If I was you I'd write a line of data by hand 'as if' exported from  
ACCESS and see if it can be LOADded.  Then look at what an export of an  
existing record looked like from mySQL/phpMyAdmin.  In an ideal world  
they should be identical.  Then start knocking out the differences.

PETER FOX Not the same since the deckchair business folded
2 Tees Close, Witham, Essex.
Gravity beer in Essex  <

Re: does "LOAD DATA" EVER work?!? I've tried EVERYTHING! wrote:
Quoted text here. Click to load it

This directive can appear only once. The last one with two backslashes is
correct (because the string is in double quotes).

E. Dronkert

Re: does "LOAD DATA" EVER work?!? I've tried EVERYTHING!


   > does "LOAD DATA" EVER work?!? I've tried EVERYTHING!

Okay - Now calm down!

   > ** - If I use phpmyadmin, and insert the above command into a sql
   > window & execute it - IT WORKS PERFECTLY!!

There's you - answering your own question.

It will work *every time* you throw a properly formed LOAD DATA command at
And obviously, you haven't tried everything everything after all!

    To troubleshoot, I might suggest -

That you dump (and examine!) the exact contents of $qry as it exists just
before you pass it to mysql_query().  You might compare it *very carefully*
with the string that you know works when you pass it to mysqladmin.

    Another thing that comes to mind -

Are you working with the same usr/pwd/permissions in mysqladmin that you are
working with in your PHP code.  It's possible you have the FILE privelege
under mysqladmin that you lack from inside your PHP code.

Post the exact contents $qry here (not your PHP assignment code!) if
something above doesn't smack you in the face with the error of your ways

-Thomas Bartkus

Site Timeline