Inconsistant MySQL login

[xplorer]

I have a connection script to my database that I am reusing - it works on a couple of commands
this works:

$Result = mysql_query("SELECT `username`,`inbound_number`,`last_visit`,`history` ,`company`,`type` FROM `dealer`WHERE username='$username' and password ='$password'")or die(mysql_error());

BUT

this does not work

$query = "LOAD DATA INFILE '".$_SERVER['DOCUMENT_ROOT']."/CSVFiles/".$file."' INTO TABLE datatest FIELDS TERMINATED BY ',' LINES TERMINATED BY 'n' IGNORE 0 LINES ( Record_Number, Inbound_Number, Call_Type, Date_Time, Calling_Party, Answer_Point, Duration, Cost);" ;

$result = mysql_query($query)

in both cases mysql_query refers to the same connection script

I get this error

Invalid query: Access denied for user 'myDomain_myName'@'localhost' (using password: YES) in line

Which means my connectionscript is wromg - BUT it works in other cases....

Is there a limit to using LOAD DATA INFILE on this server???

thx

[charlesgan]

$query = "LOAD DATA INFILE '".$_SERVER['DOCUMENT_ROOT']."/CSVFiles/".$file."' INTO TABLE datatest FIELDS TERMINATED BY ',' LINES TERMINATED BY 'n' IGNORE 0 LINES ( Record_Number, Inbound_Number, Call_Type, Date_Time, Calling_Party, Answer_Point, Duration, Cost);" ;

likely error at above line.
i taking a while guessing cause i cant execute it...
the table name is "datatest".. what is the database name?
try "yourdatabase.datatest_table"

and make sure the connection you make have the access right to "yourdatabase.datatest_table"

[Seb]

Is there a reason you need to import data using a script? You might want to try doing it with phpmyadmin.

[eileen]

I have been having a similar problem, not using a script, but using the load command from the mysql prompt.

I did get the data to load using the phpmyadmin (which I was not even aware was there for this, until I read this thread).

So I thank you for mentioning that as an option, but am still wondering why I can not get the load command to work from the prompt.

[dsaronin]

you'll want to read this section in mysql manual for more details: 13.2.5. LOAD DATA INFILE Syntax ...
but the solution is (when using hostmonster) to use the 'LOCAL' option as follows:
"LOAD DATA LOCAL INFILE '/fullpathname/datafile.txt' INTO TABLE mytable LINES TERMINATED BY '\r\n' IGNORE 1 LINES (colname1, colname2, ..., colnameN;"

the reason you need this is because mysql is running as a server on a different box (probably) and your request is acting as a client (on a different box). So "LOCAL" tells the server to grab the file LOCALLY from the point of the requester. you need to fully qualify the name (/home/<username/etc/.../datafile.txt) so that it can find the file.
The way you (and I did also) originally structured the LOAD DATA INFILE, the server was looking in its own datastructure but you lacked permissions for that.

[forTheDogs]

Hi D: I do not know how to write the "complete" path to my file. It is located in public_html/xxxxx.csv


Would you give me the exact way to put the path in an sql statement? Thanks!

[r2b2]

Full path name will be /home/yourusername/public_html/ (then your file name)

[forTheDogs]

Hi! This is what I just tried:

LOAD DATA LOCAL INFILE '/home/xxxxxxxx/public_html/update3.csv'
INTO TABLE pedigrees
FIELDS TERMINATED BY ",";

I get a message that the file is not found - Error 13

Can anyone see what I have done wrong?

This is php to mysql db.

Thanks!!

[r2b2]

Sounds like the update3.csv file doesn't exist or isn't readable... You sure its /home/xxx/public_html/update3.csv and not just /home/xxx/update3.csv?

[forTheDogs]

After a week of trying without success to upload 1000 records from a csv file using
LOAD DATA LOCAL FILE

I tried the "import" tab in phpmyadmin
browsed to the csv file on my home pc
selected csv for the type
"," for the field separation
clicked "go"

and it worked like a charm.

Thanks to all for trying to help me any way!!!