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:
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...
Not necessarily automate ...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...
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...
<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
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...
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
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...
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:
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...