On Tue, 31 Jul 2001, Nelson Goforth wrote:
> <snip>
> And this works fine for the text file - but from what I've seen I
> probably can't carry over the iteration through the (up to) 100
> actual survey questions. I named the MySQL fields 'q1', 'q2',
> anticipating some way to do a loop, but I can't see it.
>
> Does anyone have any suggestions of how best to do this? The thought
> of one huge INSERT statement makes my eyes glaze over - I'm hoping
> there is a more elegant alternative.
>
More elegant, and more fun. Here's one way. It assumes the order of your
columns is name. city, Q1, Q2, Q3, ... Q100. If you have an
auto_incrementing id column in there, you'll want to push a 'NULL' into
your @values array in the appropriate place. We're also assuming your
survey's Q1..Q100 data type is numeric rather than string.
#######################
my @values;
for (qw(name city ) ) {
push @values, $dbh->quote($asmt->param($_));
}
for (1..100) {
push @values, $asmt->param("Q$_");
}
my $values = join (",",@values);
$dbh->do("insert into your_table values ($values)")
or die("D'oh! " . $DBI::errstr;
#######################
HTH
David Mintz
Spanish Interpreter
US District Court, Southern District of New York
Web Design & Hosting http://www.dmintzweb.com/
Personal http://www.panix.com/~dmintz/
