Database template not working (and also my Dancer2 code)
Hi all, Learning Dancer2 and trying to re-learn Perl, so please be patient. I've read the Manual and Tutorial, and I can get most examples working, except for querying a database and displaying the results. I am using Dancer2::Plugin::Database. I have a simple database table (jpstars): +----+-----------------+---------------------+ | id | firstname | lastname | +----+-----------------+---------------------+ | 1 | Harumi | Nemoto | | 2 | Eiko | Koike | | 3 | Ami | Ayukawa | +----+-----------------+---------------------+ I have the following Dancer2 code (dancerapp.pm): package dancerapp; use Dancer2; use Dancer2::Plugin::Database; our $VERSION = '0.1'; get '/dbtest' => sub { my $sth = database->prepare( 'SELECT id, firstname, lastname FROM jpstars;' ); $sth -> execute; template 'display_jpstars', { subscribers => $sth -> fetchrow_hashref('id') }; }; I have this template file (display_jpstars.tt) <html><head><title>JPStars</title></head> <body> <h1>JpStars</h1> <table> <tr> <th>First Name</th> <th>Last Name</th> </tr> <% FOREACH id IN subscribers.keys.nsort %> <tr> <td><% subscribers.$id.displayname %></td> <td><% subscribers.$id.emailaddr %></td> </tr> <% END %> </table> </body> </html> dance; I get an error: "Can't use attribute 'id' because it doesn't contain a reference to an array" I'm pretty sure my template code is wrong (my dancer.pm code too), but I have no idea how it should look. Help! Thanks in advance! Pascal CHONG
On Jul 1, 2017, at 8:41 PM, CHONG Yu Meng <yumeng.chong@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.
Hi Warren, Thanks for the very valuable tips! I didn't understand a lot about what I was reading in the examples and tutorials and you have helped clear up a lot of things! I'll try out a few things and report back! -----Original Message----- From: dancer-users [mailto:dancer-users-bounces@dancer.pm] On Behalf Of Warren Young Sent: Tuesday, 4 July 2017 12:58 AM To: Perl Dancer users mailing list <dancer-users@dancer.pm> Subject: Re: [dancer-users] Database template not working (and also my Dancer2 code) On Jul 1, 2017, at 8:41 PM, CHONG Yu Meng <yumeng.chong@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. _______________________________________________ dancer-users mailing list dancer-users@dancer.pm http://lists.preshweb.co.uk/mailman/listinfo/dancer-users
participants (2)
-
CHONG Yu Meng -
Warren Young