Get a hash object from a database with DBI for use with HTML::Template

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>');
[download]

Back to Cool Uses for Perl