Beefy Boxes and Bandwidth Generously Provided by pair Networks
Keep It Simple, Stupid
 
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 contemplating the Monastery: (11)
As of 2015-07-03 11:27 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (51 votes), past polls