Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks! I am trying to evaluate the elements in the array here to check if they are not in the db, but there is something not working in my code, can someone tell me what its wrong here? 
foreach $num_doc(@num_from_doc) {

# Now retrieve data from the table.

    $sth = $dbh->prepare('SELECT num FROM num_table WHERE num <> ?'); 
+   
    
    $sth->execute($num_doc);   

my $num_in_db = $sth->fetchrow_array();

print "**This $num_doc is not in the DataBase<br>";
print "Here is what I found: $num_in_db<br>";
}

[download]


Replies are listed 'Best First'.
Re: Compare items in DB
by kyle (Abbot) on Mar 05, 2008 at 19:50 UTC

    You're selecting every record from the table that does not match the number you're looking at. That doesn't tell you whether the number is in the table. I'm guessing you want something like this: 

    my $sth = $dbh->prepare( 'SELECT count(*) FROM num_table WHERE num = ?
+' );
foreach my $n ( @num_from_doc ) {
    $sth->execute( $n );
    my ($exists) = $sth->fetchrow_array;
    my $not = $exists ? '' : ' not';
    print "Number $n is$not in the database.\n";
}

    [download]
[reply]
[d/l]
      It worked, just made a simple change to it.
          #my $not = $exists ? '' : ' not';
    unless($exists){    
                    print "Number $n is not in the database.<br>\n";
                    }

      [download]

      Thank you!!
[reply]
[d/l]
        Slightly better is print "Number $n is not in the database.<br>\n" unless $exists;
[reply]
[d/l]
      Yes it worked, can this be for the reverse?

      my $sth = $dbh->prepare( 'SELECT count(*) FROM num_table WHERE num <> 
+?
+' );

      [download]
[reply]
[d/l]

        That will certainly do something, but I think it will be something you don't want.

        Consider a table with these numbers in it: (1, 2, 4, 5). If your number is 2 (which is present), and you select the count of numbers that do not match that, you get 3 (the non-matching numbers are 1, 4, 5). If your number is 3 (which is not present), and you select the same thing, you get 4 (every number there).

        What you want is to tell whether a given number is there or not. What you're suggesting is to count (or list) every number that isn't the given number.

[reply]
Re: Compare items in DB
by dragonchild (Archbishop) on Mar 05, 2008 at 21:12 UTC 
    CREATE TEMPORARY TABLE foo (
    bar INT NOT NULL
);
INSERT INTO foo (bar) VALUES (1),(2),(4),(5);

SELECT foo.bar
  FROM foo LEFT OUTER JOIN num_table ON (foo.bar = num_table.num)
 WHERE num_table.primary_key_column IS NULL
;

    [download]
    Of course, I wouldn't use that unless I could explain it.
    My criteria for good software:
    1. Does it work?
    2. Can someone else come in, make a change, and be reasonably certain no bugs were introduced?
[reply]
[d/l]
      Is this for the reverse?
[reply]
        Look up what LEFT OUTER JOIN does before asking.
        My criteria for good software:
        1. Does it work?
        2. Can someone else come in, make a change, and be reasonably certain no bugs were introduced?
[reply]
Re: Compare items in DB
by Corion (Patriarch) on Mar 05, 2008 at 19:28 UTC

    Maybe you care to tell us which something is not working?

[reply]
      My point here is just to ask, if you are comparing all the elements of an array with what is in a database table, and return the array element that is not in the database, if the code here is right for that?!
[reply]

Back to Seekers of Perl Wisdom