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. :)
| [reply]
[d/l]
[select] |
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. :)
| [reply]
[d/l] |
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.
- 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)
);
- 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
- 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
| [reply]
[d/l]
[select] |
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. | [reply]
[d/l]
[select] |
while ($p=$sth->fetchrow_hashref) {
push(@arr,{ # create anonymous hash
aleph=>$p->{foo},
bet=>something($p->{bar}),
baz=>"etc"});
}
| [reply]
[d/l] |
| [reply] |
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."
| [reply]
[d/l] |