Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer
 
PerlMonks  

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..

Comment on Outputing data from mySQL query into format for use with HTML::Template
Select or Download Code
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.

    Chris
    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 http://www.tt2.org. 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.

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 - mneylon-pm@masemware.com || "You've left the lens cap of your mind on again, Pinky" - The Brain
(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().

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others cooling their heels in the Monastery: (5)
As of 2014-10-01 10:40 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    What is your favourite meta-syntactic variable name?














    Results (6 votes), past polls