HTML::Template and DBI

2000-10-03
2001-03-15
  • tomaso scarsi

    tomaso scarsi - 2000-10-03

    I'd like to see an example on how to put the result of an SQL select statement in a  TMPL_LOOP.

    Maybe with some kind of fetchall_arrayref()...

    thanks
    tomaso

     
    • Sam Tregar

      Sam Tregar - 2000-10-08

      Here's something like what I usually do:

      my $resultref = $dbh->selectall_arrayref('SELECT name, age, sex FROM People');
      die("Database Error: " . $dbh->errstr) unless defined $resultref;

      my @loop = map { { name => $_->[0], age => $_->[1], sex => $_->[2] } } @$resultref;
      $template->param(LOOP => \@loop);

      That map statement can be replaced by a longer 'foreach/push' loop if you like, but the activity is the same.

      -sam

       
      • Andrey Melnikov

        Andrey Melnikov - 2001-03-02

        Sam: I think you use this ugly map statement only because you need to change array of arrayrefs to array of hashrefs.. Right?

        I use this piece of code:

        while ($row=$sth->fetchrow_hashref)
        {
          push @loop, $row;
        }

        As you see, I don't need any mappings.

        But selectall_arrayref CAN return array of hashrefs. So maybe there is even better way to create TMPL_LOOP out of SQL query

         
        • Sam Tregar

          Sam Tregar - 2001-03-02

          > I think you use this ugly map statement only
          > because you need to change array of arrayrefs
          > to array of hashrefs.. Right?

          Wrong.  I do it for performance.  selectall_arrayref is considerably faster than fetchrow_hashref in a loop.  Also, I like map.

          -sam

           
          • Andrey Melnikov

            Andrey Melnikov - 2001-03-04

            But you use selectall_arrayref - it's selectall_arrayref + map. Is it still faster? I will check...

            IMHO, my loop looks shorter and more beautiful, and I don't like map :-) , because in this case my code becomes not suitable for further reading and debugging. For example, I can easily change the hashref before PUSH or can delete unnecessary fields.

            There is always a compromise between efficiency and beauty/readability. Hashes are in most cases slower than arrays, but we don't use <TMPL_VAR 0> and <TMPL_IF 5>, we use <TMPL_VAR foo> and <TMPL_IF bar> because we want our template to be understood better.

             
          • Andrey Melnikov

            Andrey Melnikov - 2001-03-15

            I think that I found the most beautiful way to make TMPL_LOOPs out of SQL queries.
            SQLQuery encapsulates prepare+execute.

            my $sth = SQLQuery($dbh,$dbq) or die;
            my %NULL;
            my $loop = $sth->fetchall_arrayref(\%NULL);
            $template->param('loop'=>$loop);

            What is the best way to test the performance?
            Are there any standard methods/utilities?

             

Log in to post a comment.

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:





No, thanks