Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight

Outputing data from mySQL query into format for use with HTML::Template

by jarvis (Initiate)
on Mar 10, 2001 at 02:59 UTC ( #63391=perlquestion: print w/replies, xml ) Need Help??
jarvis has asked for the wisdom of the Perl Monks concerning the following question:

Hi there, can anyone help??... Please!!
I'm new to all this and i am trying to output data from a search query on mySQL to a loop variable using the loop function in HTML::Template ( <TMPL_LOOP NAME=""> <TMPL_VAR NAME=""> </TMPL_LOOP>) I understand that i have to return an array of hashes to the HTML::Template module, but i cant seem to successfully convert the output from mySQL to this array of hashes. I realise that i am missing something obvious, but i can't get my head around it. My code is something like this...
##necessary stuff, use CGI etc... use constant TMPL_FILE => "C:/Xitami/cgi-bin6/templates/mytemplate.t +mpl"; my $tmpl = new HTML::Template( filename => TMPL_FILE, no_includes => 1 ); ##some other bits, then $dbh=DBI->connect($datasource,$user,$pass,{AutoCommit=> 1}) or die " +Can't Connect"; $sth=$dbh->prepare("SELECT * FROM Videos WHERE $FieldN LIKE +?") or die; $sth->execute($SearchCrit); @vidinfo = $sth->fetchrow_array();
how do i turn this bit (above - @vidinfo)
into an array of hashes
so that this bit (below)will output
it properly???
$tmpl->param( stuff =>\@whatever ); $tmpl->output;
all help gratefully received..

Replies are listed 'Best First'.
(dkubb) Re: (2) Outputing data from mySQL query into format for use with HTML::Template
by dkubb (Deacon) on Mar 10, 2001 at 08:11 UTC

    A great way to do what you want is built right into DBI, there is no need for extra code to build the data structure:

    my %attr = ( dbi_fetchall_arrayref_attr => {}, ); my $statement = q{ SELECT * FROM table WHERE field LIKE ? }; $template->param( rows => $dbh->selectall_arrayref($statement, \%attr, $value), );

    This uses a little known, and undocumented feature in DBI, where selectall_arrayref() can be coerced into returning a reference to an array of hash references. This is done through the use of the \%attr hash reference passed to selectall_arrayref().

Re: Outputing data from mySQL query into format for use with HTML::Template
by Masem (Monsignor) on Mar 10, 2001 at 03:08 UTC
    A good way that I've found to do this is ...
    my @values; my @fields = qw( title date ...); # what fields do you want? $sth=$dbh->prepare("SELECT " . join(',' @fields) . " FROM Videos WHER +E $FieldsN LIKE ?") or die DBI->errstr; $sth->execute( $SearchCrit ) or die DBI->errstr; while( my @vidinfo = $sth->fetchrow_array() ) { my %hash; # there's probably a better way to join these arrays, # but this is the easiest to understand OTTOMH ... # basically mapping the output to the respective field # name for ( my $i = 0; $i < @fields ; $i++ ) { $hash{ $fields[ $i ] } = $vidinfo[ $i ]; } push @values, \%hash; # push a reference to the hash onto the arr +ay } $tmpl->param( stuff = \@values );

    Dr. Michael K. Neylon - || "You've left the lens cap of your mind on again, Pinky" - The Brain
Re: Outputing data from mySQL query into format for use with HTML::Template
by lachoy (Parson) on Mar 10, 2001 at 03:07 UTC

    Assuming the fieldnames in your template are the same as the fieldnames in your database, you can do:

    $sth->execute( $SearchCrit ); my @results = (); while ( my $row = $sth->fetchrow_hashref ) { push @results, \%{ $row }; } $tmpl->param( stuff => \@results ); ...

    This isn't the most efficient but it's the easiest to understand. (And for small datasets like putting tables of data in a web page, you probably won't notice the difference.)

    Note that if you try a shortcut and push $row directly into @results you'll get the same row over and over.

    M-x auto-bs-mode

      The two responses before mine do a nice job of answering you question--and I've handled this task in a similar manner.

      I have since started using Template Toolkit, available on CPAN with additional information available at I find this template module much more flexible. As a matter of fact, you may pass the database handle directly to the template, saving memory and processing.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://63391]
Approved by root
NodeReaper wonders through a maze of twisty little sayings, all different

How do I use this? | Other CB clients
Other Users?
Others avoiding work at the Monastery: (6)
As of 2018-06-22 20:09 GMT
Find Nodes?
    Voting Booth?
    Should cpanminus be part of the standard Perl release?

    Results (124 votes). Check out past polls.