[dancer-users] Iteration of hash in template.

[Dave Cross]

Quoting Richard Reina <gatorreina at gmail.com>:

> Hello All,
>
> I ussually iterate a hash that is the result of $rows_ref =
> $sth->fetchall_hashref('ID'); in my template with  <% FOREACH ID
> Matched_pats.keys.sort %>. However, In this case though the template only
> displays one row because each resulting row in the SQL query all have the
> same ID number. Is there any simple change I can make to get the results to
> fully iterate?

If you're putting data into a hash that's keyed on ID and every row  
has the same ID then you will only ever have one entry in your hash as  
each row that DBI puts into the hash will overwrite the previous one  
with the same ID. It's the same as writing code like this:

   my $rows_ref = {};
   $rows_ref->{'an_ID'} = 'foo';
   $rows_ref->{'an_ID'} = 'bar';
   $rows_ref->{'an_ID'} = 'baz';

I hope it's obvious that this hash will only ever contain one  
key/value pair. Using fetchall_hashref('ID') with a constant value for  
'ID' has exactly the same effect.

As others have pointed out, the solution is to use a fetch method  
which returns an array rather than a hash. I'd add that in my  
experience (and I've been doing this a very long time) returning  
database data in an array (or an array of arrays) is almost always a  
better idea than returning a hash. Hashes certainly have their uses  
(that is, after all, why those methods exist), but arrays seem to  
match most people's model of a database resultset better than hashes do.

In fact, now I come to think about it, the other problem that I helped  
you with recently was only as complex as it was because your data  
structure was a hash. In my solution, the first thing I did was to  
call values() on the hash which effectively turned it into an array.

So I'd highly recommend using arrays rather than hashes in most  
instances when you're dealing with database resultsets.

I'd also like to point out that not all of your problems are Dancer  
problems. The people on this list are, of course, really helpful. But  
the Perl community has mailing lists for all sorts of things. In  
particular, you might consider joining the mailing lists for:

* DBI - http://lists.perl.org/list/dbi-users.html
* TT  - http://lists.perl.org/list/template-toolkit.html

Hope this is helpful,

Dave...