Parse a text file and then do MySQL insert?
Posted: Tue Jan 27, 2009 2:48 pm
I am trying to parse 2 files and then insert data to MySQL database. The two files are "action.txt" and "status.txt", and they are corresponding to 2 tables that match the name. Column "SSN" is primary key of table people, and the foreign key in table action.
For example,
people.txt
SSN|First Name|Last Name
600000000|Jack|Brin
action.txt
Month|Work|Result
01|IBM|Yes
03|Microsoft|No
11|Google|Yes
The two files are always come in a PAIR, and the people.txt only contains 1 row of data in addition to the header records. The header records my change dynamically. Please note that the SSN is not included in action.txt, so I need to parse it from people.txt.
Here is my code, but it appears that I didn't parse the SSN correctly. Can someone help me debug it, and also point me out if my script is performance optimized?
For example,
people.txt
SSN|First Name|Last Name
600000000|Jack|Brin
action.txt
Month|Work|Result
01|IBM|Yes
03|Microsoft|No
11|Google|Yes
The two files are always come in a PAIR, and the people.txt only contains 1 row of data in addition to the header records. The header records my change dynamically. Please note that the SSN is not included in action.txt, so I need to parse it from people.txt.
Here is my code, but it appears that I didn't parse the SSN correctly. Can someone help me debug it, and also point me out if my script is performance optimized?
Code: Select all
<?php
$connection = mysql_connect("databaseServer", "username", "password") or die ("Unable to connect to server");
$db = mysql_select_db("mydata", $connection) or die ("Unable to select database");
#mysql connection as per the FAQ
$fcontents = file ('./people.txt', FILE_SKIP_EMPTY_LINES);
# expects the file to be in the same dir as this script
#parse and then insert to table people
$hrecords = explode("|", $fcontents[0]);
for($i=1; $i<sizeof($fcontents); $i++)
{
$arr = explode("|", $fcontents[$i]);
$sql = "insert into people (". implode(",", $hrecords).") values ('".implode("','", $arr)."')";
mysql_query($sql);
if(mysql_error()) {
echo mysql_error() ."\n";
}
}
$fcontents = file ('./action.txt', FILE_SKIP_EMPTY_LINES);
#parse and then insert SSN
$hrecords = explode("|", $fcontents[0]);
$sql = "insert into action (SSN) values ('".$hrecords[0]."')";
#parse and then insert to table action
for($i=1; $i<sizeof($fcontents); $i++)
{
$arr = explode("|", $fcontents[$i]);
$sql = "insert into action (". implode(",", $hrecords).") values ('".implode("','", $arr)."')";
mysql_query($sql);
if(mysql_error()) {
echo mysql_error() ."\n";
}
}
?>