Gossamer Forum
Home : Products : DBMan : Customization :

Relational pains

(Page 1 of 2)
> >
Quote Reply
Relational pains
Ok, I've read many threads and I'm still stumped. I basically want to display a "many" record from within the "one". In my case a user should be able to view details of a related agent. Both cfg's have UserID in the same position and I've followed the mod text, the user_html.pl code is as follows:-
Code:
&switch_to_agency;

$in{'UserID'} = $rec{'UserID'};
$in{'sb'} = 6;
$in{'so'} = 'descend';
$in{'mh'} = 100;

my ($status2,@hits2) = &query("view");

if ($status2 eq "ok") {
my ($numhits2) = ($#hits2+1) / ($#db_cols+1);
print "<table>";

for (0 .. $numhits2 - 1) {
%rec2 = &array_to_hash($_, @hits2);
print qq|
<tr><td>
<a href="$db_script_url?db=agency&uid=$db_uid&AgentID=$rec2{'AgentID'}&view_records=1">$rec2{'Agent name'}</a><BR>|;
</td></tr>
}
print "</table>";
}
&switch_to_user;
}
I get the dreaded Internal Server Error using the above, if it's commented out then the database works ok.
Any ideas? I don't need to display the one(users) from the many(agency). So agency_html.pl is unchanged.

Code from db.cgi sub get_record :-
Code:
sub switch_to_agency {
#-----------------------------------------------------
@db_cols = qw(UserID AgentID etc. fields');
$db_file_name = $db_script_path . "/agency.db";
}

sub switch_to_user {
#-----------------------------------------------------
@db_cols = qw(UserID 'First name' etc. all fields);
$db_file_name = $db_script_path . "/users.db";
$db_key_pos = 0;
}
Quote Reply
Re: Relational pains In reply to
Sorry JPD, it's getting late in the UK Smile
I've added the code but nothing is returned on the form (as user or admin). I turned bedug on and this came back:-
Code:
Form Variables
-------------------------------------------
UserID : fwest
UserID-lt : B>fwest
db : users
mh : 100
uid : fwest.95617493495563
view_records : 1
Here is the snippet again with your bit added:-
Code:
&switch_to_agency;

$in{'UserID'} = $rec{'UserID'};
$in{'mh'} = 100;

my ($status2,@hits2) = &query("view");
print "$status";

if ($status2 eq "ok") {
my ($numhits2) = ($#hits2+1) / ($#db_cols+1);
print "<table>";

for (0 .. $numhits2 - 1) {
%rec2 = &array_to_hash($_, @hits2);
print qq|<tr><td><a href="$db_script_url?db=agency&uid=$db_uid&AgentID=$rec2{'AgentID'}&view_records=1">$rec2{'Agent name'}</a><BR></td></tr>|;
}
print "</table>";
}

&switch_to_user;
}
Any ideas? Thanks.
Quote Reply
Re: Relational pains In reply to
We can continue this tomorrow, if it's getting late for you. (I understand. Smile )

You don't need to turn on the debug feature. I was just trying to find out what, if anything was being returned from your search. I guess nothing was.

But my foggy brain didn't work too well. The debug line you should add is

Code:
print "$status2";

Sorry.


------------------
JPD






Quote Reply
Re: Relational pains In reply to
I have to plough on...deadlines Frown
It returned "no matching records".
Obviously Smile

Here is an extract from the agency.db (many) file:-
Code:
fwest|76|Nationwide Technology Recruitment PLC|St marys|23|123|123|fred@test.com|sdgfsdf
and here is the user (one) extract:-
Code:
fwest|Frank|West|Dr etc etc
Quote Reply
Re: Relational pains In reply to
I see a few syntax errors in your code.

Your display of the "many" side has a couple of errors. It should be

Code:
print qq|
<tr><td>
<a href="$db_script_url?db=agency&uid=$db_uid&AgentID=$rec2{'AgentID'}&view_records=1">
$rec2{'Agent name'}</a><BR>
</td></tr>|;

That takes care of part of it.

The other part I'm not so sure of. In the snippet of code you posted, there is an extra } at the end. This may be necessary for some routine you have earlier on, though, so I can't be sure.

Once you make the change I noted above, if you still have errors, try taking out the } at the end of this code.


