my 2 cents... You can't just blindly take form names from the query
object and try to stuff them in a table. I have function that I call
first that determines the common fields between the form and my table.
Then I create the insert/update statement. Thanks Tim for the "x @cols"
clause. Going to change my code to use it.
sub commonFields{
my $table = shift;
my $query = shift;
my $dbh = shift;
my %mark;
my $sth=$dbh->prepare("select * from $table where 1=0")|| die
$dbh->errstr;
$sth->execute || die $dbh->errstr;
grep($mark{$_}++,@{$sth->{NAME_lc}});
return grep($mark{lc $_},$query->param);
}
On Mon, 2003-03-10 at 17:15, Tim Bunce wrote:
> > foreach ( $cgi->param() ) {
> > push @cols, $_;
> > push @vals, $cgi -> param( $_ );
> > }
> > my $sql = "INSERT INTO table ( " . join( ", ", @cols ) . " )\n" .
> > " VALUES ( " . join( ", ", map { "?" } @cols ) . " )";
>
> Seeing all that 'line noise' makes me think we need a neater way.
>
> Personally I'd have written it more like
>
> my $sql = qq{
> INSERT INTO table
> ( } . join( ", ", @cols ) . qq{ )
> VALUES ( } . join( ", ", ("?") x @cols ) . qq{ )
> };
>
> but that's not really that much clearer and possibly more
> 'magical' to the novice who may not be familiar with 'x' as a
> list replication operator.
>
>
> But imagine the DBI provided these two functions:
>
> sub comma_separated_values { join ", ", @_ }
> sub comma_separated_placeholders { join ", ", ("?") x @_ }
>
> you could then write the statement like this:
> separated
> my $sql = qq{
> INSERT INTO table
> ( ${\comma_separated_values(@col)} )
> VALUES ( ${\comma_separated_placeholders(@col)} )
> };
>
> The "... ${\...} ..." is a little magical but not hard to master.
> It's just a cute way to evaluate expressions inside a double quoted
> string. The ${...} part expects a reference to a scalar so that's
> what the backslash before the function name is for. The backslash
> takes a reference to the scalar value returned by the function and
> the ${...} then interpolates that into the string.
>
> Perhaps we could call the functions dbi_csv and dbi_csp then we'd have
>
> my $sql = "INSERT INTO table ( ${\dbi_csv(@col)} ) VALUES ( ${\dbi_csp(@col)} )";
>
> which seems rather neater than where we started:
>
> my $sql = "INSERT INTO table ( " . join( ", ", @cols ) . " )\n" .
> " VALUES ( " . join( ", ", map { "?" } @cols ) . " )";
>
>
> Comments?
>
> Tim.
--
Keith Jackson <[EMAIL PROTECTED]>
