Page 1 of 1

Parse a text file and then do MySQL insert?

Posted: Tue Jan 27, 2009 2:48 pm
by sfresher
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?

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";
}
}
 
?>

Re: Parse a text file and then do MySQL insert?

Posted: Wed Jan 28, 2009 8:00 am
by jaoudestudios
Just had a quick look - you say it does not work, what happens? any errors?

In regards to optimisation dont do this...

Code: Select all

for($i=1; $i<sizeof($fcontents); $i++) // calculates the limit every loop
do this...

Code: Select all

$total = sizeof($fcontents); // calculates the limit ($total) only once
for($i=1; $i<$total; $i++) // just uses the limit ($total) no calculation required

Re: Parse a text file and then do MySQL insert?

Posted: Wed Jan 28, 2009 1:24 pm
by sfresher
Thanks for your advice. I will update the code accordingly.
The error is column SSN in table action always shows as blank.


jaoudestudios wrote:Just had a quick look - you say it does not work, what happens? any errors?

In regards to optimisation dont do this...

Code: Select all

for($i=1; $i<sizeof($fcontents); $i++) // calculates the limit every loop
do this...

Code: Select all

$total = sizeof($fcontents); // calculates the limit ($total) only once
for($i=1; $i<$total; $i++) // just uses the limit ($total) no calculation required

Re: Parse a text file and then do MySQL insert?

Posted: Wed Jan 28, 2009 7:00 pm
by sfresher
Can someone please help?