Inserting text files in to the databse - PHP, MySQL(PHPMyAdmin)

I have 3 text files at three different locations in the same drive, these text files contains some alerts as a resultant of few scripts. 1 script generates 1 text files.

Below is the format of alerts in the text file -

4/10/2010 4:05:04 AM,ServerName,0
4/10/2010 4:05:04 AM,ServerName,0
4/10/2010 4:05:04 AM,ServerName,0
and so on

I have a table in MySql Database(PHPMyAdmin), say DB Name - dbo_alert and Table Name - tab_alert
there are 3 Columns in the table - Server_Name, Date_Time, Status

Now i have to create a script which suppose to fetch all the data from the text file and insert the same data in to the database under relevant column.



pradeep_bansalAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Om PrakashCommented:
Please check the similar example
http://www.php-mysql-tutorial.com/wikis/mysql-tutorials/uploading-files-to-mysql-database.aspx
http://www.webmasterworld.com/forum88/2645.htm
Example
CREATE TABLE `friendstel` ( 
`id` bigint(6) NOT NULL auto_increment, 
`tel` varchar(10) NOT NULL default '', 
PRIMARY KEY (`id`) 
) TYPE=MyISAM AUTO_inCREMENT=1 ; 

<? 
$sql_static = 'inSERT inTO friendstel (id, tel) VALUES(NULL, '; 
$fp = fopen ("mitch888.txt", "r"); 
while (!feof ($fp)) {  
	$content = fgets( $fp, 4096 );  
	$sql = $sql_static . $content .')';  
	// echo "$sql<br />";  
	mysql_query($sql); 
} 
fclose ($fp); 
?> 

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
phpsalesCommented:
Change text file name and MySQL information below as needed.


//connect to mysql
$conn = mysql_connect("host","username","password") or die(mysql_error());
mysql_select_db("databasename",$conn) or die(mysql_error());

//open file
$filepath = "/tmp/inputfile.txt";
$handle = fopen($filepath, "rb");
if($handle){
	//for each line in file
    while(!feof($handle)){
		//read line into buffer
        $buffer = fgets($handle, 4096);
		//split by comma into array
		$data = explode(",",trim($buffer)); //e.g. array("4/10/2010 4:05:04 AM","ServerName","0");
		//build SQL
		$sql = sprintf("INSERT INTO tab_alert (Date_Time,Server_Name,Status) VALUES ('%s','%s','%s');", $data[0], $data[1], $data[2]);
		//execute query or die with error message if issue
		$result = mysql_query($sql, $conn) or die(mysql_error());
    }
    fclose($handle);
}

Open in new window

0
flytox06Commented:
Hello,

If you can run LOAD DATA INFILE statement somehow (shell script or through PHPMyAdmin) I'd advise you to do it. Try something like :

LOAD DATA INFILE '/path/to/yourfile.csv' INTO dbo_alert.tab_alert (Server_Name, Date_Time, Status) FIELDS TERMINATED BY ',';

of course you can run it on PHP. It's the fastest way to load external data. That will require you have FILE grants or you will need LOCAL keyword between LOAD and DATA.

Check http://dev.mysql.com/doc/refman/5.0/fr/load-data.html documentation.

Hope that helps.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

pareshprajapatiCommented:
Follow below steps to import your text file in database using phpmyadmin >

- First rename txt file to csv and open in excell
- Convert date format from 12 hr to 24 hour to remove AM/PM
- Save file and confirm format by opening it again in notepad
- Open phpMydmin > Database
- Open table and select Import link
- Browse and Select your text file for "Location of the text file"
- Select "CSV using LOAD DATA" in "Format of imported file" option
- Enter "," at the place of ";" in "Fields terminated by"
- In "Column names" enter "Date_Time,Server_Name, Status"
- uncheck "Use LOCAL keyword"
- GO

This is done
0
pradeep_bansalAuthor Commented:
Thanks paresh,

But these text files generated by script on daily basis. There is script (written in vbscript) which is scheduled to run in every 10 minutes, this script use to write the results on a text file or you can consider it as a log file, every time the script runs that text file get updated and at the end of the day text file suppose to have all the alerts for the day.
Currently i am moving this text file from one place to other(reository) but now there is a need to insert that data everyday into the database.
0
pareshprajapatiCommented:
For this you can do following

Make generated file accessible to php program

Solution1 :
Generate a cronjob (Scheduled Tasks) for php program
This program will read text file (one by one line, format data, create insert query and run) daily at your given time and import in database

Solution 2:
Change vb script to generate date in 24 hour format
Generate a cronjob (Scheduled Tasks) for php program
This program will use "LOAD DATA INFILE" mysql command to import your file in database

Solution 2 will be better for big text files.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.