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

Hi, I have the following code: 
for (my $eu=0;$eu<=(scalar(@identifier));$eu++){
    foreach my $id(@idfeat)    { if ($id eq $identifier[$eu]){
                my $sth=$dbh->prepare("INSERT INTO dd VALUES('$identif
+ier[$eu]','Feature')");
                $sth->execute();    
                $sth->finish;
                } 
                else {my $sth=$dbh->prepare("INSERT INTO dd VALUES('$i
+dentifier[$eu]','Bug')");
                $sth->execute();    
                $sth->finish;
                }
            }
    }

[download]
What I want to insert in the database is the result of an html form. In the html form, for each element of the array @identifier i have a checkbox:if it's checked then I will insert that element in the database as Feature, if it's not checked i will insert as Bug in database. So I will have in @idfeat those elements from @identifier that are checked: example: @identifier=("A1","B1","C1","D1"); @idfeat=("A1","B1");this means that A1and B1 are checked in the html form so they are Feature: I want my database to look like this: 
A1 Feature
B1 Feature
C1 Bug
D1 Bug

[download]
But in the way I do it it looks like this: 
A1 Feature
A1 Bug
B1 Feature
B1 Bug
C1 Bug
C1 Bug
D1 Bug
D1 Bug

[download]
Thank you for your time

Replies are listed 'Best First'.
Re: inserting in a database
by hmerrill (Friar) on Nov 24, 2003 at 13:59 UTC
    I think I see what the problem is - in your inner loop, when you find a match you are inserting 'Feature', but you are not exiting that inner loop. Instead, you are continuing in the inner loop to process the next element of @idfeat. Try something like this (untested): 
       my $insert_sql = qq{
      INSERT INTO dd
      VALUES(?, ?)
   };
   my $sth_insert=$dbh->prepare($insert_sql);

   for (my $eu=0;$eu<=(scalar(@identifier));$eu++){
      my $match_found = 0;
      foreach my $id(@idfeat) {
         if ($id eq $identifier[$eu]){
             $match_found = 1; # found a match
             last; # break out of the foreach
         }
      }
      if ($match_found) {
          $sth_insert->execute($identifier[$eu], 'Feature');
      } 
      else {
          $sth_insert->execute($identifier[$eu], 'Bug');
      }
   }
   $sth_insert->finish;

    [download]
    Read up on using 'placeholders' in the excellent DBI documentation by doing 
       perldoc DBI

    [download]
    at a command prompt. Using placeholders will only help you - they take care of all quoting issues (which can be a real pain in the butt), and they can improve performance.

    HTH.
[reply]
[d/l]
[select]
Re: inserting in a database
by edoc (Chaplain) on Nov 24, 2003 at 14:02 UTC

    untested

    my $sth = $dbh->prepare("INSERT INTO dd VALUES( ?, ? )");
my %feat = map { $_ => 1 } @idfeat;
foreach my $ident (@identifier){
  my $result = $feat{$ident} ? 'Feature' : 'Bug';
  $sth->execute($ident,$result);    
}

    [download]

    cheers,

    J

[reply]
[d/l]
Re: inserting in a database
by Taulmarill (Deacon) on Nov 24, 2003 at 13:57 UTC 
    my @identifier = ("A1","B1","C1","D1"); 
my @idfeat = ("A1","B1");
my $pattern = join ( "|", @idfeat );

for my $eu ( 0 .. $#identifier ) {
    if ($identifier[$eu] =~ /$pattern/ ){
        print "$identifier[$eu], Feature\n";
        #my $sth=$dbh->prepare("INSERT INTO dd VALUES('$identifier[$eu
+]','Feature')");
        #$sth->execute();    
        #$sth->finish;
    } 
    else {
        print "$identifier[$eu], Bug\n";
        #my $sth=$dbh->prepare("INSERT INTO dd VALUES('$identifier[$eu
+]','Bug')");
        #$sth->execute();    
        #$sth->finish;
    }
}

    [download]
[reply]
[d/l]
      Just be sure to do a $identifier[$eu] = $dbh->quote( $identifier[$eu] ); before you prepare ;)
[reply]
[d/l]
        ... or use place holders instead: 
        my $sth = $dbh->prepare("
   INSERT INTO dd 
   VALUES(?,?)
");

$sth->execute($identifier[$eu],'Feature');

        [download]
        and let the module do intelligent quoting and provide statement caching for you (like hmerrill provided). Kudos to Taulmarill, however, for using 
        for my $eu (0..$#identifier) {

        [download]
        instead of 
        for (my $eu = 0; $eu <= (scalar(@identifier)); $eu++) {

        [download]

        jeffa

        L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
B--B--B--B--B--B--B--B--
H---H---H---H---H---H---
(the triplet paradiddle with high-hat)
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom