Gossamer Forum
(Advanced Search)
for
Home : Products : DBMan : Customization :

Relations between databases

Jan 20, 1999, 8:54 AM
JPDeni
Veteran / Moderator (8669 posts)
Jan 20, 1999, 8:54 AM
Post #1 of 8
Views: 2395
Shortcut
Quote Reply
Relations between databases
Lauren asked about future releases supporting relations between databases. It's possible to get the current version of DBMan to work as if there were relations.

This probably would only be appropriate for the more experienced users to try!

I'm using two databases, one called "org" for an employer's basic information and "jobs" for job postings they add. I have a separate file that is called at the beginning of the html.pl files for each which defines (among other things) the basic format of all the pages, as well as the field names for each database and the number of the $auth_user_field for each. (It would have been easier to make the $auth_user_field the same for each one, but I didn't think of this until after I got it set up.) The databases are linked by a field "Org_ID" which is the key in the "org" database.

When a new job is entered, I wanted to pass the location information from "org" to "jobs," so at the beginning of html_add_form in the jobs html.pl file, I used the following:

Code:
# Pick up info from org db
$db_file_name = $db_script_path . "/org.db";
$auth_user_field = $org_auth_user_field;
@db_cols = @org_cols;
%rec2 = &get_record($db_userid);
$rec{'Organization'} = $rec2{'Organization'};
$rec{'City'} = $rec2{'City'};
$rec{'State'} = $rec2{'State'};
$rec{'Country'} = $rec2{'Country'};
$rec{'Contact_Type'} = $rec2{'Contact_Type'};

&html_record_form (%rec);

I used &get_record($db_userid); because "org" only has one record per user. When I want a list of the jobs listed for each org, I use

Code:
$db_file_name = $db_script_path . "/jobs.db";
$auth_user_field = $jobs_auth_user_field;
@db_cols = @jobs_cols;
$in{'Org_ID'} = $db_userid;
($status, @hits) = &query();

This may not be the best way to do it, but it works! Smile



------------------
JPD
Jan 22, 1999, 12:40 PM
Lauren Stegman
Novice (24 posts)
Jan 22, 1999, 12:40 PM
Post #2 of 8
Views: 2311
Shortcut
Quote Reply
Re: Relations between databases In reply to
Thanks JP.

I think I understand your subroutines for picking related data from the second database. However, I am not so clear about the html.pl files for the related databases. Do you have one html.pl file for both? Could you give me a better idea of what the file called at the beginning of html.pl contains? Does this file replace the .cfg files for each database or does it simple provide the .cfg infomation from the database containing related records to the database making the request? As you can tell I am a little lost.

If you have the time, I would love to learn more about your work-around.

Thanks again and in advance,
Lauren
Jan 22, 1999, 2:04 PM
JPDeni
Veteran / Moderator (8669 posts)
Jan 22, 1999, 2:04 PM
Post #3 of 8
Views: 2327
Shortcut
Quote Reply
Re: Relations between databases In reply to
Sure.

Each database has it's own .cfg file and html.pl file. The extra file I made is called format.pl (for want of a better name Smile ) and has formatting for the tops of the pages -- title bars and things like that. But that's not really important for what we're discussing here.

I added a line to the beginning of both html.pl files that says

Code:
require $db_script_path . "format.pl";

in order to get that script into memory. (The reason I put this into separate file is that I'm going to end up with more databases interacting and I didn't want to repeat the data too many times. If you only had two databases, you could just as easily put the "foreign" db's field names and auth_user_field in the .cfg file.)

For the relational part of the database, format.pl contains the field names for both databases in an array, like:

Code:
@org_cols = (Org_ID,Organization,Ctct_First_Name,
Ctct_Last_Name,Contact_Title,Contact_Email,
Contact_Address1,...);

$org_auth_user_field = 0;

@jobs_cols = (Job_ID,Date_Posted,Job_Title,Category,
Specialty,Job_Type,Shifts,Required_Edu,
Required_Exp,...);

$jobs_auth_user_field = 22;

The field names are in the same order in the arrays as they are in the respective .cfg files.

Basically what I did was look at the query and get_record subroutines and figure out what data they needed in order to do the search.

If you had a specific idea you wanted to implement, I'd be happy to help you either on the forum or through private mail.


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


[This message has been edited by JPDeni (edited January 22, 1999).]
Jan 25, 1999, 3:30 PM
Leo
New User (1 post)
Jan 25, 1999, 3:30 PM
Post #4 of 8
Views: 2305
Shortcut
Quote Reply
Re: Relations between databases In reply to
Hi JPDeni

I try to use your script but I can't make it work.

I have 2 data Base ( aweb and links)

I Create:
aweb.cgi, aweb.cfg, aweb.html.pl, links.html.pl and links.cfg

both db works Fine

I want to read all data and stored in @links.

I put in the begin of the aweb.cgi the following script
Code:
# Load the form information and set the config file and userid.
local(%in) = &parse_form;
$in{'db'} ? ($db_setup = $in{'db'}) : ($db_setup = 'aweb');
$in{'uid'} ? ($db_uid = $in{'uid'}): ($db_uid = '');

if ($in{'db'} !~ 'links') {&loadlinks;}

and the sub loadlinks is:

Code:
sub loadlinks {
# --------------------------------------------------------

#-----Actual DB Data---------
@web_cols = @db_cols;
$web_auth_user_field = $auth_user_field;
$web_db_file_name = $db_file_name;
$webID = $in{'ID'};
@web_db = $in{db};

#-----Links Data -----------
@links_cols = ('ID','Link', 'Userid');
$links_auth_user_field = 2;
$links_db_file_name = $db_script_path . "/data/links.db";

#-----New Value -----------
$db_file_name = $links_db_file_name;
$auth_user_field = $links_auth_user_field;
@db_cols = @links_cols;
$in{'ID'} = '*';

my ($status, @hits) = &query("view");
if ($status eq "ok") {
my ($numhits) = ($#hits+1) / ($#db_cols+1);
for (0 .. $numhits - 1) {
@links=&array_to_hash($_, @hits);
}
}
else {
&cgierr ("Error Loading requiered database.\n Error code:$status ");
}
#----Restore Value--------
@db_cols = @web_cols;
$auth_user_field = $web_auth_user_field;
$db_file_name = $db_db_file_name;
$in{'ID'} = $webID;
$in{db} = @web_db
}

When I try to run this script I received a nasty "Internal Server Error"
I try a lot of changes and I think the problem is with the "ID" in the @db_cols, I erased the ID in the @db_cols and I received a error "no search terms specified"

I'm not and expert in perl (only 4 weeks) and I'm not sure about the meaning of every part of the DBMAN script


Please tell me how can I fix the script.

thanks, I really appreciate any kind of help

Leo
Jan 25, 1999, 3:51 PM
JPDeni
Veteran / Moderator (8669 posts)
Jan 25, 1999, 3:51 PM
Post #5 of 8
Views: 2317
Shortcut
Quote Reply
Re: Relations between databases In reply to
When I posted the first message in this thread, I wondered if I should. :-)

What are you trying to do with your two databases? How are the linked?

We can take this to private mail if you want.



------------------
JPD
Oct 11, 1999, 7:59 AM
spike
Novice (41 posts)
Oct 11, 1999, 7:59 AM
Post #6 of 8
Views: 2324
Shortcut
Quote Reply
Re: Relations between databases In reply to
Hi. I'm trying to do something that seems similar to the above. I have one database with a field called "Dealer". It will have a number entered into it that corresponds to the "ID" number of second database.

What I'd like to do is when the first database is accessed, the "Dealer" field is used to pull some fields from the second database into the display for that particular record. Name, address, city, phone, etc.

There will be multiple records from each dealer and it seems a waste of space to enter all of the "Dealer" info for each record.

So, I guess what I need is a routine that can be added to the sub html_record that does the call for the additional information so it can be displayed.

Hope this makes sense.

Thanks,
spike
Jan 30, 2000, 9:36 PM
Luis
Novice (26 posts)
Jan 30, 2000, 9:36 PM
Post #7 of 8
Views: 2321
Shortcut
Quote Reply
Re: Relations between databases In reply to
I am trying to do a very similar thing. I have db1 with id_no, description; and db2 with buncha fields plus id_no...this is for display only, and would like to have the description come up (from db1) when db2 is displayed.

Is there a mod for this?
Jan 31, 2000, 10:47 AM
Eliot
Deleted
Jan 31, 2000, 10:47 AM
Post #8 of 8
Views: 2315
Shortcut
Quote Reply
Re: Relations between databases In reply to
Go here:

www.jpdeni.com/dbman/Mods/relational.txt

By the way, this mod has been linked MANY times in this forum...keep searching if the first time you do not succeed!

Regards,

------------------
Eliot Lee
Anthro TECH,L.L.C
www.anthrotech.com
* Be sure to visit the Resource Center for FAQ's, Modifications and Extra Goodies!!
* Search Forums!
* Say NO to Duplicate Threads. :)
----------------------