Map hashref to get values?

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

I've obviously been staring at my code too long and I need a second pair of eyes...

  my $dbfieldlist = join ", ", @fields;
  my $dbfielddata = join ", ", map { $dbh->quote($_) } $form->field;

  my $insert = $dbh->prepare(
    INSERT into $DBTABLE ($dbfieldlist) VALUES ($dbfielddata)
  );
  $insert->execute();
[download]

$form->field contains a hashref of field/values from CGI::FormBuilder. So the above code is inserting the field names into the database instead of inserting the values into the corresponding columns.

What am I missing?

Thanks in advance, Monks.

Comment on Map hashref to get values? Download Code

Replies are listed 'Best First'.
Re: Map hashref to get values? by Eliya (Vicar) on Mar 07, 2012 at 23:40 UTC
I think you want `... my $fields = $form->field; my $dbfielddata = join ", ", map { $dbh->quote( $fields->{$_} ) } @fie +lds; ...` [download] assuming that `@fields` holds the keys of the hash — otherwise, you might want to use `keys %$fields` instead (both for creating `$dbfieldlist` and `$dbfielddata`, so their ordering matches) P.S.: instead of fiddling with `quote()` it's usually better to use placeholders.	[reply] [d/l] [select]
Re^2: Map hashref to get values? by apu (Sexton) on Mar 07, 2012 at 23:44 UTC
`@fields` only has the list of fields. (It is what is fed to FormBuilder to generate the form.) `$form->field` has the actual hashref with the name/value pairs	[reply]
Re^3: Map hashref to get values? by aaron_baugher (Curate) on Mar 08, 2012 at 01:44 UTC
In that case, `$form->field` contains everything you need and you can ignore `@fields`. Although a hash is unordered, the `keys` and `values` functions are guaranteed to return their elements in the same order. So you can do the following, and be confident that things will match up right: `my $fields = join ', ', keys %{$form->field}; my $values = join ', ', map {$dbh->quote($_)} values %{$form->field};` [download] However, as Eliya said, placeholders are generally safer than quote(). To use placeholders, you'll need a count of the values: `my $fields = join ', ', keys %{$form->field}; my @values = values %{$form->field}; my $placeholders = join ',', ('?') x @values; my $sql = qq\| INSERT INTO mytable ($fields) VALUES ($placeholders); \|; my $st = $dbh->prepare($sql); $st->execute(@values);` [download] Aaron B. My Woefully Neglected Blog, where I occasionally mention Perl.	[reply] [d/l] [select]
Re^4: Map hashref to get values? by apu (Sexton) on Mar 08, 2012 at 04:29 UTC
Re^3: Map hashref to get values? by Eliya (Vicar) on Mar 07, 2012 at 23:50 UTC
Yes, but if you use `@fields` to create your list of column names (`$dbfieldlist`) for the insert statement, the corresponding values should match, shouldn't they? And hashes have no defined ordering. In other words, if you take the keys from the hash, you'd have to make sure that you sort them appropriately. So why not take `@fields` directly — I'd suppose it contains the same names as `keys %$fields` anyway, only in (presumably) different ordering. `my @fields = qw(foo bar baz); # create a hash (as returned by $form->field) my $fields = { map { $_ => "val_$_" } @fields }; my $dbfieldlist = join ", ", @fields; my $dbfielddata = join ", ", map { "'$fields->{$_}'" } keys %$fields; + # wrong ordering #my $dbfielddata = join ", ", map { "'$fields->{$_}'" } @fields; + # ok print "INSERT into DBTABLE ($dbfieldlist) VALUES ($dbfielddata)"; # INSERT into DBTABLE (foo, bar, baz) VALUES ('val_bar', 'val_baz +', 'val_foo')` [download]	[reply] [d/l] [select]