[dancer-users] Database template not working (and also my Dancer2 code)

[Warren Young]

On Jul 1, 2017, at 8:41 PM, CHONG Yu Meng <yumeng.chong at invictus.com.sg> wrote:
> 
> subscribers => $sth -> fetchrow_hashref('id’)

Do you really want just one row here?

If so, your SELECT statement should have a WHERE clause that matches only one row, and you should be checking for an empty result set.

If you’re actually intending to pass all subscribers to the template, you probably want fetchall_arrayref({});  That is, you want a reference to an array of hashrefs.

Alternately, since you’re specifying the columns by name in the SELECT statement, you could probably get DBI to give you a reference to an array of arrayrefs, and then index by column ID, but that’s a bit more brittle in the face of future changes.

> <% FOREACH id IN subscribers.keys.nsort %>
> <tr> <td><% subscribers.$id.displayname %></td> <td><% subscribers.$id.emailaddr %></td> </tr>
> <% END %>

I don’t use Template Toolkit, so I’m just going on what I can see by Googling things, but give the array-of-hashes option above, I think you want something more like this:

<% FOREACH s IN subscribers %>
<tr> <td><% s.displayname %></td> <td><% s.emailaddr %></td> </tr>
<% END %>

That is, you’re taking $subscribers as an arrayref, getting each subscriber in whatever order the DB returns it, and then indexing into each row by column name.

If you want a different row ordering, you’d add an ORDER BY clause to the SELECT statement.  This is also why I think you probably don’t want fetchall_hashref(): ordering for display will be harder than letting the DBMS sort for you.