RE: [htmltmpl] looping through a DB result_set - LONG

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 454-5900

Michael,

It appears that you're not creating your nested loop properly.  For each
iteration of the CATEGORY_ROWS array (ie for each hash), there needs to be a
reference to an array of hashes, which is the IMAGE_ROWS inner loop.

...

push @category_rows, { name => $name, IMAGE_ROWS => \@image_rows };
$template->param( { CATEGORY_ROWS => \@category_rows } );

...

To accomplish this you will have to execute your 2nd query for each result
of the first query:

my @category_rows = ();
#loop through results of 1st query

while($data = $sth->fetchrow_hashref){

	#2nd query
    $sth = $dbh->prepare("select image_name, category from card_images where
category = 	'$name' order by image_name");
    $sth->execute;
    $sth->bind_columns(\my($image_name, $category));

	# VERY Important step
	my @image_rows = ();  

	#loop through results of 2nd query.
	while($data = $sth->fetchrow_hashref){
	    push @image_rows, { image_name => $image_name,  category =>
$category};
	}

    push @category_rows, { name => $name, IMAGE_ROWS => \@image_rows };
}

Please validate syntax as I haven't had an opportunity to check it myself.

I hope I understood the problem correctly.
Hope this helps.
-Dan

-----Original Message-----
From: l8x...@sn... [mailto:l8x...@sn...]
Sent: Tuesday, November 26, 2002 12:57 PM
To: htm...@li...
Subject: [htmltmpl] looping through a DB result_set - LONG

Gooday folks,

First off I do apologize for my rather verbose email,
but I thought I should include as much detail as possible.

I'm creating my first project with HTML::Template and although it has been
a bit of a mind-boggler learning all the new methods used by HT, I can see
how future
development will really be helped and I quite like this method.

I have hit a bit of a snag tho and its in regards to "loops".
As the docs say "<TMPL_LOOP> is a bit complicated to grasp at first, but it
is very powerful."

I have tried everything I can come up with and I am just baffled on how to
achieve
the desired results.... major brain_fart.

I'm querying out of a MySQL db for a "category" field in one table and an
"image_name"
field from another and then trying to loop through the result_set to produce
an output
similar to this. Category followed by and limited to 5 images results
"foreach" category.
----------------------------------------------------------------------------
-----------------------
Xmas
-------
image_name1.jpg  image_name2.jpg image_name3.jpg  image_name4.jpg
image_name5.jpg

Halloween
------------
image_name1.jpg  image_name2.jpg image_name3.jpg  image_name4.jpg
image_name5.jpg

Thanksgiving
---------------
image_name1.jpg  image_name2.jpg image_name3.jpg  image_name4.jpg
image_name5.jpg

The category names are unique, thus the routine just needs to grab five
random images from the
images table "foreach" unique category queried from the DB.

Below is where I left off after days of fiddling and geting crossed-eyed
trying to grasp the logic
....please excuse its "simplicity".... but its a steep learning curve for
me. :-)  and I feel I get close..then lose it
code wise.
----------------------------------------------------------------------------
---------------------------

$sth = $dbh->prepare('select name from categories order by name');
    $sth->execute;
    $sth->bind_columns(\my($name));

while($data = $sth->fetchrow_hashref){

    push @category_rows, { name => $name };

}

   # hard code category var
    my $category = "Xmas";

    #$sth = $dbh->prepare("select image_name, category from card_images
where category = '$category' order by image_name");
    # die "DBI error with prepare:", $sth->errstr unless $sth;
    #$sth->execute();
    #my $image_rows;
    #push @{$image_rows}, $_ while $_ = $sth->fetchrow_hashref();

    $sth = $dbh->prepare("select image_name, category from card_images where
category = '$category' order by image_name");
    $sth->execute;
    $sth->bind_columns(\my($image_name, $category));

while($data = $sth->fetchrow_hashref){

    push @image_rows, { image_name => $image_name,  category => $category};

}

     $sth->finish;

$dbh->disconnect;

my $template = HTML::Template->new(filename => 'in_out_loop.html',

die_on_bad_params => 0,

global_vars => 1,

associate => $query
                                                      );

$template->param( {
                            CATEGORY_ROWS => \@category_rows,
                            IMAGE_ROWS => \@image_rows,
                           } );

----------------------------------------------------------------------------
--------------------------------
Below is my template file with the various loops.

 category_loop
 <p>
<TMPL_LOOP NAME=CATEGORY_ROWS>
    <TMPL_VAR NAME=name><br>
</TMPL_LOOP>
----------------------------------------------
<p>

image_loop
<p>
<TMPL_LOOP NAME=IMAGE_ROWS>
    <TMPL_VAR NAME=image_name> - <TMPL_VAR NAME=category><br>
</TMPL_LOOP>
-------------------------------------
<p>

in_out_loop
<p>
<TMPL_LOOP NAME=CATEGORY_ROWS>
    OUTER CATEGORY : <TMPL_VAR NAME=name><br>
       <TMPL_LOOP NAME=IMAGE_ROWS>
           INNER IMAGE_NAME : <TMPL_VAR NAME=image_name><br>
            INSIDE OUT CATEGORY: <TMPL_VAR NAME=name><br>
    </TMPL_LOOP>
</TMPL_LOOP>
      <p>
      ------------------------------------
     <p>
  <TMPL_LOOP NAME="CATEGORY_ROWS">
     <TMPL_VAR NAME="name"><br>
     <TMPL_LOOP NAME="IMAGE_ROWS">
       <TMPL_VAR NAME="image_name"> <br>
     </TMPL_LOOP>
   </TMPL_LOOP>

And finally the results of my template with hard coded category var
my $category = "Xmas";  for an example.
-------------------------------------------------

category_loop

Halloween
Xmas
----------------------------------------------

image_loop

bunny.jpg - Xmas
kitten.jpg - Xmas
-------------------------------------

in_out_loop

OUTER CATEGORY : Halloween
INNER IMAGE_NAME : bunny.jpg
INSIDE OUT CATEGORY: Halloween
INNER IMAGE_NAME : kitten.jpg
INSIDE OUT CATEGORY: Halloween
OUTER CATEGORY : Xmas
INNER IMAGE_NAME : bunny.jpg
INSIDE OUT CATEGORY: Xmas
INNER IMAGE_NAME : kitten.jpg
INSIDE OUT CATEGORY: Xmas

------------------------------------

Halloween
bunny.jpg
kitten.jpg

Xmas
bunny.jpg
kitten.jpg

Could someone please point me in the right direction, with a bit of an code
example
or perhaps some documentation..with examples of something similar to get me
moving again
on my project...as I'm stuck at this juncture.

I have read the crap out of any docs I could find and have spent days
pounding my head on the
wall and its only after this point I have come to the list to ask for help.

Any help would be greatly appreciated and I do thank you in advance for your
time and troubles.

--

michael

-------------------------------------------------------
This SF.net email is sponsored by: Get the new Palm Tungsten T 
handheld. Power & Color in a compact size! 
http://ads.sourceforge.net/cgi-bin/redirect.pl?palm0002en
_______________________________________________
Html-template-users mailing list
Htm...@li...
https://lists.sourceforge.net/lists/listinfo/html-template-users