comparing data from text file to MySQL table

Codewalkers Forums Sponsor:

Be the architects of evolution and help create the mobile internet future. It’s your move---enter to win here!

July 16th, 2002, 06:06 PM

scrtagt69

Join Date: Apr 2007

Posts: 3

Time spent in forums: < 1 sec
Reputation Power: 0

comparing data from text file to MySQL table

I was wondering if this is possible: Compare a row from a table to a row from a text file, and if that row is not in that text file, then delete it from that table, and move it to another one. Say for example I read in a text file and assign the following variables:

Code:

$fd = fopen ("d:active.txt", "r");
while (!feof ($fd)) {
      $line= fgets($fd, 4096);

$MyArray=explode("|", $line);
$LastName=addslashes($MyArray[0]);
$FirstName=addslashes($MyArray[1]);
$MiddleName=$MyArray[2];
$SSN=$MyArray[3];

Then I have a table Active with the same fields:
LastName, FirstName, MiddleName, SSN

And an Inactive table exactly the same
LastName, FirstName, MiddleName, SSN

This text file is inserted into the Active table.

The reason is, is that this text file is updated every 2 weeks, and only current data should be maintained, with an archive for old data. So again, I want to move data from the Active table to the Inactive table which is not present in the text file. Can anyone give me an idea on how to do this??? Hope all this makes sense, if not let me know and ill try to clarify it more. Thanks

July 16th, 2002, 07:06 PM

greggory

Join Date: Apr 2007

Location: Reims, France

Posts: 82

Time spent in forums: < 1 sec
Reputation Power: 2

RE: comparing data from text file to MySQL table

My answers implies that you add a field named 'flag' in your 'active' table. It has to be a boolean, so a char is perfect.

The goal is to mark each record from the active table to know that it is in the file, then copy inactive records to that table, then delete inactive records from 'active'.

php Code:

Original - php Code

 
<?php
// First mark that sthg is happening
mysql_query( "update active set flag = 0" ) 
     or die( mysql_error() );
 
$fd = fopen ("d:active.txt", "r");
while (!feof ($fd)) {
     $line= fgets($fd, 4096);
 
     $MyArray=explode("|", $line);
     $LastName=addslashes($MyArray[0]);
     $FirstName=addslashes($MyArray[1]);
     $MiddleName=$MyArray[2];
     $SSN=$MyArray[3];
 
 
    // Then update each active line 
    // (assuming that two different people cannot have the same first & last name)
    mysql_query( "update active set flag = 1 where lastname ='$LastName' and firstname = '$FirstName'" )
      or die( mysql_error() );
}
 
// We can now put records with 0 into the inactive table
$r = mysql_query( "select lastname,firstname,middlename,ssh from active where flag = 0" )
   or die( mysql_error() );
while ( $tmp = mysql_fetch_array( $r )) {
   mysql_query( "insert into inactive ( lastname,firstname,middlename,ssh ) values ( '".
     addslashes( $tmp['lastname'] )."','".      
     addslashes( $tmp['firstname'] )."','".      
     addslashes( $tmp['middlename'] )."','".      
     addslashes( $tmp['ssh'] )."' )" )
   or die( mysql_error() );
}
 
// Delete inactive
mysql_query( "delete from active where flag = 0" ) 
   or die( mysql_error() );
?>

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: