Gossamer Forum: Products: DBMan: Customization: JPD Relational DB

May 24, 2000, 10:21 AM

Norvin

Novice (24 posts)

May 24, 2000, 10:21 AM

Post #1 of 12

Shortcut

JPD Relational DB

Hi,

Thanks for the reply to my post 21/5 and the support regarding relational DB’s

I have followed your details as stated and placed the following code to the top of item_html.pl
sub html_record_form
my (%rec) = @_;

if ($in{'add_form'}) {
&switch_to_user;
$rec2=&get_record($db_userid);
&switch_to_item;
$rec{'County'} = $rec2{'County'};
}
($db_auto_generate and print &build_html_record_form(%rec) and return);

I did have to add a ) in the first line to get it to run however I am still unable to get the $rec2{'County'} to display in html_record_form

I have rec2 working fine in the html_record display but alas not displaying in the html_record_form

I am using
<input type="TEXT" name="County" value="$rec2{'County'}" size="20" maxlength="6"> in the html_record_form but even writing it as $rec2{'County'} still shows nothing.
Cheers

Regards
Norvin

All the best

May 24, 2000, 8:50 PM

LoisC

Veteran / Moderator (3034 posts)

May 24, 2000, 8:50 PM

Post #2 of 12

Shortcut

Re: JPD Relational DB In reply to

In the other thread it said to add another field for county in your item database. With the code that JPDeni provided I would think that the field within your item database would then read:

<input type="TEXT" name="County" value="$rec{'County'}" size="20" maxlength="6">

Have you tried it like this?

P.S. Just a tip, if you add a new comment to your original thread it will bring that message back to the top. That way all of the related information is kept within one thread Smile

Let us know if by changing to the above works.

Hope this helps

May 24, 2000, 11:11 PM

JPDeni

Veteran / Moderator (8669 posts)

May 24, 2000, 11:11 PM

Post #3 of 12

Shortcut

Re: JPD Relational DB In reply to

In order to see what's wrong, you'll need to do some debugging.

After

$rec2=&get_record($db_userid);

add

Code:

foreach $col (@db_cols) {
print "$col -- $rec2{$col}<BR>";
}

This should give you a list of the fields in your user database, along with the values that match the current user. See what happens.

JPD

Jun 5, 2000, 11:50 AM

Norv

Novice (7 posts)

Jun 5, 2000, 11:50 AM

Post #4 of 12

Shortcut

Re: JPD Relational DB In reply to

Hi,

Thanks for the help and support, I have inserted as stated below and have managed to get the county field to show and added to the Items DB.

However this only works on the ADD ERROR page, if I add a record, the county or records from rec2 do not show, but submiting the record with an error and all the details show as needed from both user / items and the records are updated correctly.

But alas not the first time around, the details are not showing in the Add Records form but are in the Add Error Page.

Any Ideas on this I seem to have almost cracked it.

Thanks again for the time

Norvin

Jun 5, 2000, 3:42 PM

JPDeni

Veteran / Moderator (8669 posts)

Jun 5, 2000, 3:42 PM

Post #5 of 12

Shortcut

Re: JPD Relational DB In reply to

At the beginning of sub html_add_form, add

$in{'add_form'}=1;

JPD

Jun 5, 2000, 5:33 PM

Norvin

Novice (24 posts)

Jun 5, 2000, 5:33 PM

Post #6 of 12

Shortcut

Re: JPD Relational DB In reply to

Hi,

I have used the insert at the top of the ADD page, but again the records will not show.
The only records that show are after hitting the add button are the ID and date fields.
leave a field empty to show the add error page and all is fine all are showing as they should, I then hit the add button again and the record is add.

Any idea around this strange shows on the add error but not on the add page

this is the top of add record form
my (%rec) = @_; # Load any defaults to put in the VALUE field.

&switch_to_user;

%rec2 = &get_record($rec{'Userid'});
foreach $col (@db_cols) {
print "$col -- $rec2{$col}<BR>";
}
($db_auto_generate and print &build_html_record_form(%rec2) and return);
&switch_to_item;
$rec{'County'} = $rec2{'County'};
($db_auto_generate and print &build_html_record_form(%rec) and return);
my $font = 'Font face="Verd

and the top of the ADD Page

