Beefy Boxes and Bandwidth Generously Provided by pair Networks
go ahead... be a heretic
 
PerlMonks  

AoH refs for setting HTML::Template loops

by bradcathey (Prior)
on May 08, 2004 at 14:15 UTC ( #351713=perlquestion: print w/replies, xml ) Need Help??

bradcathey has asked for the wisdom of the Perl Monks concerning the following question:

Fellow monasterians,

I'm still trying to get my head around references and was doing okay until this bit of code where I must set an HTML::Template loop with a AoH. I'm fine when I fetchall_arrayref() and set the param with the AoH reference directly:

my $branches = $sth->fetchall_arrayref(); ... $template->param( branches => $branches );

But when I try to build a new AoH for the purposes of the application's functionality and set that ref, I get an error of trying to set the "param with a scalar." I tried this:

my $branchaoh = \$selbranch;

and

my $branchaoh = \@selbranch;

after the for loops and my @selbranch; before the loops and got strict errors for both. So, question: how do I make my $selbranch an acceptable reference for H::T? Background questions: when does $selbranch become an array? a reference? Here's the whole deal:

my $stmt = "SELECT id, name FROM branches"; &execute_it; my $branches = $sth->fetchall_arrayref(); $stmt = "SELECT branches FROM users WHERE username = '$user'"; &execute_it; my @sqldata = $sth->fetchrow_array(); my @areas = split (/,/, $sqldata[0]); my $selbranch; foreach my $i (0 .. $#areas) { foreach my $j (0 .. $#$branches) { if ($areas[$i] == $branches->[$j]{'id'}) { $selbranch->[$i]{ 'value' } = $areas[$i]; $selbranch->[$i]{ 'branch' } = $branches->[$j]{'name'}; } } } my $template = HTML::Template -> new(filename => "../xm_dialogs/editme +nu.tmpl"); $template->param( branches => $selbranch ); #--------------------- sub execute_it { $sth = $dbh->prepare($stmt) or die "prepare: $stmt: $DBI::errstr"; $sth->execute() or die "execute: $stmt: $DBI::errstr"; }

Thanks all.


—Brad
"A little yeast leavens the whole dough."

Replies are listed 'Best First'.
OT: Database table design
by jeffa (Bishop) on May 08, 2004 at 14:54 UTC
    It appears to me that your usertable looks something like this:
     id     username     branches
      1     vroom        gods,QandA,janitors
      2     merlyn       janitors
      3     Ovid         power users,janitors
      4     dws          power users,janitors,pmdev
    
    This is violating the First Normal Form, which roughly states that no field of a table may contain multiple values. That's why we use Relational Databases in the first place, so that we don't have to use an outside Programming Language to split the values apart. Instead, you make another table, called branch or branches:
     id  name
      1  gods
      2  QandA
      3  janitors
      4  power users
      5  pmdev
    
    Now, if a given user can only belong to one 'branch', then our user table might look like: (2nd Normal Form)
     id     username     branch_id
      1     vroom        1
      2     merlyn       3
      3     Ovid         4
      4     dws          4
    
    If we need to pull out the branches, SQL to the rescue!
    SELECT user.username, branch.name FROM user INNER JOIN branch ON user.branch_id = branch.id
    However, a given user can belong to multiple branches, so we need employ the 3rd Normal Form. Start by removing the branch_id column from the user table, then create a new table - a 3rd table that will join Users to Branches: (user_branches)
    user_id branch_id
       1        1
       1        2
       1        3    (vroom belongs to 1, 2, and 3)
      -------------
       2        3    (merlyn is a janitor)
      -------------
       3        4
       3        3    (Ovid is power user and janitor)
      -------------
       4        4
       4        3
       4        5
    
    The SQL to join all three tables together is trickier, but the more you practice, the better you get. Here it is:
    SELECT user.username, branch.name FROM user INNER JOIN user_branches ON user.id = user_branches.user_id INNER JOIN branch ON user_branches.branch_id = branch.id
    If you database tables are set up "properly", then you minimize the amount of work you have to do youself in Perl. Let the database do that work for you. :)

    jeffa

    L-LL-L--L-LL-L--L-LL-L--
    -R--R-RR-R--R-RR-R--R-RR
    B--B--B--B--B--B--B--B--
    H---H---H---H---H---H---
    (the triplet paradiddle with high-hat)
    
Re: AoH refs for setting HTML::Template loops
by jeffa (Bishop) on May 08, 2004 at 14:33 UTC

    Where is the data? What does it look like? Why are you splitting data retrieved from a database? Why didn't you design your database to handle this instead? Why are you calling the subroutine execute_it with no arguments? And why aren't you using Data::Dumper for debugging your data structures?

    So many problems bradcathey. Try this instead: (untested)

    use DBI; use HTML::Template; use Data::Dumper; my $dbh = DBI->connect( qw(DBI:vendor:database:host user pass), {RaiseError => 1}, ); my $username = 'jeffa'; my $branches = $dbh->selectall_arrayref( 'SELECT branches FROM users WHERE username = ?', {Slice => {}}, $username, ); warn Dumper $branches; my $tmpl = HTML::Template->new(filehandle => \*DATA); $tmpl->param(branches => $branches); print $tmpl->output; __DATA__ <tmpl_loop branches> <tmpl_var branches> </tmpl_loop>
    and let me know how it goes. :)

    jeffa

    L-LL-L--L-LL-L--L-LL-L--
    -R--R-RR-R--R-RR-R--R-RR
    B--B--B--B--B--B--B--B--
    H---H---H---H---H---H---
    (the triplet paradiddle with high-hat)
    
