comment on

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]

In reply to Get a hash object from a database with DBI for use with HTML::Template by hiseldl

Are you posting in the right place? Check out Where do I post X? to know for sure.
Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
<code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>
Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
Want more info? How to link or How to display code and escape characters are good places to start.


Problems? Is your data what you think it is?
	PerlMonks