sub html_add_form {
# --------------------------------------------------------
$in{'add_form'}=1;
&html_print_headers;
print qq|
<html>
<head>

cheers again
Norvin

Jun 5, 2000, 11:54 PM

JPDeni

Veteran / Moderator (8669 posts)

Jun 5, 2000, 11:54 PM

Post #7 of 12

Shortcut

Re: JPD Relational DB In reply to

I'm confused. Smile

Do you have

Code:

if ($in{'add_form'}) {
&switch_to_user;
$rec2=&get_record($db_userid);
&switch_to_item;
$rec{'County'} = $rec2{'County'};
}

Code:

&switch_to_user;

%rec2 = &get_record($rec{'Userid'});
foreach $col (@db_cols) {
print "$col -- $rec2{$col}<BR>";
}
($db_auto_generate and print &build_html_record_form(%rec2) and return);
&switch_to_item;
$rec{'County'} = $rec2{'County'};

It makes a difference.

JPD

Jun 6, 2000, 1:12 AM

Norvin

Novice (24 posts)

Jun 6, 2000, 1:12 AM

Post #8 of 12

Shortcut

Re: JPD Relational DB In reply to

Hi,

I have been using your first example

my (%rec) = @_;

if ($in{'add_form'}) {
&switch_to_user;
$rec2=&get_record($db_userid);
foreach $col (@db_cols) {
print "$col -- $rec2{$col}<BR>";
}

&switch_to_item;
$rec{'County'} = $rec2{'County'};
}
($db_auto_generate and print &build_html_record_form(%rec) and return);

However I changed it to the latter in your example, and removed the
if ($in{'add_form'})
and managed to get it to work as said and update the items db all fine BUT only via the add error page .

I have used both and many other way over the last six months to get this to operate.

I do have it working and updating the items db all fine but again only after adding via the error page.

Thanks again for your time JPD
much appreciated

Regards
Norvin

Jun 6, 2000, 8:30 PM

Norvin

Novice (24 posts)

Jun 6, 2000, 8:30 PM

Post #9 of 12

Shortcut

Re: JPD Relational DB In reply to

Hi,

It seems that when the sub html_record_form is being called at 93 in db.cgi it is not showing the data in rec2(user.db) because the database is not being called.

The sub html_record_form is being called before the DB is accessed and hence no data to display.

So at wits end I have add a new sub that calls the db.cgi before the sub html_record_form, and I have it showing in the sub html_record_form as needed but alas, the items default data (item no and date) is not hmmmm.

Now the quest is to get both to show at the same time.

In the sub html_add_form I need an expression to show both

(get_defaults) and (%in) at the same time.

the html.pl as standard is

|; &html_record_form (get_defaults); print qq|

How would I change this to show

|; &html_record_form (%in); print qq|

as well.

ie
|; &html_record_form (get_defaults),(%in); print qq|

or whatever combination to get it to run

Cheers

Norvin.

Jun 6, 2000, 10:04 PM

JPDeni

Veteran / Moderator (8669 posts)

Jun 6, 2000, 10:04 PM

Post #10 of 12

Shortcut

Re: JPD Relational DB In reply to

Let's start over -- and use "real" language for what's going on rather than Perl.

We're at sub html_add_form. The script prints out the top part of the html page and then switches to sub html_record_form, sending any default values with it. (&html_record_form (&get_defaults);)

The first thing in sub html_record_form is to assign the defaults (if any) to the %rec hash. (my (%rec) = @_;)

Now we want to get a field from another database.

First we fool DBMan into thinking we're logged onto that database. (&switch_to_user;) This will tell DBMan what the column names are, which .db file to use and which number field the key value is in.

You didn't post your sub switch_to_user, but it should look something like

Code:

sub switch_to_user {
#-----------------------------------------------------
$cols = 'UserID,UserFirstName,UserLastName,UserEmail,UserCity,UserState,UserCounty';
@db_cols = split /,/,$cols;
$db_file_name = $db_script_path . "/user.db";
$db_key_pos = 0;
}

We know the key field for the other database ($db_userid), so we can just get that one record. (%rec2 = &get_record($db_userid);)

Now we can fool DBMan again, this time going back to our original database. (&switch_to_item;)

Put the value of the field we're interested in into the %rec hash for the current database. ($rec{'County'} = $rec2{'County'};)

So, putting it all together, we have

Code:

my (%rec) = @_;
&switch_to_user;
%rec2 = &get_record($db_userid);
&switch_to_item;
$rec{'County'} = $rec2{'County'};

If the County field is not entered, there is a problem somewhere in the above code. It will take a little debugging to find out where the problem is.

First --

Code:

my (%rec) = @_;
&switch_to_user;
foreach $col (@db_cols) {
print "$col<BR>;"
}
%rec2 = &get_record($db_userid);
&switch_to_item;
$rec{'County'} = $rec2{'County'};

This should give you a list of the columns in the user database. If it doesn't, there is a problem with your sub switch_to_user.

If it does, go to the next debugging step:

Code:

my (%rec) = @_;
&switch_to_user;
%rec2 = &get_record($db_userid);
foreach $col (@db_cols) {
print "$col -- $rec2{$col}<BR>;"
}
&switch_to_item;
$rec{'County'} = $rec2{'County'};

This should give you a list of the fields in the user database, along with the values of the record associated with the $db_userid.

If it doesn't, either your $db_key_pos setting is incorrect in your sub switch_to_users, or you didn't delete the line

($restricted = 1) if ($auth_modify_own and !$per_admin);

from sub get_record.

(My guess is that it would be the former problem, though.)

Take out the things you added and try this. Let me know what happens.

JPD

Jun 7, 2000, 3:00 PM

Norvin

Novice (24 posts)

Jun 7, 2000, 3:00 PM

Post #11 of 12

Shortcut

Re: JPD Relational DB In reply to

First off I would like to say thanks for excellent support both in patience , speed of reply and process of diagnostics.

I have it working like a well oiled machine, doing all that it should at the right times.

Thanks to your explanation cheers.

I hate to say the error but at some time I had inadvertently used the wrong db.cgi and the line
($restricted = 1) if ($auth_modify_own and !$per_admin);
was still in place

Thanks again for the time and all the work on the Relational db mod.

All the best
Regards

Norvin Blush

Jun 8, 2000, 11:52 AM

JPDeni

Veteran / Moderator (8669 posts)

Jun 8, 2000, 11:52 AM

Post #12 of 12

Shortcut

Re: JPD Relational DB In reply to

Well, that's what I would have done if it was my database and it wasn't doing what I wanted. Test out one line at a time.

I'm glad we were able to figure out what the problem was.

JPD