Perl Compare Arrays from Database Results

[fsaerospace]

Hi All!!

I want to compare results from two MySQL queries.
Lets say I have two databases with approx. the same data (i.e. employee names).

SQLquery1=select employees from sf_emps order by employees
Array1=(aaron,abott,babbot,cabbot,dabbot,fabbot)

SQLquery2=select employees from OTHERDATABAE order by employees
Array2=(aaron,babbot,cabbot,dabbot,fabbot)

So, both arrays have been created from the results of the two sql queries.
Now, I want to loop through and see if each of the employees in Array1 are in Array2. If one of the employees isn't then I want to print out: "Booboo, you have a missing entry in the second database!" or something like it

What is the best and most efficient way to do this???

THANKS for your help!!

- matthias

 

[fortytwo]

Hi,

I worked this out using the following:

#!/usr/bin/perl -w

use strict;

#Fill up the test array 1
my @sql_query_results_1 = qw(aaron abott babbot cabbot dabbot fabbot);

#fill up the test array 2
my @sql_query_results_2 = qw(aaron babbot cabbot dabbot fabbot);

#for each element of the result set from DB query 2, create a hash key that has the value 1
my %people_seen = map { $_ => 1 } @sql_query_results_2;

#Loop through each element of the 1st result set and print out a warning message if there is no equivalent hash key in the hash of the 2nd set of results
foreach my $person (@sql_query_results_1) {
        next if ($people_seen{$person});
        print "Booboo, you have a missing entry in the second database! - $person\n";
}

There may well be more efficient methods though.

HTH

Will. will@hellacool.co.uk

http://home.hellacool.co.uk/

 

[fishiface]

If you don't mind some slightly startling syntax, hash slices are nice in this context:

[tt]#Fill up the test array 1
my @ta1 = qw(aaron abott babbot cabbot dabbot fabbot);

#fill up the test array 2
my @ta2 = qw(aaron babbot cabbot dabbot fabbot);

#for each element of the result set from DB query 1, create a hash key that has the value 'undef';
my %h;
undef @h{@ta1};

#clear all elements where we have a match from query 2
delete @h{@ta2};

foreach my $person (keys %h) {
print "Booboo, you have a missing entry in the second database! - $person\n";
}[/tt]

I don't know whether it's any more efficient (it might take less memory to store undefs than values in a hash) but I love the power of the syntax.

Yours "As soon as we started programming, we found to our surprise that it wasn't as
easy to get programs right as we had thought. Debugging had to be discovered.
I can remember the exact instant when I realized that a large part of my life
from then on was going to be spent in finding mistakes in my own programs."
--Maurice Wilk