html-template-users

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

[C H. <hag...@ep...>]

I'm hoping for some conceptual help here....
I have tried to find references to my question without
success ( the list-archive link appears not to be working).

System info: Linux - Perl - MySql

I'm fairly comfortable at this stage with H::T,
and am also fairly comfortable with the DBI/MySql
interface.

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";
}

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 tried googling, and did find a few references on this topic,
but I had difficulty understanding them. I'm no wizard when
it comes to the object oriented side of things, or MySql
retrievals in general.

I have found that (for me) retrieving data using fetchrow_hashref
is easier 'cause the info can be gleaned using the field names,
where (it seems to me), fetchrow_array is based on the numerical
order/array-reference of the items, and is easier for me to screw up.

At any rate, I think I'm close to understanding it, but am hoping
someone can explain the process in a pretty basic way. I think
I understand the fundamentals, but am really struggling
trying to understand how the info gets tossed into some
sort of container so it can be sent out to a <TEMPL_LOOP>
call in the related template.


Carl Hagstrom

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

[Puneet K. <pk...@ei...>]

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.

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

[Timm M. <tm...@ag...>]

At 09:21 AM 4/14/04 +1000, Mathew Robertson wrote:
> > > 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...
>
>What has Perl's string interpolation got to do with DBI?
>
>"SELECT * FROM $working_tbl WHERE first='one' ORDER BY Location"
>
>The variable $working_tbl will be interpolated by Perl, long before DBI 
>ever gets to see the SQL statement, due to the double-quote.

$working_tbl may come from some kind of user input, which opens you up to 
SQL injection attacks.  Most databases don't allow placeholders for the 
table name.  It's best to use DBI->quote() whenever you absolutely must 
interpolate a variable into an SQL statement.


>Mathew
>
>
>-------------------------------------------------------
>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.
>_______________________________________________
>Html-template-users mailing list
>Htm...@li...
>https://lists.sourceforge.net/lists/listinfo/html-template-users

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

[Mathew R. <mat...@re...>]

> >What has Perl's string interpolation got to do with DBI?
> >
> >"SELECT * FROM $working_tbl WHERE first=3D'one' ORDER BY Location"
> >
> >The variable $working_tbl will be interpolated by Perl, long before =
DBI=20
> >ever gets to see the SQL statement, due to the double-quote.
>=20
> $working_tbl may come from some kind of user input, which opens you up =
to=20
> SQL injection attacks.  Most databases don't allow placeholders for =
the=20
> table name.  It's best to use DBI->quote() whenever you absolutely =
must=20
> interpolate a variable into an SQL statement.

no - you should never take any kind of user input without doing input =
validation -> otherwsie you are asking for trouble.

Also, DBI->quote() will not help with the example written above, as Perl =
will interpolate the vairable into to string, due to the double-quotes.  =
You are correct that most databases wont use variable table names, but =
the syntax was not written like:

"SELECT * FROM ? WHERE ..."

Mathew

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

[petersm <pe...@ve...>]

C Hagstrom wrote:
> I'm hoping for some conceptual help here....
> I have tried to find references to my question without
> success ( the list-archive link appears not to be working).
> 
> 
> 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";
> }
> 
> 
> 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


you can use something like this to get the structure from DBI

$sth = $dbh->prepare("SELECT FirstName, LastName FROM Person WHERE ...");
#this will get an array of hashrefs
my $result = $sth->fetchall_arrayref({});

then you would put that structure into your template like so...
my $tmpl->HTML::Template->new(..);
$tmpl->param('Person_Loop' => $result);

and then your template could look like this

<TMPL_LOOP Person_Loop>
  Hello, My name is <TMPL_VAR FirstName> <TMPL_VAR LastName>.<br />
</TMPL_LOOP>

Does that help?

Michael Peters
Venzia

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

[Keith J. <kja...@ey...>]

Here is a code snippet that uses fetchrow_hashref and pushs the records
into an array to pass to a template...

------------------------------------
my $sth = $dbh->prepare(qq(select * from shopping_cart));
$sth->execute();
while($hash_ref = $sth->fetchrow_hashref()){
	push(@records, $hash_ref);
}

use HTML::Template;
my $template = new HTML::Template(filename=>cart.html);
$template->param(records => \@records);
print $template->output();
-------------------------------------

Hope that helps....



On Tue, 2004-04-13 at 10:38, C Hagstrom wrote:
> I'm hoping for some conceptual help here....
> I have tried to find references to my question without
> success ( the list-archive link appears not to be working).
> 
> System info: Linux - Perl - MySql
> 
> I'm fairly comfortable at this stage with H::T,
> and am also fairly comfortable with the DBI/MySql
> interface.
> 
> 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";
> }
> 
> 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 tried googling, and did find a few references on this topic,
> but I had difficulty understanding them. I'm no wizard when
> it comes to the object oriented side of things, or MySql
> retrievals in general.
> 
> I have found that (for me) retrieving data using fetchrow_hashref
> is easier 'cause the info can be gleaned using the field names,
> where (it seems to me), fetchrow_array is based on the numerical
> order/array-reference of the items, and is easier for me to screw up.
> 
> At any rate, I think I'm close to understanding it, but am hoping
> someone can explain the process in a pretty basic way. I think
> I understand the fundamentals, but am really struggling
> trying to understand how the info gets tossed into some
> sort of container so it can be sent out to a <TEMPL_LOOP>
> call in the related template.
> 
> 
> Carl Hagstrom
> 
> 
> 
> 
> 
> -------------------------------------------------------
> 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
> Htm...@li...
> https://lists.sourceforge.net/lists/listinfo/html-template-users
-- 
Keith Jackson <kja...@ey...>

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

[Mathew R. <mat...@re...>]

> > I can get the results to print directly with this code:
> >=20
> > $sth =3D $dbh->prepare ("SELECT * FROM $working_tbl WHERE =
first=3D'one'=20
> > ORDER BY Location");
> > $sth->execute ();
> > while ( my $hash_ref =3D $sth->fetchrow_hashref ) {
> >         print "ID: $hash_ref->{'ID'} -- Name: $hash_ref->{'Name'} =20
> > (etc....) <br>\n";
> > }

> this is cool, but do note that the DBI docs generally advise against =
the=20
> 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=20
> query. Anyway...

What has Perl's string interpolation got to do with DBI?=20

"SELECT * FROM $working_tbl WHERE first=3D'one' ORDER BY Location"

The variable $working_tbl will be interpolated by Perl, long before DBI =
ever gets to see the SQL statement, due to the double-quote.

Mathew

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

[Puneet K. <pk...@ei...>]

On Apr 13, 2004, at 6:21 PM, Mathew Robertson wrote:

>>> 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...
>
> What has Perl's string interpolation got to do with DBI?
>
> "SELECT * FROM $working_tbl WHERE first='one' ORDER BY Location"
>
> The variable $working_tbl will be interpolated by Perl, long before 
> DBI ever gets to see the SQL statement, due to the double-quote.
>
>

you are correct, and my advise was half-baked. The assertion I made 
holds true when doing multiple queries using bind variables, and hence, 
I try to stay away from variable-izing table names... that is where my 
advise came from and it really doesn't apply to Carl's case. But, as 
they say... you gotta do what you gotta do.