[RESOLVED] Can not open file using LOAD DATA INFILE

[edziffel]

Am starting new thread as old one wandered a bit and would be hard to focus on current state of problem.

Have done a flle upload using post, input type=file then did a file move using move_upload_file to a created directory in the current working directory called temp_uploads, drawback.com/temp_upload, where I can browse to and see the file. Using $_FILE attributes I can parse the file. All is well so far.

Then have a page to select a database from the server and save it as a session variable
$SESSION['database_selected'] and a link to the next page where a table in that database is selected and stored in another session variable, $SESSION['database_table_selected'].

Then the file upload is done and the path to the file is saved as another session variable $_SESSION['target_path']

As a test, they are all echoed out just before trying to doing a mysql_connect, and a mysql_select_db, all of which have fail and succeed output after running. So far so good.

The out put for the session variables just before tying to load data is
Path = temp_uploads/tester_feet_CVS.csv
database = drawback
table - TESTER_FEET

The current working directory is checked just before trying to execute the LOAD DATA INFILE

echo getcwd()."<br/>";

evaluates to /var/www/drawback.com

Next the code I have for the LOAD DATA INFILE

$uploadalready="LOAD DATA INFILE 'temp_uploads/tester_feet_CVS.cvs' INTO TABLE TESTER_FEET FIELDS TERMINATED BY ','";

mysql_query($uploadalready) or die(mysql_error());

which is returning

Can't get stat of '/var/lib/mysql/temp_uploads/tester_feet_CVS.cvs' (Errcode: 2)

so made a copy of the file temp_uploads and put in in /var/lib/mysql/ so that you can see the path exactly like is is displayed when you can see the tester_feet_CVS.cvs file. Which is were it was when it gave this result

What can I try to get this working?

[bradgrafelman]

The problem is that the current working directory of the PHP script is irrelevant; MySQL can't (and doesn't) know where your script is executing from.

Instead, you need to pass the full path to the file in the 'LOAD DATA INFILE' query. [man]realpath/man can help you out in that regard.

EDIT: Then again, if you already know the absolute path (e.g. if you have the path relative to the root of your website, use $_SERVER['DOCUMENT_ROOT']), then there's no need for realpath().

[edziffel]

Thanks bradgrafelman,

Really appreciate the feed back was running out of ideas. Did try every which path like /var/www/drawback.com/temp_uploads, etc. etc,. Totally did the spray and pray. lol

Will throughly check out all of the above and post up how it goes. :-)

[edziffel]

bradgrafelman,

Did some homework but still not clear on how to proceed (newbie stuff no doubt).

No doubt you are already sure for the situation, but I still have that look on my face so wanted to be certain about some repeat stuff...

1. Where the uploaded and moved file is on the machine
   [INDENT]a. The temp_uploads directory was created by me in the www/drawback.com/ directory using nautilus as root, meaning that the file was created via the default file browser and to eliminate issues was given universal permissions (chmod 777 using a terminal interface )
   [/INDENT]
   [INDENT]b. Can just browse to the folder and see what ever files I just uploaded and moved. Just to make sure, deleted the files using a file browser and did it again. Upload and move working just like it is supposed to.
   
   [/INDENT]
   [INDENT]c. Know exactly what the absolute path on the machine is. It is /var/www.drawback.com/temp_uploads/file (is "file" the right way to indicate a generic file?). This is the path that is displayed when uploading the file, the exact same file as uploaded and moved, using phpmyadmin, which I did to make sure there were no problems with the csv file created with open office. Uploading from phpmyadmin works but you have to specify the delimiter, return character, and specify the field names. Was first trying with out extra options in my web app looking for the same error messages as phpmyadmin gave me before adding the specifications.
   
   [/INDENT]

Ok back to what you were saying...

2. If I get what you are saying is that when MySQL is running, it is evaluating the query in a mystery-meat location and therefor the reference to the file needs to be starting from the directory that MySQL is running in. So...

3. Did look up $_SERVER["DOCUMENT_ROOT"]. Says that it will return the "The document root directory under which the current script is executing, as defined in the server's configuration file". Not sure how to use it. The complete php code for where I'm using the LOAD DATA INFILE is

   [<?php
   echo getcwd()."<br/>";
   $uploadalready="LOAD DATA INFILE 'temp_uploads/tester_feet_CVS.cvs' INTO TABLE TESTER_FEET FIELDS TERMINATED BY ','";
   
   mysql_query($uploadalready) or die(mysql_error());
   
   ?>/CODE]
   Before executing the above code, have already run mysql_connect and mysql_select_db and I get that the echo getcwd() is not going to do the job.  But how do I use the $_SERVER["DOCUMENT_ROOT"] in this case?  IE, I put it where?, and add what arguments? and parse it how?  What I found online was not very clear.  
   
   4.  Pretty much the same same issues and questions for  realpath();

[edziffel]

bradgrafelman

I figured it out. Please check the extension of what should be the csv file in the load data statement. I've got cvs. Even though it appears that I'm functionally a dyslexic moron, I did learn a ton and trust me when I say I've got the LOAD DATA INFILE thing down. lol.

But could not have gotten there with out your help. Thank you very much.

Also with out the LOCAL, LOAD DATA INFILE assumes that the directory is the database directory or where the database tables are stored, so it actually would have worked when I pasted the temp_uploads directory into that file if I had the extension right.

The working code is

$uploadalready="LOAD DATA LOCAL INFILE '/var/www/drawback.com/temp_uploads/tester_feet_CVS.csv' INTO TABLE TESTER_FEET FIELDS TERMINATED BY ','";

mysql_query($uploadalready) or die(mysql_error());

Again thank you very much!