insert records into table from text file as well as from another table

hi .........
i have two tables emp_company and at0310
in at0310 having fields: ecardno,atdate,time,IO,serialno,cardrd and ecode. and at0310 records comes from text file..
into my text file ecardno,atdate,time,IO,serialno,cardrd record available but ecode not comes from there.
in my emp_compnay i have fields like ecode,ecardno,etc
i want into my at0310 table ecode comes from emp_company and only those ecode should be stored into at0310 that matches with ecardno with text file.... can you tell me any query .....
according to me select and insert needed but i cant place them in working order
plz help me out waiting for reply

<html>
<?php
$con=mysql_connect("localhost","root","root");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db('master',$con);
//(1) Read the text file into a variable
$file = "ATND.txt";
$fp = fopen($file, "r");
$data = fread($fp, filesize($file));
fclose($fp);
//(2) Then we can get rid of the tabs in there:
$output = str_replace("\t"," ", $data);
//(3) Then we explode it at every line break
$output = explode("\n", $output);
foreach($output as $var) {
$tmp = explode(" ", $var);
$ecardno = $tmp[0];
$cluster = $tmp[1];
$atdate = substr($tmp[1],0,6);
$attime = substr($tmp[1],6,4);
$IO = substr($tmp[1],10,1);
$serialno = substr($tmp[1],11,5);
$cardrd = substr($tmp[1],16);
//$sql = "(INSERT INTO at0210 SET ecardno='$ecardno', atdate='$atdate',attime='$attime',IO='$IO',serialno='$serialno')";
$sql = mysql_query("INSERT INTO at0310 ('$ecardno', '$atdate','$attime','$IO','$serialno','$cardrd'") ("SELECT ecode FROM emp_company WHERE ecode = '$_POST[ecardno]'")");
mysql_query($sql);
}
echo "Done!";
?>

mcastu

Newbie Poster

22 posts since Feb 2010

Reputation Points: 10

Solved Threads: 1

2 Years Ago

i would do it like this:

<?php
$con=mysql_connect("localhost","root","root");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db('master',$con);
//(1) Read the text file into a variable
$file = file("ATND.txt");

foreach($file as $line){
     //(2) Then we can get rid of the tabs in there:
     $line = str_replace("\t"," ", $line);

     $fields = explode(" ", $line);
     $ecardno = $fields[0];
     $cluster = $fields[1];
     $atdate = substr($fields[1],0,6);
     $attime = substr($fields[1],6,4);
     $IO = substr($fields[1],10,1);
     $serialno = substr($fields[1],11,5);
     $cardrd = substr($fields[1],16);
     
     // get ecode
     $ecode = implode('',mysql_fetch_row(mysq_query("SELECT ecode FROM emp_company WHERE ecardno = '$ecardno'")));
     
     $sql = "INSERT INTO at0310 (ecardno, atdate, attime, ...) VALUES ('$ecardno', '$atdate', '$attime', ...)";
     mysql_query($sql);
}

echo "Done!";
?>

notice that you'll have to fill the blanks... i didn't wrote all the fields on the insert statement!

hope that helps!

damnpoet

Light Poster

36 posts since Feb 2010

Reputation Points: 10

Solved Threads: 7

2 Years Ago

i would do it like this:

<?php
$con=mysql_connect("localhost","root","root");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db('master',$con);
//(1) Read the text file into a variable
$file = file("ATND.txt");

foreach($file as $line){
     //(2) Then we can get rid of the tabs in there:
     $line = str_replace("\t"," ", $line);

     $fields = explode(" ", $line);
     $ecardno = $fields[0];
     $cluster = $fields[1];
     $atdate = substr($fields[1],0,6);
     $attime = substr($fields[1],6,4);
     $IO = substr($fields[1],10,1);
     $serialno = substr($fields[1],11,5);
     $cardrd = substr($fields[1],16);
     
     // get ecode
     $ecode = implode('',mysql_fetch_row(mysq_query("SELECT ecode FROM emp_company WHERE ecardno = '$ecardno'")));
     
     $sql = "INSERT INTO at0310 (ecardno, atdate, attime, ...) VALUES ('$ecardno', '$atdate', '$attime', ...)";
     mysql_query($sql);
}

echo "Done!";
?>

notice that you'll have to fill the blanks... i didn't wrote all the fields on the insert statement!

hope that helps!

damnpoet

Light Poster

36 posts since Feb 2010

Reputation Points: 10

Solved Threads: 7

This article has been dead for over three months

You