Re: Using a multi-level hash in an insert statement...

IIRC

(untested)

my $statement = "Insert into TABLE (name, date, type, federal, active)
+ values( ?, ?, ?, ?, ? );

$sth = $dbh->prepare( $statement );

foreach my $name ( keys %holidays ) {
  $sth->bind_param( 1, $name );
  my $i = 2;
  $sth->bind_param( $i++, $holidays->{$name}->{$_} ) foreach keys %{$h
+olidays->{$name}};
  $sth->execute(); # check errors here too
}
[download]

Update: (uses named parameters - not supported in all DB's)

my $statement = "Insert into TABLE (name, date, type, federal, active)
+ values( :name, :date, :type, :federal, :active );

$sth = $dbh->prepare( $statement );

foreach my $name ( keys %holidays ) {
  $sth->bind_param( ':name', $name );
  $sth->bind_param( ':'.$_, $holidays->{$name}->{$_} ) foreach keys %{
+$holidays->{$name}};
  $sth->execute(); # check errors here too
}
[download]

Comment on Re: Using a multi-level hash in an insert statement... Select or Download Code

Replies are listed 'Best First'.
Re^2: Using a multi-level hash in an insert statement... by davidrw (Prior) on Apr 21, 2005 at 15:07 UTC
This line: `$sth->bind_param( $i++, $holidays->{$name}->{$_} ) foreach keys %{$hol +idays->{$name}};` [download] Will cause transposing (e.g. '20050101' to be stored as 'name') of the key/values in the insert, because you can't rely on the order that keys() returns. Unforunately needs to be something like: `$sth->bind_param( $i++, $holidays->{$name}->{$_} ) foreach qw/ name da +te type federal active /;` [download] Or, even better, would be to use SQL::Abstract (disadvantage is that it can't be prepare'd): `use SQL::Abstract; my $sql = SQL::Abstract->new; while( my ($name,$data) = each %holidays ) { my %data = ( %$data, name => $name ); # assuming hash only has key +s corresponding to cols in table my($stmt, @bind) = $sql->insert('your_table_name', \%data); $dbh->do($stmt, {}, @bind); }` [download]	[reply] [d/l] [select]
Re^3: Using a multi-level hash in an insert statement... by Transient (Hermit) on Apr 21, 2005 at 15:09 UTC
Ah yes.. my mistake - forgotten about that sort order bit. using named placeholders - while not supported for all DB's, would work in lieu of this.	[reply]