Re: [htmltmpl] [Question] H::T and DBI => stuffing query info into 's

[Puneet Kishor]

C Hagstrom wrote:

I'm hoping for some conceptual help here....
..
I have a script that queries the database, and pulls
information from numerous records ... in other words,
it grabs and displays all the record information
for a selected group of records.
I can get the results to print directly with this code:

$sth = $dbh->prepare ("SELECT * FROM $working_tbl WHERE first='one' 
ORDER BY Location");
$sth->execute ();
while ( my $hash_ref = $sth->fetchrow_hashref ) {
        print "ID: $hash_ref->{'ID'} -- Name: $hash_ref->{'Name'}  
(etc....) <br>\n";
}

this is cool, but do note that the DBI docs generally advise against the 
use of a variable table name (well... I understand them to be advising 
against) as it doesn't allow for creating a plan and optimizing the 
query. Anyway...


And before I became an H::T convert, I would use
the above approach, and craft the HTML output
directly in the script.
Now that I am an H::T convert, I'm looking for guidance
on how best to handle the database output so I can
take advantage of the <TMPL_LOOP> feature in H::T
I have posted several times about how to do this using a very simple 
approach -- the fetchall_arrayref({}) method in DBI. This fetches a ref 
to an array of hashes... exactly what the TMPL_LOOP wants.

  # Create the sql query
  my $sql = <<SQL;
  SELECT col1, col2, col3
  FROM my_working_tbl
  WHERE first = 'one'
  ORDER BY Location
  SQL
  # Create the statement handle and execute it
  my $sth = $dbh->prepare(qq{$sql});
  $sth->execute;
  # Fetch the results as a ref to an AofH
  my $res = $sth->fetchall_arrayref({});
  # Assign the ref to a var in the template
  $t->param(RES => $res);
Then in your template --

  <table>
  <tr><th>Col1</th><th>Col2</th><th>Col2</th></tr>
  <TMPL_LOOP RES>
  <tr>
        <td><TMPL_VAR COL1></td>
        <td><TMPL_VAR COL2></td>
        <td><TMPL_VAR COL3></td>
  </tr>
  </TMPL_LOOP>
  </table>
Keep in mind -- you can't use SELECT * (which is inefficient for the 
database anyway). Specify the column names you want to retrieve, and the 
column names automatically become the names of the TMPL_VARs.

I hope this helps. It really doesn't get any simpler and basic than this.

Let us know if you need further help.

Ya, the archive seems to be down, but when it comes back up (if it 
does), there are a lot of good discussions there.

Good luck.

-------------------------------------------------------
This SF.Net email is sponsored by: IBM Linux Tutorials
Free Linux tutorial presented by Daniel Robbins, President and CEO of
GenToo technologies. Learn everything from fundamentals to system
administration.http://ads.osdn.com/?ad_id=1470&alloc_id=3638&op=click
_______________________________________________
Html-template-users mailing list
[EMAIL PROTECTED]
https://lists.sourceforge.net/lists/listinfo/html-template-users