Re: AoH refs for setting HTML::Template loops
by dragonchild (Archbishop) on May 08, 2004 at 14:59 UTC
    Try fetchall_arrayref( {} ); instead of fetchall_arrayref();, see if that works. Oh, and get rid of all your foreach stuff. Changing your fetchall_arrayref() call puts it in the format H::T is expecting for a TMPL_LOOP.

    Oh - you probably will want to, at some point, change your database.

    1. You're storing a bunch of stuff in a comma-delimited list. That means you need a cross-reference table. Something like (in Oracle's DDL):
      CREATE TABLE USER_BRANCH_XREF ( USER VARCHAR2(20) NOT NULL REFERENCES USERS(USERNAME) ,BRANCH NUMBER NOT NULL REFERENCES BRANCHES(ID) ,CONSTRAINT PRIMARY KEY (USER, BRANCH) );
    2. Then, you have your users in one table and your branches in another. If a user has a connection to a branch, then the user's id and the branch's id are both put into the XREF table. So, if you now want to find all branches for a given user, do something like:
      SELECT branches.id AS value ,branches.name AS branch FROM users ,branches ,user_branch_xref WHERE users.username = ? AND user_branch_xref.user = users.username AND user_branch_xref.branch = branches.id
    3. Then, you take the return value from fetchall_arrayref({}) from executing that statement (passing the $user in to the execute() call) and pass it directly to H::T.

    Putting it all together, you would have:

    my $sql = <<__END_SQL__; SELECT branches.id AS value ,branches.name AS branch FROM branches ,user_branch_xref WHERE user_branch_xref.user = ? AND user_branch_xref.branch = branches.id __END_SQL__ my $sth = $dbh->prepare_cached( $sql ) or die $DBI::errstr; $sth->execute( $user ) or die $DBI::errstr; my $template = HTML::Template -> new( filename => "../xm_dialogs/editmenu.tmpl", ); $template->param( branches => $sth->fetchall_arrayref( {} ), );

    ------
    We are the carpenters and bricklayers of the Information Age.

    Then there are Damian modules.... *sigh* ... that's not about being less-lazy -- that's about being on some really good drugs -- you know, there is no spoon. - flyingmoose

    I shouldn't have to say this, but any code, unless otherwise stated, is untested

Re: AoH refs for setting HTML::Template loops
by dave_the_m (Monsignor) on May 08, 2004 at 14:39 UTC
    when does $selbranch become an array? a reference?
    The code $selbranch->[$i]{'value'}=something will, assuming $selbranch is currently undefined, create a new anonymous array, make $selbranch a reference to it, then create a new anonymous hash, and make $selbranch->[$i] a reference to it, then store the value in the 'value' slot of the hash. So it looks like you're doing the right thing. I'd recommend doing
    use Data::Dumper; print Dumper($branch); print Dumper($selbranch);
    to see what data structures you've actually got.
Re: AoH refs for setting HTML::Template loops
by matija (Priest) on May 08, 2004 at 14:56 UTC
    I usualy create my AoH for HTML::Template like this:
    while ($p=$sth->fetchrow_hashref) { push(@arr,{ # create anonymous hash aleph=>$p->{foo}, bet=>something($p->{bar}), baz=>"etc"}); }
Re: AoH refs for setting HTML::Template loops
by bradcathey (Prior) on May 08, 2004 at 15:13 UTC
    Yikes! Looks like I need to back off, do some reading, and rework my database first. Then the rest should be come easy...not the Rube Goldberg I have here. And yes, I really do use Data::Dumper :-) As always, monks, thanks all for the help.

    —Brad
    "A little yeast leavens the whole dough."

      Spent a few hours reading, and ended up combining some of jeffa's node and some of dragonchild's, all with much consideration of gmax's educational nodes on database programming.

      Here's the much reduced, working version:

      my $stmt = "SELECT xm_branches.id AS value, xm_branches.name AS branch + FROM xm_users, xm_branches, xm_userbranches WHERE xm_users.username = '$user' AND xm_userbranches.user_id = xm_users.id AND xm_userbranches.branch_id = xm_branches.id"; &execute_it($stmt); $template = HTML::Template -> new(filename => "../xm_dialogs/editmenu. +tmpl"); $template->param( branches => $sth->fetchall_arrayref({}));

      Lessons learned: normalize that database and let the it do the work (also some unseen DBI stuff from gmax). Still need to work on INNER JOINs. Thanks all.


      —Brad
      "A little yeast leavens the whole dough."

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://351713]
Approved by matija
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others drinking their drinks and smoking their pipes about the Monastery: (6)
As of 2021-05-15 20:10 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Perl 7 will be out ...





    Results (150 votes). Check out past polls.

    Notices?