Parse a text file and then do MySQL insert?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
sfresher
Forum Newbie
Posts: 12
Joined: Tue Jan 13, 2009 1:35 pm

Parse a text file and then do MySQL insert?

Post 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";
}
}
 
?>
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

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

Post 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
sfresher
Forum Newbie
Posts: 12
Joined: Tue Jan 13, 2009 1:35 pm

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

Post 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
sfresher
Forum Newbie
Posts: 12
Joined: Tue Jan 13, 2009 1:35 pm

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

Post by sfresher »

Can someone please help?
Post Reply