Gossamer Forum: Products: Gossamer Links: Version 1.x: Problem with Relations: Pulling Multiple Rows

Mar 2, 2001, 7:49 PM

Stealth

Veteran (17240 posts)

Mar 2, 2001, 7:49 PM

Post #1 of 5

Shortcut

Problem with Relations: Pulling Multiple Rows

Okay...I have been struggling with this for a few weeks now...and I can't get it to work, so any suggestions would be greatly appreciated.

Okay...here is what I am attempting to do is:

Pull rows from one table called Person_Status that contains the following columns/fields:

PersonID (Index, Not Unique, no tracking)
StatusID (Index, Not Unique)

These are foreign keys for the Person and Status tables.

The Person table contains some of the following fields:

PersonID
FirstName
LastName
Email
Password

The Status table contains the following columns/fields:

StatusID (Primary, Unique)
StatusName (Unique) - [Administrator|Editor|Moderator|Registered]

I did edit the authenticate sub in the DB_Utils.pm module as follows:

Code:

sub authenticate {
# --------------------------------------------------------
# Make sure the current session is valid and gets the user info.
#
my $s = shift;
$s =~ /^[\d\w]+$/ || return undef;
(length $s < 26) || return undef;
if (! $USERDB) {
$USERDB = new AG::DBSQL $AG{def_files} ."tbl_AG_Person" . ".def";
}
my $query = qq!
SELECT *
FROM tbl_AG_Person, tbl_AG_User_Sessions
WHERE tbl_AG_Person.PersonID = tbl_AG_User_Sessions.PersonID AND tbl_AG_User_Sessions.ID = '$s'
!;
my $sth = $USERDB->prepare ($query);
$sth->execute();
my $user = undef;
if ($sth->rows) {
$user = $sth->fetchrow_hashref;
}
return $user;
}

I also added the following codes in the login.cgi script:

Code:

my $user_r = $USERDB->get_user_record ($email, 'HASH');
my $personid = $user_r->{PersonID};
my $firstname = $user_r->{FirstName};
my $lastname = $user_r->{LastName};
my $password = $user_r->{Password};
my $user_s = $USERSTDB->get_record ($personid, 'HASH');
foreach (keys %$user_s) {
$statusid = $user_s->{StatusID};
$user_r->{$_} = $user_s->{$_};
}

I also tried using hard-coded SQL queries/statements to pull the correct values, and that did not work.

In the login_success.html, I added the following tags:

(NOTE: 1 = Administrator, 2 = Editor, 3 = Moderator, 4 = Registered)

Code:

<%if StatusID eq '4'%>
USER LINKS
<%if StatusID eq '1'%>
ADMIN LINKS
<%endif%>
<%if StatusID eq '2'%>
EDITOR LINKS
<%endif%>
<%if StatusID eq '3'%>
MODERATOR LINKS
<%endif%>
<%endif%>

Now, I can login, but when I login, only one the first StatusID is printed and noticed...For example, one of the accounts I am testing with is associated with all four StatusID values...but only 1 is pulled from the table....what should happen, logically speaking, is that since I am associated with all four statuses, I should be able to access all four set of links...

Any suggestions for making this work?

Thanks in advance.

Regards,

Eliot Lee

Mar 6, 2001, 1:59 PM

Stealth

Veteran (17240 posts)

Mar 6, 2001, 1:59 PM

Post #2 of 5

Shortcut

Re: Problem with Relations: Pulling Multiple Rows In reply to

---> Bump to Top <---

Regards,

Eliot Lee

Mar 6, 2001, 5:28 PM

Alex

Administrator (9387 posts)

Mar 6, 2001, 5:28 PM

Post #3 of 5

Shortcut

Re: Problem with Relations: Pulling Multiple Rows In reply to

Hi,

You can't do that as a hash key only contains one value. I would do something like:

foreach (keys %$user_s) {
$user_r->{'IsA_' . $_} = 1;
}

and then in your template:

<%if IsA_Administrator%>
..
<%if IsA_User%>
..
etc.

Hope that helps,

Alex

--
Gossamer Threads Inc.

Mar 7, 2001, 7:35 PM

Stealth

Veteran (17240 posts)

Mar 7, 2001, 7:35 PM

Post #4 of 5

Shortcut

Re: Problem with Relations: Pulling Multiple Rows In reply to

Thank you very much for the reply, Alex.

Unfortunately, the codes did not work...I tried variations of the codes and they did not work...

I will play around with it some more and see what I can come up with...may be using ($sth->rows) will work...but I have tried that, may be I am missing something.

Any other suggestions would be greatly appreciated.

Thanks in advance.

Regards,

Eliot Lee

Mar 11, 2001, 1:18 PM

Stealth

Veteran (17240 posts)

Mar 11, 2001, 1:18 PM

Post #5 of 5

Shortcut

Re: Problem with Relations: Pulling Multiple Rows In reply to

Finally got it to work..although probably not the most efficient method...basically, I wrote separate queries to the tables using different WHERE clauses and then created separate calls to the same subroutine.

Here is what I used:

Code:

my $user_r = $USERDB->get_user_login_record ($username, 'HASH');
my $personid = $user_r->{PersonID};
my $firstname = $user_r->{FirstName};
my $lastname = $user_r->{LastName};
my $password = $user_r->{Password};
my $checkuser = $user_r->{Username};
my $checkmail = $user_r->{Email};
my $register = $user_r->{Registered};
my $adminquery = qq!
SELECT *
FROM tbl_AG_Person_Status
WHERE tbl_AG_Person_Status.StatusID = '1' AND tbl_AG_Person_Status.PersonID = $personid
!;

my $admin = $USERDB->prepare($adminquery);
$admin->execute();
my $adminhits = $admin->rows();
my ($admin_perm, $adminoutput);
while ($admin_perm = $admin->fetchrow_hashref) {
$showadmin .= &site_html_staff_link ($admin_perm, $dynamic);
}
my $modquery = qq!
SELECT *
FROM tbl_AG_Person_Status
WHERE tbl_AG_Person_Status.StatusID = '7' AND tbl_AG_Person_Status.PersonID = $personid
!;

Then I added the following in the codes:

Code:

if (!$to) {
print $in->header( -cookie => [$session_cookie, $user_cookie] );
my $title_linked = &build_linked_title ("User Login/Login Success");
&site_html_login_success ({Administrator => $showadmin, Moderator => $showmod, FirstName => $firstname, LastName => $lastname, title_linked => $title_linked, %$user_r}, $in, $dynamic);
}

Then in the login_success.html template, I used the following codes:

Code:

<%if Administrator%>
ADMIN LINKS
<%endif%>
<%if Moderator%>
MODERATOR LINKS
<%endif%>

Probably not the most efficient solution...if anyone has suggestions for improving the above codes, let me know, please.

Thanks in advance.

Regards,

Eliot Lee