comment on

Here's how I would do it:


use strict;

use Text::CSV_XS;
use DBI;

my $csv_inputfile = "input.csv"; # change me

my %data; # hash for storing csv data

my $csv = Text::CSV_XS->new;
open my $fh, '<', $csv_inputfile or die "can't open $csv_inputfile: $!
+ \n";
while (my $line = <$fh>) {
  $line =~ s/^\s+//;  # strip leading whitespace
  next unless $line;  # ignore blank lines

  # assume CSV file looks like: ip, user, nbname
  if ( $csv->parse($line) ) {
    my ($ip, $user, $nbname) = $csv->fields();
    next unless $ip;
    $data{$ip} = {
      USER    => $user   || '',
      NBNAME  => $nbname || '',
      
    };
  } else {
    my $error = $csv->error_diag();
    print "Parse error: line $. : $error.\n";
  }

}
close $fh;

my $user = "db_user";  # change me
my $pass = "db_pass";  # change me
my $dbh = DBI->connect(
  "dbi:mysql:pstsize",
  $user,
  $pass,
  {RaiseError => 1, AutoCommit => 1},
);
unless ($dbh) {
  die "Could not get database handle.";
}

my $sth = $dbh->prepare( select_data() );

eval {
  $sth->execute();
};
if ($@) {
  die "Database error: ", $dbh->errstr;
}

print "<html><body><pre>\n";

while ( my ($ip, $user, $nbname) = $sth->fetchrow ) {

  unless ($user) {
    print "DB missing USER for $ip.";
    if ($data{$ip}{'USER'}) {
      print ".. but CSV file has value: $data{$ip}{'USER'}.";
    }
    print "\n";
  }

  unless ($nbname) {
    print "DB missing NBNAME for $ip.";
    if ($data{$ip}{'NBNAME'}) {
      print ".. but CSV file has value: $data{$ip}{'NBNAME'}.";
    }
    print "\n";
  }

}

print "</pre></body></html>";

$sth->finish;
$dbh->disconnect;

sub select_data {
  return <<"  END_SQL";
    SELECT DISTINCT
      ipaddr,
      user,
      nbname
    FROM
      sizehist
    ORDER BY
      ipaddr
  END_SQL
}
[download]

Notes:

Read the CSV file first, storing the data into a hash. The IP address is used as the hash key. Then each value in the hash is a reference to another hash, containing the data fields for a given record.
Move the SQL stuff into its own subroutine.
As you pull data from the DB, check the values, then compare against the data in your hash (from the CSV file).
I put simple HTML tags around the output. There's a lot of room here to make it more pretty. I'd suggest maybe using HTML::Template (that way all the HTML is outside of your script).
I added lots of error checking - that stuff's important, never skimp on it!
I made some assumptions about your file formats, etc. So you may need to make some minor changes, but hopefully this is closer to what you need.

Good luck!

In reply to Re: Need to search through a hashref array to find empty, null, or otherwise translucent key values. by scorpio17
in thread Need to compare 2 hash values by carlo1973

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		`&`
	<		`<`
	>		`>`
	[		`[`
	]		`]`

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.