in reply to Re^3: Best way to send records to the browser?
in thread Best way to send records to the browser?

Didn't mean to make it sounds like I'm an advocate for HT over TT2. I recognize the power TT2 provides, and I like what you can do with it. Just wish you could pass it arrays directly from Perl.

My MySQL query looks like this:

# issue query
my $sth = $dbh -> prepare("SELECT uid, fname, lname FROM users") || &b
+ail_out("Error: cannot prepare DB query");
$sth -> execute() || &bail_out("Error: cannot execute DB query");

[download]

The following loop yields an array with all relevant keys and values as individual elements. The results are printed to the terminal for testing:

while (@rray = $sth -> fetchrow_array) {
    $row{uid} = $rray[0];
    $row{fname} = $rray[1];
    $row{lname} = $rray[2];
    push @rows, %row;
}

foreach (@rows) {
    print "$_\n";
}

[download]

This next variation is an attempt to build a hash instead of an array. It only builds one column (lname) from the table at this time. Keys are defined as records.

while (@rray = $sth -> fetchrow_array) {
    $record = "record_$i";
    $hash{$record} = $rray[1];
    $i += 1;
}

my ($key, $value);

while (($key, $value) = each(%hash)) {
    print "key = $key, value = $value\n";
}

[download]

The two pieces of code above work. Finally, however, my attempt to build a HoH is failing. Here is what I have so far:

while (@rray = $sth -> fetchrow_array) {
    $record = "record_$i";
    $hash1{fname} = $rray[1];
    $hash2{$record} = %hash1;
    $i += 1;
}

while (($key, $value) = each(%hash2)) {
    print $hash2{$value} -> {fname};
}

[download]

There are obviously some problems here. Can you offer me some guidance?

Thanks!

Replies are listed 'Best First'.
Re^5: Best way to send records to the browser?
by almut (Canon) on Jun 26, 2010 at 17:51 UTC 
    $hash1{fname} = $rray[1];
$hash2{$record} = %hash1;

    [download]

    Values stored in hash entries can only be scalar (not hashes or arrays), so you need to store a reference to a hash, which is scalar.  You get a reference with \ 

        $hash2{$record} = \%hash1;

    [download]

    But note that this would store references to the same global hash, in which you'd just overwrite the fname entry for every record.  You most likely want separate hashes, i.e. 

        my %hash1;  # new hash instance
    $hash1{fname} = $rray[1];
    $hash2{$record} = \%hash1;

    [download]

    or simply 

        $hash2{$record} = { fname => $rray[1] };

    [download]

    (the { } create an anonymous hash and return a reference to it)

[reply]
[d/l]
[select]

      Thank you. I will give that a try.

      In the meantime, I wrote this variation. The first loop basically works. However, just as you said, each row is getting overwritten. I guess I need to instantiate a new hash instance each time the program loops as you suggested.

      while (@rray = $sth -> fetchrow_array) {
    $record = "record_$i";
    %hash = ($record => {uid => $rray[0], fname => $rray[1], lname => 
+$rray[2]});
    print $hash{$record} -> {uid}, qq/ /, $hash{$record} -> {fname}, q
+q/ /, $hash{$record} -> {lname}, qq/\n/;
    $i += 1;
}

for ($i = 0; $i < 100; $i++) {
    print $hash{$i} -> {uid}, qq/ /, $hash2{$i} -> {fname}, qq/ /, $ha
+sh2{$i} -> {lname}, qq/\n/;
}

      [download]

      Can I simply save each new instance of %hash to a larger hash (say %hash2) each time the program loops? Or do I need to use hash references?

      I think the second loop fails because the hash is getting overwritten in the first loop. I get these errors:

      pass.cgi: Use of uninitialized value in print at ./pass.cgi line 85.

      with each loop. I did notice that the last record is intact ... which now makes sense. So I guess I just need to eliminate the overwriting in the first loop. Is this right?

      Thanks so much.

[reply]
[d/l]

        You probably want 

        $hash{$record} = {uid => $rray[0], fname => $rray[1], lname => $rray[2
+]};

        [download]

        instead of 

        %hash = ($record => {uid => $rray[0], fname => $rray[1], lname => $rra
+y[2]});

        [download]

        because the latter initializes the hash anew every time, so the old entries are lost.

        Also, in this particular case, another problem seems to be that in the first loop you're creating hash entries with keys as "record_$i", while in the second loop you're using plain numbers ($i).  Also, you probably mistyped $hash2{$i} when you meant $hash{$i}.

        More generally, there are two things that will likely save you lots of debugging time:

        • use strict; use warnings; to catch typos (like the above) and more
        • use Data::Dumper; ... print Dumper \%hash; for checking what's in a complex data structure.
[reply]
[d/l]
[select]
Re^5: Best way to send records to the browser?
by Your Mother (Archbishop) on Jun 26, 2010 at 20:33 UTC

    Here you go. This should be close to what you're doing (adjust the column names in the query). Related reading of interest: DBI recipes. 

    use warnings;
use strict;
use Template;
use DBI;

my $dbh = DBI->connect("dbi:mysql:mycow;mysql_read_default_file=$ENV{H
+OME}/.my.cnf",
                       undef, undef,
                      { RaiseError => 1 });

my $sth = $dbh->prepare("SELECT id, name, email FROM user LIMIT 3 OFFS
+ET 100");
$sth->execute();
my $employees_loh = $sth->fetchall_arrayref({});

my $tt = Template->new();

$tt->process(\*DATA,
             { users => $employees_loh })
    or die $tt->error(), "\n";

__END__
[% FOR user IN users %]
<div class="record">
  <div class="id">[% user.id %]</div>
  <div class="name">[% user.name %]</div>
  <div class="email">[% user.email %]</div>
</div>
[% END %]

 -- Output ------------------

<div class="record">
  <div class="id">101</div>
  <div class="name">Actaeonis</div>
  <div class="email">Accius@sitesviagra.com</div>
</div>

<div class="record">
  <div class="id">102</div>
  <div class="name">varvang</div>
  <div class="email">vartang@roliks.com</div>
</div>

<div class="record">
  <div class="id">103</div>
  <div class="name">JacobHorony</div>
  <div class="email">jeremilerom@bestbussiness.net</div>
</div>

    [download]

    I wasn't trying to disrespect HTML::Template too! It's a fine choice and I understand why some prefer a view without a mini-language on top.

[reply]
[d/l]

      Thanks! I'll keep this in my pocket. Different approach than what I'm taking now, but still useful.

      I'm going to take my new HoH to TT2 next. I'll post back with an update on how it goes ...

[reply]

In Section Seekers of Perl Wisdom