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

Hi monks,

I'm currently working on a part of programme designed to update a form which we are updating the Perl. However the person who started this update has left the company and I'm a novice at Perl (at the moment).

There are two arrays set up with the form fields and map to some of the database columns in MySQL. 

my (@ffs) =("txtName", "txtCreated","txtDateren","txtOldname","txtDel"
+);
my (%cols) = ("txtName"=>"Listname", "txtCreated"=>"DateCreated","txtD
+ateren"=>"DateRenamed",
        "txtOldname"=>"OldListname","txtDel"=>"DateDeleted");

[download]

In the add and amend routines, we are try loop through the fields, define the non-empty ones and put them into a variable which is then placed inside the relevant spot in the SQL query to be passed to the db. 

foreach(@ffs)
    {
      if($fd{$_} eq ""){
          $updatestring = "$updatestring,[$cols{$_}] = NULL";
      }
      else{
          $updatestring = "$updatestring,[$cols{$_}] = \'$fd{$_}\'";
      }
    }

[download]

SQL statement: 

my $sth = $db->prepare("UPDATE table SET $updatestring WHERE tbl.field
+ = '$lname'");

[download]

I have tested the SQL directly on the database and this is working but the $updatestring doesn't seem to be reaching the db at all.

I suspect that I have set up the foreach loop incorrectly so that the SQL is not seeing the correct parts of the form.

I'd be very grateful for some pointers in the right direction.

Thanks.

Edit: g0n - code tags and formatting

Replies are listed 'Best First'.
Re: Looping an array and placing into SQL
by jhourcle (Prior) on May 22, 2007 at 16:01 UTC

    After you do a 'prepare', you need to do an 'execute'. See the DBI documentation.

    ...

    I don't know what type of values your data might have, but a single quote within any item will break your process. This is one of the cases where placeholders are your friend. (and if you pass 'undef' to them, then you don't have to worry about the logic for handling NULL)

    my $sql = 'UPDATE table SET '
    . join (',', map {"$_=?"} @ffs)
    . 'WHERE tbl.field=?';
# check the string '$sql' -- you used 'table' in the FROM, but 'tbl' i
+n the WHERE
my $sth = $db->prepare( $sql );
$sth->execute( @cols{@ffs}, $lname );

    [download]

    update: fixed a couple of typos.

    update 2: if the empty string is being used to signify null (and not just a bad test for undef (undef eq ''), then change the execute line to:

    $sth->execute( (map { $_ eq '' ? undef : $_ } @cols{@ffs} ), $lname );
[reply]
[d/l]
[select]
Re: Looping an array and placing into SQL
by thezip (Vicar) on May 22, 2007 at 16:27 UTC

    You really should consider using placeholders, as in:

    #!/perl/bin/perl -w
use strict;
use Data::Dumper;

# Added for test purposes
my $lname = "foo";
my %fd = (
  txtName    => "", 
  txtCreated => "2007-04-04",
  txtDateren => "", 
  txtOldname => "Jackson",
  txtDel     => "",
);

# The actual code...
my %cols = (
  txtName    => "Listname", 
  txtCreated => "DateCreated",
  txtDateren => "DateRenamed", 
  txtOldname => "OldListname",
  txtDel     => "DateDeleted",
);

my @values = ();
my @fields = ();
for ( qw/ txtName txtCreated txtDateren txtOldname txtDel / ) {
  push (@fields, $cols{$_} . "=" . '?');
  push (@values, ($fd{$_} eq '') ? 'NULL' : $fd{$_});
}

my $updatestring = join(', ', @fields);
my $SQL = qq(
UPDATE table 
SET $updatestring 
WHERE tbl.field = '$lname'
);

print $SQL;

my $sth = $db->prepare($SQL);
$sth->execute(@values);

__OUTPUT__

UPDATE table
SET Listname=?, DateCreated=?, DateRenamed=?, OldListname=?, DateDelet
+ed=?
WHERE tbl.field = 'foo'

    [download]

    Where do you want *them* to go today?
[reply]
[d/l]
      Many thanks to both of you for your comments and help.
[reply]
Re: Looping an array and placing into SQL
by naikonta (Curate) on May 23, 2007 at 15:55 UTC
    I would let SQL::Abstract to generate the SQL statement for me. 
    #!/perl/bin/perl
use strict;
use warnings;
use Data::Dumper;
use SQL::Abstract;

my %fd = (
    txtName    => 'OK',
    txtCreated => '',
    txtDateren => '', # will use NOW() function 
    txtOldname => 'NOT OK',
    txtDel     => '',
);
my $id = 1234;

# column name mapping
my %cols = (
  txtName    => 'Listname', 
  txtCreated => 'DateCreated',
  txtDateren => 'DateRenamed', 
  txtOldname => 'OldListname',
  txtDel     => 'DateDeleted',
);

# which column to update?
my %column = map { $cols{$_} => $fd{$_} } grep { $fd{$_} } keys %fd;
$column{DateRenamed} = \'NOW()';

my $sql = SQL::Abstract->new;
my($query, @binds) = $sql->update('table', \%column, { id => id });
print Data::Dumper::Dumper($query, \@binds);

    [download]
    Output: 
    $VAR1 = 'UPDATE table SET DateRenamed = NOW(), Listname = ?, OldListna
+me = ? WHERE ( id = ? )';
$VAR2 = [
          'OK',
          'NOT OK',
          1234
        ];

    [download]

    Open source softwares? Share and enjoy. Make profit from them if you can. Yet, share and enjoy!

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom