I have been given a list of 'From' and 'To' names, as there are multiple entries in my DB eg. Fred Bloggs, Bloggs Fred, F Bloggs are all the same person but I have organised the list like so.
Bloggs Fred, Fred Bloggs
F Bloggs, Fred Bloggs
Fred Bloggs, Fred Bloggs
So values on the left are 'From' and values on the right are 'To'.
heres my code:
Expand|Select|Wrap|Line Numbers
- #!perl.exe
- # script to CLEAN the AX4GANGS table
- # If values of FROM and TO are the same then ignore!
- # If values of FROM and TO are different INSERT 'To Names' to AX4GANGS table.
- # Delete values of FROM, from AX4GANGS table
- #
- # Jonny Carter
- #
- #
- BEGIN
- {
- ( $SCRIPTNAME=$0 )=~s/^.*\\//g;
- $SCRIPTNAME=~s/^.*\\//g;
- #get the pm libfiles
- push( @INC, "../pm" );
- }
- use CGI;
- use DBI;
- use DBNAME;
- use LOG;
- use Time::Local;
- $dbh=DBI->connect("dbi:mysql:host=$DB_host:dbname=$database",$DB_user,$DB_pass)
- or die "could not conect to database $database";
- %allaudited=( getsqllist( "select distinct AUDITED, '1' from AX4GANGS " ) );
- $n=keys(%allaudited);
- open FILE, "ax4audit.csv" or die $!;
- while( $line=<FILE> )
- {
- chomp $line;
- ($count,$company,$from,$to) = split( ',', $line );
- # AX4GANGS INSERTS and DELETES
- ( $from eq $to ) && next;
- if ( ! $allaudited{$to} )
- {
- print "INSERT INTO AX4GANGS VALUES (NULL,'$company','$to',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'jonnyc',NULL);\n";
- $allaudited{$to}="1";
- }
- print "DELETE from AX4GANGS where AUDITED='$from';\n";
- # AUDITS Table UPDATES
- print "UPDATE AUDITS set AUDITED ='$to' where AUDITED='$from';\n";
- }
- close ( FILE );
- exit;
Heres an example of what is happening:
I have a value of JONny Carter and a value of Jonny Carter.
for some reason when I then run the sql file I get both inserted into the table.
I hope I have explained this well enough ... Any help is really appreciated.