------------------
JPD






Quote Reply
Re: Relational pains In reply to
Thanks JPD Smile
I'm now getting missing right bracket messages Frown
Hey ho - I'll keep looking
Quote Reply
Re: Relational pains In reply to
Ok, I can now access my users db again Smile
However, there is no related record from agency. I expected the link to the full "item" record print to display this. Both cfg's have UserID in the same position and both test records are created by the same user. Here is the code (lots of it) from user_html.pl again with the extra bits...
Code:
sub html_record {
# --------------------------------------------------------
# How a record will be displayed. This is used primarily in
# returning search results and how it is formatted. The record to
# be displayed will be in the %rec hash.

my (%rec) = @_; # Load any defaults to put in the VALUE field.
($db_auto_generate and print &build_html_record(%rec) and return);

my $font_color = 'Font face="Verdana, Arial, Helvetica" Size=2 Color=#003399';
my $font = 'Font face="Verdana, Arial, Helvetica" Size=2';

$url{'Email address'} = $rec{'Email address'};
$url{'Email address'} =~ s/<\/?B>//g;
$rec{'Home address'} =~ s/\n/<BR>/g;
$rec{'Bank address'} =~ s/\n/<BR>/g;

[code deleted to save bandwidth]

if ($rec{'Date PasswordPack sent to user'}) {
print qq| |;
if ($per_admin) {
print qq|
<TR><TD ALIGN="Right" VALIGN="TOP" WIDTH="20%"><$font_color>Date PasswordPack sent to user:</FONT></TD>
<TD WIDTH="80%"> <$font>$rec{'Date PasswordPack sent to user'}</Font></TD></TR>
|;
}
print qq|
</TABLE>
|;

&switch_to_agency;

$in{'UserID'} = $rec{'UserID'};
$in{'mh'} = 100;

my ($status2,@hits2) = &query("view");

if ($status2 eq "ok") {
my ($numhits2) = ($#hits2+1) / ($#db_cols+1);
print "<table>";

for (0 .. $numhits2 - 1) {
%rec2 = &array_to_hash($_, @hits2);
print qq|<tr><td><a href="$db_script_url?db=agency&uid=$db_uid&AgentID=$rec2{'AgentID'}&view_records=1">$rec2{'Agent name'}</a><BR></td></tr>|;
}
print "</table>";
}

&switch_to_user;
}
}
It may be my right bracket problem? Thanks again,
Rob

[This message has been edited by JPDeni (edited April 19, 2000).]
Quote Reply
Re: Relational pains In reply to
I hope you don't mind my editing your previous post. It did make the thread awfully long. Smile

I found your error. It was a bracket problem, but not where you thought it was. After what I edited out, it should be:

Code:
if ($rec{'Date PasswordPack sent to user'}) {
if ($per_admin) {
print qq|
<TR><TD ALIGN="Right" VALIGN="TOP" WIDTH="20%"><$font_color>Date PasswordPack sent to user:</FONT></TD>
<TD WIDTH="80%"> <$font>$rec{'Date PasswordPack sent to user'}</Font></TD></TR>
|;
}
}
print qq| </TABLE>|;

&switch_to_agency;
$in{'UserID'} = $rec{'UserID'};
$in{'mh'} = 100;
my ($status2,@hits2) = &query("view");
if ($status2 eq "ok") {
my ($numhits2) = ($#hits2+1) / ($#db_cols+1);
print "<table>";
for (0 .. $numhits2 - 1) {
%rec2 = &array_to_hash($_, @hits2);
print qq|<tr><td><a href="$db_script_url?db=agency&uid=$db_uid&AgentID=$rec2{'AgentID'}&view_records=1">$rec2{'Agent name'}</a><BR></td></tr>|;
}
print "</table>";
}
&switch_to_user;
}


------------------
JPD






Quote Reply
Re: Relational pains In reply to
Feel free! I now have a working users and a working agency. There is still however no display in Users of the Agency detail? Isn't that what the db_script_url line in the switch_to_agency is supposed to do or am I missing the point? Smile
Thanks in advance,
Rob
Quote Reply
Re: Relational pains In reply to
Well, first we had to find the syntax error. Nothing will work correctly until that's fixed.

Now I need you to add a debugging line.

After

my ($status2,@hits2) = &query("view");

add

Code:
print "$status";

Let me know what happens.


------------------
JPD






Quote Reply
Re: Relational pains In reply to
Try adding

Code:
undef %in;

before

Code:
$in{'UserID'} = $rec{'UserID'};

I think I need to put that in the mod.


------------------
JPD






Quote Reply
Re: Relational pains In reply to
I was just about to doze off when I realized what your problem is.

Before

Code:
$in{'UserID'} = $rec{'UserID'};

add

Code:
$rec{'UserID'} =~ s/<?.B>//g;

When you posted the debug code, I couldn't figure out why you would have

UserID-lt: B>fwest

It didn't make any sense to have a "less than" search for a userid.

Then it hit me. There are bold tags from your search. You need to get rid of them to do your search.


------------------
JPD






Quote Reply
Re: Relational pains In reply to
Thanks JPD!
The user table (one) still does not display any agency details, the status code returns an ok, so it must be finding a matching record from agency?.
So...the code from the one side is thus now:-
Code:
&switch_to_agency;

$rec{'UserID'} =~ s/<?.B>//g;

undef %in;
$in{'UserID'} = $rec{'UserID'};
$in{'mh'} = 100;

my ($status2,@hits2) = &query("view");
# print "$status2";

if ($status2 eq "ok") {
my ($numhits2) = ($#hits2+1) / ($#db_cols+1);
print "<table>";

for (0 .. $numhits2 - 1) {
%rec2 = &array_to_hash($_, @hits2);
print qq|<tr><td><a href="$db_script_url?db=agency&uid=$db_uid&AgentID=$rec2{'AgentID'}&view_records=1">$rec2{'Agent name'}</a><BR></td></tr>|;
}
print "</table>";
}

&switch_to_user;
I've taken the undef %in; in and out with no difference.
Here is the many code:-
Code:
my (%rec) = @_; # Load any defaults to put in the VALUE field.
# Switch to user db
&switch_to_user;
# Get the associated record
%rec2 = &get_record($rec{'UserID'});
# Switch back to original agency db
&switch_to_agency;
I've tried the rec2 definition in the agency_html but that makes no difference.
Thanks again,
Rob
Quote Reply
Re: Relational pains In reply to
Well, at least it's finding the record now!

Let's work on the "one" side first. I get confused enough as it is. Smile

I know you're on a deadline, but I don't know how to do this without debugging step by step.

The next thing to do is to find out what's in the @hits2 array.

After

if ($status2 eq "ok") {

add

Code:
foreach $field (@hits2) {
print "$field<BR>";
}

This should print out the field values of the records that are returned, one on each line.


------------------
JPD






Quote Reply
Re: Relational pains In reply to
Thanks JPD Smile
The records returned (correctly)are thus:
Code:
fwest
76
Nationwide Technology Recruitment PLC
St marys
23
12345
123
fred@test.com
sdgfsdf
fwest
77
Test Agent PLC
TEst
wefr
sdf
wer
w34@test.com
werw
So that's better, looks like the display/print statement perhaps? We're getting there, or rather you are Smile
Rob
Quote Reply
Re: Relational pains In reply to
That tells me a lot!

You can take out the lines you added last.

The only thing that might be a problem still is the $numhits2 variable. After

my ($numhits2) = ($#hits2+1) / ($#db_cols+1);

add

print "$numhits2<BR>";

You should get, for the record you've been working on, 2.

What exactly is showing up from the other database? Anything?


------------------
JPD






Quote Reply
Re: Relational pains In reply to
I got 1.05882352941176 !!
Code is currently:-
Code:
&switch_to_agency;

$rec{'UserID'} =~ s/<?.B>//g;

undef %in;
$in{'UserID'} = $rec{'UserID'};
$in{'mh'} = 100;

my ($status2,@hits2) = &query("view");
# print "$status2";

if ($status2 eq "ok") {
# foreach $field (@hits2) { print "$field<BR>";}
my ($numhits2) = ($#hits2+1) / ($#db_cols+1);
print "$numhits2<BR>";
print "<table>";

for (0 .. $numhits2 - 1) {
%rec2 = &array_to_hash($_, @hits2);
print qq|<tr><td><a href="$db_script_url?db=agency&uid=$db_uid&AgentID=$rec2{'AgentID'}&view_records=1">$rec2{'Agent name'}</a><BR></td></tr>|;
}
print "</table>";
}

&switch_to_user;
Without the debug statements there is nothing displayed. I should add (if it helps) that this form has quite a mumer of admin only fields.
Quote Reply
Re: Relational pains In reply to
1.05882352941176?!?!?!?!

Wow! Smile

Are you certain that in your sub switch_to_agency that you have all the fields correctly defined? It seems like you may have more field names than there are fields in the database.


------------------
JPD






Quote Reply
Re: Relational pains In reply to
Hold the line caller I'll triple check Smile
Quote Reply
Re: Relational pains In reply to
/scratch head mode
Well I checked db.cgi and copy and pasted the field names from the agency cfg file and it still returns 1.05882352941176! The last field name has a space after the last char and before the ' in the cfg descriptor. All fields are described as 'fieldname' and have a space between them in the switch sub. Weied huh!????
Frown
Quote Reply
Re: Relational pains In reply to
The only thing I can think to do is to take a look at your %db_def from the agency .cfg file, your sub switch_to_agency, and a record from the agency .db file.

At least we know where the problem is. Now to figure out how to fix it. Smile


------------------
JPD






Quote Reply
Re: Relational pains In reply to
The patience of a saint....

agency.cfg:
Code:
# Database Definition
# --------------------------------------------------------
# Definition of your database. Format is
# field_name => ['position', 'field_type', 'form-length', 'maxlength', 'not_null', 'default', 'valid_expr']

%db_def = (
'UserID' => [ 0, 'alpha', 15, 15, 1, '', ''],
'AgentID' => [ 1, 'alpha', 15, 15, 1, '', ''],
'Agent name' => [ 2, 'alpha', 20, 255, 1, '', ''],
'Agent address' => [ 3, 'alpha', '40x5', 1000, 0, '', ''],
'Agent postcode' => [ 4, 'alpha', 20, 255, 0, '', ''],
'Agent telephone number'=> [ 5, 'numer', 20, 255, 0, '', ''],
'Agent fax number' => [ 6, 'numer', 20, 255, 0, '', ''],
'Agent email address' => [ 7, 'alpha', 20, 255, 0, '', '.+@.+..+'],
'Agent Invoice Address and Postcode'=> [ 8, 'alpha', '40x5', 1000, 0, '', '']
);
and sub switch:
Code:
sub switch_to_agency {
#-----------------------------------------------------
@db_cols = qw('UserID' 'AgentID' 'Agent name' 'Agent address' 'Agent postcode' 'Agent telephone number' 'Agent fax number' 'Agent email address' 'Agent Invoice Address and Postcode');
$db_file_name = $db_script_path . "/agency.db";
}
and here is the data:-
Code:
fwest|76|Nationwide Technology Recruitment PLC|St marys|23|12345|123|fred@test.com|Test 1234
fwest|77|Test Agent PLC|TEst|wefr|sdf|wer|w34@test.com|werw
admin|78|Fred|Test|123|123|123|fred@test.com|
Does the location of the actual sub switch in db.cgi matter? I've plonked mine in sub get record after that section title but before the get record code. I'm sure I've tried it elsewhere!! Smile
Quote Reply
Re: Relational pains In reply to
Yes, the location of the subroutine matters! It cannot be within another subroutine!!!!! Move it to any other place in db.cgi that is not within a subroutine.

That could definitely be the cause of your problems. Everything else looks great.

To test whether this is the cause of your problems or not, after

&switch_to_agency

add

Code:
foreach $col (@db_cols) {
print "$col<BR>";
}

If you get the "agency" columns, then your placement of the subroutine is fine. If you get the other columns, you need to move the subroutine.


------------------
JPD






Quote Reply
Re: Relational pains In reply to
Forgive me, I had two sub agency switches in the db.cgi, I've made sure I only have one which is at the end of db.cgi!! Sorry.

However....yes the agency columns are printed but I added back your previous debug statements and it now returns:-no search terms specified
Frown
Sorry JPD, did it just get worse again?
Quote Reply
Re: Relational pains In reply to
Any clues anyone?
Rob
> >