Trying to match keys of hash with DBI fetchrow_hashref

[samasama]

Hi, noob here...
I'm trying to read a row containing usernames and see if those
usernames are in an already created hash...

------- Code -------

my %lusers;

sysopen( LIST0, "list.txt", O_RDONLY );
#|| die "Can't open original list!";

while (<LIST0>) {
my @fields = split(/\s{2}/, $_);
$lusers{$fields[0]} = $fields[1];
}


my $dbh = open_db('ro') || die "Could not open db: $!\n";
my $sql = qq{ SELECT username
FROM dialup
};

my $sth0 = $dbh->prepare($sql) || die "DBH Preparing: ",
$dbh->errstr;

$sth0->execute;

while ( my $row = $sth0->fetchrow_hashref ) {
foreach my $keys (keys %lusers) {
if ($row->{'username'} ne $keys) {
print "$keys not in db\n";
}
}
last;
}

----------------

I know I'm going wrong on my if statement there, but I'm not sure what
I should be comparing exactly...

Any help is vastly appreciated.

Thanks

 

[Paul Lalli]

I'm trying to read a row containing usernames and see if those
usernames are in an already created hash...

while ( my $row = $sth0->fetchrow_hashref ) {
foreach my $keys (keys %lusers) {
if ($row->{'username'} ne $keys) {
print "$keys not in db\n";
}
}
last;
}

I know I'm going wrong on my if statement there, but I'm not sure what
I should be comparing exactly...

Your structure says to loop through all the keys of the existing hash,
and compare $row->{username} to each one of them. If ANY of them
don't match, you print out the error message. Obviously, at most one
should match. You should be using the exists function, not looping
through the keys.

while (my $row = $sth0->fetchrow_hashref) {
if (!exists $lusers{ $row->{username} } ) {
print "$row->{username} not in existing hash\n";
}
}

Paul Lalli

 

[samasama]

Your structure says to loop through all the keys of the existing hash,
and compare $row->{username} to each one of them. If ANY of them
don't match, you print out the error message. Obviously, at most one
should match. You should be using the exists function, not looping
through the keys.

while (my $row = $sth0->fetchrow_hashref) {
if (!exists $lusers{ $row->{username} } ) {
print "$row->{username} not in existing hash\n";
}
}

Paul Lalli

errr, I explained myself wrong... I need to check whether the key
(username) in the hash exists in the database, if not do something with
that username.

 

[Paul Lalli]

errr, I explained myself wrong... I need to check whether the key
(username) in the hash exists in the database, if not do something with
that username.

Then I'd say you're going backwards...

my $sql = "SELECT 1 FROM dialup WHERE username = ?";
my $sth = $dbh->prepare($sql);
for my $username (keys %lusers) {
$sth->execute($username);
my ($found) = $sth->fetchrow_array();
print "$username not found in DB\n" unless $found;
}

If for some reason you really wanted to fetch them all and then
check...
my $sql = "SELECT username FROM dialup";
my $sth = $dbh->prepare($sql);
$sth->execute();
my %db_users
while (my ($username) = $sth->fetchrow_array() ) {
$db_users{$username} = 1;
}
for my $username (keys %lusers) {
print "$username not found in DB" unless exists
$db_users{$username};
}

Paul Lalli

 

[samasama]

Then I'd say you're going backwards...

my $sql = "SELECT 1 FROM dialup WHERE username = ?";
my $sth = $dbh->prepare($sql);
for my $username (keys %lusers) {
$sth->execute($username);
my ($found) = $sth->fetchrow_array();
print "$username not found in DB\n" unless $found;
}

Wow, yeah, that's a lot simpler (and efficient). I'll have to stare at
it a bit...

Thanks so much Paul!