Nice idea. I've some simple improvements to make it more
human friendly.
# some sample data.
my %msgdata = ( messageid => $next_messageid,
quoteid => $quoteid,
body => $q_body,
viewed => 0,
timestamp_m => "CURRENT TIMESTAMP" );
# you can do a sort here if you care about the order
# of the keys.
my @keys = keys %msgdata;
# uncomment this if you want to exclude all pairs
# that have no value.
#@keys = map {defined($msgdata{$_})? $_ : ()} @keys;
# get all my values in the same order as my keys.
my @values = @msgdata{@keys};
# replace any undefined values with "NULL".
# Comment this out if you're excluding these above.
# Note: if you're using $dbh to quote your values
# then use $dbh->quote($_) for the true option
@values = map {defined($_)? $_ : "NULL"} @values;
# my sql statement.
my $sql = "INSERT INTO msg ( " . join (",\n", @keys) . ") " .
"VALUES " . join (",\n", @values);
# or with DBI: (but not so good for printing)
# you don't need to use the $dbh->quote above if
# you use this.
my $sql2 = $dbh->do(
"INSERT INTO msg ( " . join (",\n", @keys) . ") " .
"VALUES " . substr(("?,\n" x @values), 0, -2),
undef,
@values);
I like DBI. But a friend who doesn't like his SQL to mess
up his code puts all of the queries into a module and then
calls them functionally. eg:
insert($table, @keys, @values);
# or possibly
insert($table, $data_ref);
# and definately
$data_ref = select($table, $conditions_ref, $desired);
That works as well of course.
Jacinta
