[RESOLVED] Syntax Error using LOAD DATA FILE

edziffel

Trying to upload some cvs files to a database table where the server is local.

CVS files are already uploaded and called using session variable that evaluates to /tmp/phpC0QUKw
Created a very small cvs file using open office calculate which works fine as my application parses the first 100 rows to the monitor.

MySQL is already connected using mysql_connect and the database is already selected using mysql_select_db. So far so good.

Can not get the syntax right for the following:


<?php  

$uploadalready="LOAD DATA INFILE '/tmp/phpC0QUKw' INTO TABLE 'TESTER_FEET' FIELDS TERMINATED BY ','"; 

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

?>

This works just well enough to generate the following error message:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''TESTER_FEET' FIELDS TERMINATED BY ','' at line 1.

Checked the manual, various tuts etc.

Have tried a number of different combinations of quotes, parenthesis, etc and have only managed to change the error message and or generate a completely blank page.

What am I doing wrong?

bradgrafelman

Identifiers (names of databases/tables/columns/views/etc.) are optionally surrounded by backticks in MySQL (or double quotes, if STRICT mode is enabled), not single quotes (which always denote strings).

edziffel

Thanks bradgrafelman

Was pretty sure that I tried that already but did again.. and blah blah but while was working on it just as was about to reply and checked the MySQL manual once again and it turns out that what is called for is back tics on the file to upload and nothing around the table.

$uploadalready="LOAD DATA INFILE '/tmp/phpC0QUKw' INTO TABLE TESTER_FEET FIELDS TERMINATED BY ','";

Now I'm getting a brand new error: Can't get stat of '/tmp/phpC0QUKw' (Errcode: 2)

Tried the double quote thing with that and got a blank page, which doesn't mean that it still doesn't need it and something else as well.

going to look up errcode; 2 and define call full path to upload file to test LOAD DATA INFILE. will get back with more info.

bradgrafelman

edziffel wrote:
what is called for is back tics on the file to upload

Nope; single quotes are required around the file name, since it is, after all, just a string.

edziffel wrote:
and nothing around the table

Assuming you didn't use one of the MySQL Reserved Words, then yes, identifiers need no delimiters, hence why I said they are optionally surrounded with backticks (or double quotes if strict SQL mode is enabled).

edziffel wrote:
Now I'm getting a brand new error: Can't get stat of '/tmp/phpC0QUKw' (Errcode: 2)

New errors are at least indicative of progress. :p

The 'Errorcode: 2' bit is the most helpful there, as that's an O/S error code that should correspond with (depending upon OS, of course) an error called 'ERROR_FILE_NOT_FOUND'. It's name is fairly self-explanatory.

So, two questions:

Where did you get that filename? That is to say, are you sure that path is correct?
Is your MySQL server running on the same server as PHP? For example, when connecting to MySQL, do you use 'localhost' as the hostname or some other server's name?

The point here is that supplying a local file path to a remote server isn't going to do that server any good, since MySQL can't magically leap over to the server where PHP is running, grab the local file, and then leap back to its remote home. If this is the case, you'll want to do a 'LOAD DATA INFILE LOCAL' query, which basically tells the MySQL client library to read in the local file and transfer it to the remote MySQL server across the DB connection.

edziffel

Originally Posted by edziffel
what is called for is back tics on the file to upload
Nope; single quotes are required around the file name, since it is, after all, just a string.

Have no idea what a back tic is. Thought you meant apostrophe/single quote. So, what is a back tic anyway?

New errors are at least indicative of progress.

That's how I see it. :-) Was actually happier just to get it to generate the first error statement. You figure that at least your are on the right planet. lol

The 'Errorcode: 2' bit is the most helpful there, as that's an O/S error code that should correspond with (depending upon OS, of course) an error called 'ERROR_FILE_NOT_FOUND'. It's name is fairly self-explanatory.

Did not know that. Thanks again. Did treat it that way. See below.

Where did you get that filename? That is to say, are you sure that path is correct?

<form action="db_upload_post_to" method="post" enctype="multipart/form-data" >
<input type="file" name="fileupload" size="70"/>
<input type="submit" value="Upload"/>
</form>

Then

<?php 
	$dname=$_FILES["fileupload"]["tmp_name"];
	echo "The temporay file name for the uploaded file is ".$dname."."."<br/>";
	$_SESSION['csv_file_selected']=$dname;
