Need help with Files

pmconaway

Ok, here is what I'm trying to do.

I have a php script that needs to read files in a directory and import them into a MySQL table. I have pasted the code I have so far below.

The function builds the correct SQL statement but doesn't execute it correctly. My guess is that I'm not actually connecting to the file itself. Just displaying the name. Any help is greatly appreciated.

Paul
function dir2() {
$linkID = @mysql_connect("localhost","*","*");
@mysql_select_db("test", $linkID);
echo "Function dir2<br>\n";
$path = "/test";
If ($handle = opendir($path)) {
echo "Directory handle: $handle\n";
echo "Files: \n";

while (false !== ($file = readdir($handle))) {
	if($file != "." && $file != "..") {
	$findme = ".csv";
	$pos1 = strpos($file, $findme);
	If ($pos1 > 0) {
		$filename = $path . "/" . $file;
		$fileinfo = file_get_contents($filename);
		$querystring = "LOAD DATA INFILE '$filename' in TABLE 'districtinfo' FIELDS TERMINATED by ',' ENCLOSED by '\"' ESCAPED by '\\\\' LINES TERMINATED by '\\r'";
		print $querystring;
		$result = @mysql_query($querystring,$linkID);

		print "<br>Result = $result<br>";
	//	echo "<------- $file<br>\n";
	}
	}
}
closedir($handle);
@mysql_close($linkID);

}

stolzyboy

take that @ off of this line

$result = @($querystring,$linkID);

so you can see errors that mysql is trying to tell you, the @ surpresses errors of functions

pmconaway

Nothing happens. this is the output I get on the web browser

LOAD DATA INFILE '/test/mysqlexport1.csv' in TABLE 'districtinfo' FIELDS TERMINATED by ',' ENCLOSED by '"' ESCAPED by '\' LINES TERMINATED by '\r'
Result =

No error messages appear.

stolzyboy

oops, change this

$result = @($querystring,$linkID);

to this

$result =@($querystring,$linkID) or die(mysql_error());

pmconaway

Ok, here is the 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 'in TABLE 'districtinfo' FIELDS TERMINATED by ',' ENCLOSED by '"

When I use phpmyadmin to do the file import the sql that was generated was:

    [B]LOAD  DATA  INFILE  '/var/tmp/phpkQoLHg' INTO  TABLE  `districtinfo`  FIELDS  TERMINATED  BY  ',' ENCLOSED  BY  '"' ESCAPED  BY  '\\' LINES  TERMINATED  BY  '\r'     [/B]

Now the file I selected was '/test/mysqlexport.csv' Which is the same file that my script chooses. The 'var/tmp/phpkQoLHg' variable has me thinking that something in what I setup isn't linking quite right. Also running the insert manually via phpmyadmin works and insert records correctly.

Paul

stolzyboy

take a look at [man]basename[/man] and see if that will help you at all to get the REAL name of the file instead of the temp name

pmconaway

Ok, I put this in earlier to make sure that I could actually get the file contents displayed when I printed them.

$fileinfo = file_get_contents($filename);

$filename = basename($file);
// $filename = $path . "/" . $file;
$fileinfo = file_get_contents($filename);
$querystring = "LOAD DATA INFILE '$filename' in TABLE 'districtinfo' FIELDS TERMINATED by ',' ENCLOSED by '\"' ESCAPED by '\\' LINES TERMINATED by '\r'";

If $filename = $path . "/". $file; I get the results displayed. Now with that commented out and the basename($file) defining $filename the file_get_contents($filename) line errors outs with file_get_contents(mysqlexport.csv): failed to open stream: No such file or directory in /Library/Apache2/htdocs/phpinfo.php
So I think it worked better before. 🙂

stolzyboy

if you echo out $filename with the basename line, what do you get, i have a feeling you are having a path issue

pmconaway

the echo returns
mysqlexport.csv

Which is the file in question. With no path to where it is at.

stolzyboy

ok, so when you do the get contents, you need to get the correct path to that file, either in relation to the script, or the full path from root down to the file, so that error doesn't come up, then once you have that the query should work correctly

pmconaway

Mysql still doesn't like it for some reason. I'm back to the 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 'in TABLE 'districtinfo' FIELDS TERMINATED by ',' ENCLOSED by '" error. 🙁

stolzyboy

and if you echo out the query, and cut and paste that into phpmyadmin, it works fine? that is odd...

pmconaway

No actually I get the same error if I cut and past.

It only works if I follow through with the PHPmyadmin screens to manually insert records via a file. and that is where the

LOAD DATA INFILE '/var/tmp/phpkQoLHg' INTO TABLE districtinfo FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\' LINES TERMINATED BY '\r'

sql comes from. now in both cases I choose the file /test/mysqlexport.csv file.
I my question is what the h*ll is '/var/tmp/phpkQoLHg' file my guess is that it is something that phpmyadmin sets up to transfer the file from where I was browsing it.

stolzyboy

the temp file is just like when you upload a file, it sets a temporary place to store the file until you do what you actually want to do with it, i am somewhat perplexed on what the underlying problem is

pmconaway

I guess so am I. For me it comes down to why does the tempfile work when I use the phpmyadmin client to upload the file. But when I use the same code and substitute my filename it doesn't work. Ive been programming for 15 years but I am new to php so this has me perplexed. The only thing that I came up with overnight was to try moving the file to a different directory and see if it was a permissions problem. But I don't think that is the problem.

Thanks for you help.

Paul

pmconaway

Ok, I have made some progress with the my earier problem. Here is what I am getting now.
LOAD DATA LOCAL INFILE '/Library/Apache2/htdocs/mysqlexport.csv' into TABLE districtinfo FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\' LINES TERMINATED BY '\r'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 '\r'' at line 1

For the life of me I can't find the syntax error. Any help would be greatly appreciated.

Paul

stolzyboy

Is everything being escaped, terminated, enclosed by the correct things...?
Can you run this one in phpMyAdmin and get no error?
Perplexed, if 1 = yes && 2 = no.

pmconaway

To answer your questions.

Yes, everything is terminated as stated in the SQL statement.
No- it still doesn't work in PHPmyadmin I age the same error.

The only thing that is different in the phpmyadmin statement(that works) is that the table name districtinfo is enclosed in slanted single quotes. In my php code I removed the single quotes.
Should I try putting them back in. I got an error message before when I tried to leave them on. I don't remember what it said. The code got less errors when I removed the single quotes.

stolzyboy

yeah, give it a whirl, but instead of regular 'single quotes', use single back quotes

pmconaway

How do I do singular back quotes? I couldn't figure it out before that is why I removed them when it was giving me errors the first time.

Paul