Page 1 of 1

Using LOAD DATA INFILE in PHP page

Posted: Tue Aug 07, 2007 4:58 pm
by sparky753
I've got a bunch of text files with fixed-width data in them on a server. If i run the following command in MySQL, it inserts every row of data from the text file into the MySQL database table tblData:

Code: Select all

load data local infile '/etc/test.txt' into table tblData FIELDS ENCLOSED BY '' TERMINATED BY '';
Is there a way i can set up a form on a PHP page where the user would enter the name of the text file (in this example, 'test.txt'), submit it and that would send the above statement to MySQL?

I know, if i can pass the statement to MySQL just like a regular query, i should be able to pass the name of the text file as a parameter too...

Please help...

Posted: Tue Aug 07, 2007 5:03 pm
by VladSun
Are you trying to do some batch working? I.e. you want to automate operations on your "bunch of text files with fixed-width data" :) ?

Posted: Wed Aug 08, 2007 8:16 am
by sparky753
Not necessarily automate :wink: ...I want to be able to enter the text file name on a PHP form, click a button and run the MySQL LOAD DATA INFILE statement to insert the records from that file...

Posted: Wed Aug 08, 2007 4:59 pm
by VladSun
So ... do you upload the text file or it is already on the server?

Posted: Wed Aug 08, 2007 8:23 pm
by sparky753
The text file is already on the server. I know exactly what you're thinking - i had toyed with the upload idea but i realized that would have a host of problems. So i've decided to leave the text files on the server. then i'm going to try to use this PHP page to run the MySQL command to do a bulk insert...

Thanks for getting back to me...

Posted: Wed Aug 08, 2007 9:50 pm
by VladSun

Code: Select all

<html>
<body>
<?php

if (empty($_POST['action']) || $_POST['action'] != 'do_insert')
{
	?>
	<form action='' method='post'>
		<input type="text" name="file_name">
		<input type="hidden" name="action" value="do_insert">
		<input type="submit" value="Insert File!">
	</form>
	<?php
}
else
{
	$file_name = $_POST['file_name'];
	// connect to mysql and "load data local infile '/etc/test.txt' into table tblData FIELDS ENCLOSED BY '' TERMINATED BY '';" goes here
	// query should be:
	// "load data local infile '/etc/".$file_name."' into table tblData FIELDS ENCLOSED BY '' TERMINATED BY ''"
	// if you files are in /etc/
}
?>
</body>
</html>
Keep in mind that $file_name is not sanitized - it's your homework :)

Still wondering why you want to do this via web instead doing it directly into the server console (or by bash script)?

Posted: Thu Aug 09, 2007 11:40 am
by sparky753
Thank you VladSun...i'll give this a shot. Ah, you bring up a very valid question. I used to do it using a bash script but I'm setting this up for someone else to do. that user wants an interface that he can use to upload these text files without having to use the shell...

Posted: Thu Aug 09, 2007 11:56 am
by VladSun
I have a similar application:
there are two servers - the first one (a router) copies a tab delimited file (traffic usage per IP) to the second one (www,db server) via scp. Then a cron job on the second server imports this file into MySQL and (optionaly) moves it to an archive directory or deletes it.

That should also work in your case - you'll have a small delay (as defined in crontab) between uploading file and having its data in the MySQL DB. Thus the only action required is to upload the file into the specified sirectory.

Posted: Fri Aug 10, 2007 9:12 am
by sparky753
I'm probably going to go with your last suggestion.

Earlier, I created a form based on the lines of PHP code you sent me and it works but the only problem is, i don't get any feedback to let me know if the insertion took place or not. What would you suggest for that? Thanks so much for everything, Vladsun...

Posted: Fri Aug 10, 2007 9:18 am
by VladSun
The crontab will automatically send you an email if there is an output to STDERR. You may also redirect STDOUT to STDERR to have all messages generated by the script:

Code: Select all

/usrl/local/sbin/mysql_loadf .........    1 > &2

Posted: Fri Aug 10, 2007 9:32 am
by sparky753
Is it possible to send an error message to the PHP page? This guy i'm working with seems hell bent on using the form...starting to annoy me actually because i loved your idea of setting up a cron job and doing it through the shell...

Posted: Fri Aug 10, 2007 9:37 am
by VladSun
Yes - you'll just have to output the mysql_error:

Code: Select all

mysql_query($query,$connect) or die('Error, import failed!'); 
echo 'File imported!';
in the else block in the code above.

Posted: Fri Aug 10, 2007 10:18 am
by VladSun
Hm, I do not understand this guy - you still have to upload the file ... why is this double work?