http://www.perlmonks.org?node_id=187448

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

Replies are listed 'Best First'.
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