?>

Was using session variable then hard coded temp file path and location, then tried to use absolute path to file hard coded in an effort to eliminate possibilities and isolate one issue at a time.

Is your MySQL server running on the same server as PHP? For example, when connecting to MySQL, do you use 'localhost' as the hostname or some other server's name?

Running a linux Ubuntu 10.04 lts with lamp, phpmyadim using bluefish as a text editor. Also using codeigniter as a framwork, although what I'm finding is that for doing a single app, it is quicker to just use functions than to try digging way deep into their system to get what you need. That said I am using some of their functionality to keep all the calls to new pages in a controller file. While not ruling out the possibility of a problem there, it is very unlikely as again I'm not using their prepackaged functions. So the PHP, MySQL and Apache server are all on the same local machine with localhost as the root URL: http://localhost/what_ever/even_more.

The original file is on a thumb drive, which winds up being a mounted partition... I think. Was planing to move it to another location on the main drive in a further "spray and pray" apporach. lol. The temp file /tmp/phpC0QUKw is in the server tmp file default directory. Not sure if the temp file would be any good even if it was being located; not sure it is still in a fomat, CVS, say, that that the LOAD DATA INFILE can read. Was looking at the config files but did not see where to change the default temp directory. Again I tried to substitute with absolute path /media/thumbdrive/orginal)_file, with single quotes.

Did try to use the LOAD option. Same results, which again does not rule out that using it might be right with something else missing.

bradgrafelman

Er, wait a sec - why are you storing the path to the temporary file in a session variable? It's not going to do you any good except for the duration of the script processing the file upload; after it's finished executing, PHP will automatically delete the temp file since it assumes you've already processed it.

EDIT: Also, a backtick on a standard U.S. English keyboard is the key to the left of '1' and looks like: ` (In other words, it's a tick mark that's leaning backwards 😉)

edziffel

Er, wait a sec - why are you storing the path to the temporary file in a session variable? It's not going to do you any good except for the duration of the script processing the file upload; after it's finished executing, PHP will automatically delete the temp file since it assumes you've already processed it.

Initially yes. But since that didn't work, then tried to hard code absolute path to original CVS file on my thumb drive, which is where the original file was uploaded from. Which raises another couple of quesions:

If the temp file scope is only on the page it is posted to, how would you access any of its data going forward? Was hoping to use it to post data to a table in a database;
If the objective is to use a web interface to read data to a table, is this the right approach? IE
using a post form input type=file, then select database from existing databases, then parse tables from selected database then upload or should it be select db, select table, then upload? Or is there yet another approach I just don't know about?

Don't have a lot of exp with php or mysql. Still got to pay my dues and look stupid for a bit. How else?

One post I saw was inquiring about permissions. Just throwing that out.

Derokorian

Its on the same key as the tilde ~ =D Looks like an angled apostrophe

edziffel

Yeah but of course! Thanks
Also thanks for the tip about using the thread tools menu. Was wondering how that worked.

bradgrafelman

edziffel;10985557 wrote:
then tried to hard code absolute path to original CVS file on my thumb drive

Careful - make sure MySQL has permissions to access that folder/file. The file would probably need to be readable by all and the folder (and possibly any folder above it?) at least readable/executable by all.

If it's a permissions issue, however, you should hopefully get a different error message indicating this is the case.

edziffel;10985557 wrote:
1. If the temp file scope is only on the page it is posted to, how would you access any of its data going forward?

You'd either use [man]move_uploaded_file/man to move it to a temporary location and [man]unlink/man it when you're finished with it, or simply do all of the necessary processing on the temp file before the script receiving the file upload terminates.

edziffel;10985557 wrote:
Was hoping to use it to post data to a table in a database;

If that's true, then make sure you execute the 'LOAD DATA INFILE' query in the same script that's receiving the uploaded file.

edziffel;10985557 wrote:
2. If the objective is to use a web interface to read data to a table, is this the right approach? IE
using a post form input type=file, then select database from existing databases, then parse tables from selected database then upload or should it be select db, select table, then upload?

That last approach might be logically more beneficial, since you wouldn't have to upload the file until it's actually needed. Otherwise, you'd probably have to move the file to a new temporary location and preserve it until it's been used.