I needed a way to get name/value pairs from a table using another column as the "object ID." This allowed me to have access to the values in a hash instead of having to do multiple selects.
Warning: this reads the entire data set into memory, so be careful not to read huge amounts of data.
I'm using &get_hash(...) to create a hash filled with values to fill a template created with HTML::Template, I'm also fetching the template from another table in my real script; I simplified the snippet by putting the template in with the attributes with a lookup of "page". With this method, I can use two select statements with one connect statement to create a dynamic web page instead of a file load and several select statements. :)
Update: added {RaiseError=>1}, put parameter binding in get_hash(...), and added array slice in $sth->fetchall_arrayref().
--
hiseldl
#!/usr/bin/perl -w
use strict;
use DBI;
use HTML::Template;
####################################################
# Example usage
####################################################
# connect to the database
my $dbh = DBI->connect("dbi:mysql:mydb", "user", "pass",
{RaiseError=>1} );
# create the hash from the name/value pairs in the db
# using object_id="1"
my %params = &get_hash($dbh, "hashobjects", 1, 2, "object_id", "1" );
# create the html template with the page value from the DB
my $template = HTML::Template->new(scalarref => \$params{page},
die_on_bad_params => 0,
);
# substitute the params in the template
$template->param(\%params);
# print everything out
print "Content-Type: text/html\n\n";
print $template->output;
# disconnect so we don't get the warning,
# "Database handle destroyed without explicit disconnect."
$dbh->disconnect();
exit 0;
####################################################
# END Example usage
####################################################
####################################################
####################################################
# get_hash
# params:
# $dbhandle = database handle created with DBI->connect()
# $name_col = array index of column in the record set
# used for the key of the hash
# $val_col = array index of column in the record set
# used for the value of the hash
# $key_col = column name in the record set
# used for the key of the object id
# (this is the column name used in the WHERE clause)
# $key_val = column name in the record set
# used for the value of the object id
# (this is the column value used in the WHERE clause)
#
sub get_hash {
my ($dbhandle,$table,$name_col,$val_col,$key_col,$key_val)=@_;
my $stmt = "SELECT * FROM $table WHERE $key_col = ?";
my $sth = $dbhandle->prepare( $stmt );
$sth->execute($key_val);
# Here's where we read in the entire data set into
# an array ref containing refs to record arrays.
#my $ref=$sth->fetchall_arrayref();
my $ref = $sth->fetchall_arrayref([$name_col,$val_col]);
$sth->finish();
# use map to turn the array ref of array refs
# into a hash and then return it anonymously
#return map { $_->[$name_col]=>$_->[$val_col] } @$ref;
return map {@$_} @$ref;
}
####################################################
####################################################
__END__
# Here is a simplified version of the table
# along with some data to make the example
# run. This should work in MySQL.
# hashobjects.sql
#
# 1. save this data to a file
# 2. to create the table and view the data,
# at the mysql prompt type:
# mysql> use mydb;
# mysql> \. hashobjects.sql
# mysql> describe hashobjects;
# mysql> select * from hashobjects;
#
# Table structure for table `hashobjects`
#
CREATE TABLE hashobjects (
object_id int(11) NOT NULL,
name varchar(255) NOT NULL,
value text NOT NULL
);
insert into hashobjects values (1, 'firstname', 'Jack');
insert into hashobjects values (1, 'bgcolor', '#FFFFFF');
insert into hashobjects values (1, 'page', '<HTML><HEAD></HEAD>
<BODY BGCOLOR="<TMPL_VAR NAME="bgcolor">">
Welcome, <TMPL_VAR NAME="firstname">!<P>
</BODY></HTML>');
