Beefy Boxes and Bandwidth Generously Provided by pair Networks DiBona
Perl-Sensitive Sunglasses
 
PerlMonks  

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

by hiseldl (Priest)
on Aug 04, 2002 at 03:44 UTC ( #187448=snippet: print w/ replies, xml ) Need Help??

Description: 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>');


Comment on Get a hash object from a database with DBI for use with HTML::Template
Download Code
Re: Get a hash object from a database with DBI for use with HTML::Template
by Cine (Friar) on Aug 04, 2002 at 14:36 UTC
    A couple of comments on your code:
    • You should use placeholders for values where possible for many reasons, including security, problems with quoting etc. That is you should change
      my $stmt = "SELECT * FROM $table WHERE $key_col = '$key_val'"; my $sth = $dbhandle->prepare( $stmt ); $sth->execute();
      into
      my $stmt = "SELECT * FROM $table WHERE $key_col = ?"; my $sth = $dbhandle->prepare( $stmt ); $sth->execute($key_val);
    • You should errorcheck your database stuff. Add a || die $DBI::errstr to all your DBI->connect, $dbh->prepare and $sth->execute.
    • You only need two row, so only get those. That is change:
      my $ref = $sth->fetchall_arrayref(); $sth->finish(); return map { $_->[$name_col] => $_->[$val_col] } @$ref;
      into
      my $ref = $sth->fetchall_hashref([$name_col,$val_col]); $sth->finish(); return map {@$_} @$ref;
    • print "Content-Type: text/html\n\n"; is wrong. The HTTP standard says it should be \r\n\r\n. However most browsers dont care ;)
    • Dont use a & in a sub call without needing it. It overrides any prototype you may add at a later time to make perl check the number/type of params to get_hash.
    • Dont overgeneralize. If you only use get_hash for this specific purpose, then fill in the tablename/colname and select only the two rows you need instead of *.


    T I M T O W T D I

      "Add a || die $DBI::errstr to all ... "

      Too much typing, especially after the code has already been typed. Just add {RaiseError => 1} to connect():
      my $dbh = DBI->connect( "dbi:mysql:mydb", "user", "pass", {RaiseError => 1}, );

      "Dont use a & in a sub call without needing it. It overrides any prototype you may add ..."

      Perl prototypes really are not very good, IMHO. I prefer to avoid them, opting for good documentation instead. Check out (tye)Re: A question of style for some good info.

      jeffa

      L-LL-L--L-LL-L--L-LL-L--
      -R--R-RR-R--R-RR-R--R-RR
      B--B--B--B--B--B--B--B--
      H---H---H---H---H---H---
      (the triplet paradiddle with high-hat)
      
Re: Get a hash object from a database with DBI for use with HTML::Template
by dug (Chaplain) on Aug 04, 2002 at 23:19 UTC
      Warning: this reads the entire data set into memory, so be careful not to read huge amounts of data.

    You can avoid this by creating other problems :)
    Inside of get_hash(),
    my $sth = $dbhandle->prepare( $stmt );
    can become
    my $sth = $dbhandle->prepare( $stmt, {"mysql_use_result" => 1} );
    This will block other processes, and you will need to roll throught the complete dataset with fetchall_* or fetchrow_* and friends to avoid future errors on the handle unless you want to set up a brand new database for each query. It does seem to make sense some places, however.

      dug

Back to Snippets Section

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: snippet [id://187448]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others studying the Monastery: (6)
As of 2014-04-20 14:37 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    April first is:







    Results (485 votes), past polls