Gossamer Forum
Home : Products : Gossamer Links : Version 1.x :

Problem with Relations: Pulling Multiple Rows

Quote Reply
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
Subject Author Views Date
Thread Problem with Relations: Pulling Multiple Rows Stealth 3298 Mar 2, 2001, 7:49 PM
Post Re: Problem with Relations: Pulling Multiple Rows
Stealth 3165 Mar 6, 2001, 1:59 PM
Thread Re: Problem with Relations: Pulling Multiple Rows
Alex 3169 Mar 6, 2001, 5:28 PM
Thread Re: Problem with Relations: Pulling Multiple Rows
Stealth 3169 Mar 7, 2001, 7:35 PM
Post Re: Problem with Relations: Pulling Multiple Rows
Stealth 3151 Mar 11, 2001, 1:18 PM