Using LOAD DATA INFILE in PHP page

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
sparky753
Forum Commoner
Posts: 51
Joined: Fri Nov 03, 2006 10:39 am

Using LOAD DATA INFILE in PHP page

Post 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...
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Post 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" :) ?
There are 10 types of people in this world, those who understand binary and those who don't
sparky753
Forum Commoner
Posts: 51
Joined: Fri Nov 03, 2006 10:39 am

Post 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...
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Post by VladSun »

So ... do you upload the text file or it is already on the server?
There are 10 types of people in this world, those who understand binary and those who don't
sparky753
Forum Commoner
Posts: 51
Joined: Fri Nov 03, 2006 10:39 am

Post 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...
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Post 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)?
There are 10 types of people in this world, those who understand binary and those who don't
sparky753
Forum Commoner
Posts: 51
Joined: Fri Nov 03, 2006 10:39 am

Post 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...
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
sparky753
Forum Commoner
Posts: 51
Joined: Fri Nov 03, 2006 10:39 am

Post 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...
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Post 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
There are 10 types of people in this world, those who understand binary and those who don't
sparky753
Forum Commoner
Posts: 51
Joined: Fri Nov 03, 2006 10:39 am

Post 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...
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Post by VladSun »

Hm, I do not understand this guy - you still have to upload the file ... why is this double work?
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply