Carol,

Okay...I got a real stinker for ya or anyone else that wants to take a stab at it.



I have two databases, which differ in structure, yet have similar fields. What I would like to do is link fields between databases. I have separate files for each datbase (including db.cgi, html.pl and default.cfg). I know that the easiest thing to do would be to have one db.cgi file, but since our database programs are live, and our test environment is being re-configured, I really don't have the luxury of making any changes to the file structure of the databases at this time.

The two fields that I would like to try to link between the databases are:

SUBJ = Course Subjects
TITLE = Title of Courses

Note: Be aware that these fields have multiple values because instructors teach multiple classes each semester. Also, remember that I have renamed my db.cgi to index.cgi.

I have done the following:

1) Added the following codes to the db.cgi file of the Employee Directory Database:


2) Added the following variable to the default.cfg in the Employee Directory:


3) Added some regular expression statements to the html_record_long sub-routine in the html.pl file:


I have other regular expression statements that puts ,<BR> codes between values of the two fields:


I added the top regular expression statements because I was getting a Bad Request Error due to the <BR> codes that were being inserted into the query string.

4) Added a link to the SUBJ row in the html_record_long sub-routine:


So, what I need to do is find a way to link the multiple values in at least the SUBJ field between the two databases.

Any ideas??

TIA.

Regards,

------------------
Eliot Lee
Founder and Editor
Anthro TECH, L.L.C
http://www.anthrotech.com/
info@anthrotech.com
==========================
Coconino Community College
http://www.coco.cc.az.us/
Web Technology
Coordinator
elee@coco.cc.az.us

[This message has been edited by Eliot (edited August 19, 1999).]

Thanks sun for the tip. I will try that.

Carol,


Yes.


No.


No. In the "instructor" db (which BTW, also includes staff records), there is ONE field for SUBJ and ONE field for TITLE. In the class schedules, there is ONE field for SUBJ and ONE field for TITLE. However, many records in the "instructor" db have multiple values:

(e.g., CIS 102~~CIS 200~~CIS 232)

In the class schedules, there is only ONE SUBJ per record. In the class schedules, there is a field for Last_Name, which sort of corresponds to the Last_Name field in the Employee Directory. (The problem is that the names of instructor field are extracted in a different manner in the Class Schedules database than the names of instructor field in the Employee Directory. The instructor names in the Class Schedules are truncated and capitilized. The instructor names in the Employee Directory are mixed case and also their full name.) However, the values for the course subject (SUBJ) and title (TITLE) are exactly the same between databases.

For instance, in the Class Schedules, for the SUBJ field, there is a CIS class. In the Employee Directory, there is a CIS class.

If you'd like to see an example of what I am talking about, go to:

Class Schedules:

www.coco.cc.az.us/cgi-bin/schedules/index.cgi?&uid=default

Select "Computer Information Systems" in the "Subject and Course Number" row.

Then you will see in the table a column called SUBJ.

Employee Directory

www.coco.cc.az.us/cgi-bin/directory/index.cgi?&uid=default

Select "Computer Information Systems (CIS)" in the "Academic Area" row. Then click on Jeff Rhode. In the Courses row, you will see a list of courses (SUBJ) that Jeff teaches.

What I am trying to do is, for example, link the CIS in the Employee Directory to the CIS in the Class Schedules.

I hope this explains it better.

Thanks.


------------------
Eliot Lee
Founder and Editor
Anthro TECH, L.L.C
http://www.anthrotech.com/
info@anthrotech.com
==========================
Coconino Community College
http://www.coco.cc.az.us/
Web Technology
Coordinator
elee@coco.cc.az.us

[This message has been edited by Eliot (edited August 19, 1999).]

[This message has been edited by Eliot (edited August 19, 1999).]

I see. I didn't understand how you had multiple courses for the instructor. Now I got it.

I'm still not really certain about what you're trying to accomplish, though. Do you want a link that will list the classes that the instructor teaches, if any?

If so, is there anything that is in common between the two databases we can use as a search field? You can't search for the class number only, because there are other instructors who teach the same class. You might sorta be able to search for the last name, but that's pretty iffy, given the way you have things set up. Searching for the Last Name in the schedules db won't necessarily give you the search results you want.

What would be really nice is to have an ID for the instructors. Possibly you could have the "Instructor" field from the schedule database somewhere in the staff database. Or the key field from the staff database somewhere in the schedule database.

Alternatively, you could have the CRN -- the unique class identifier in the schedule database -- somewhere in the staff database, although that would be less efficient.

If you want, I can probably come up with code for you to create a link that would use the SUBJ field to do a search, but I don't think it will give you what you want.


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

That's really interesting, but I'm not sure what you're trying to accomplish.


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

JPDeni,

Okay...In our Employee Directory, we have fields for our instructors: Course Subject (SUBJ) and TITLE (Title).
In our Class Schedules, we have fields for classes: Course Subject (SUBJ) and TITLE (Title). (Same fields)

I want to link these fields in the Employee Directory (and possible do the same thing with the Class Schedule with other similar fields, like Instructors). So, if a course is listed in the Employee Directory that is also found in the Class Schedules, then a link will show, which will take you to the records in the Class Schedules.

Does this make better sense?

Thanks for your response.

Regards,

------------------
Eliot Lee
Founder and Editor
Anthro TECH, L.L.C
http://www.anthrotech.com/
info@anthrotech.com
==========================
Coconino Community College
http://www.coco.cc.az.us/
Web Technology
Coordinator
elee@coco.cc.az.us

Hello Eliot:

What you can do is to have the $in{'SUBJ'} and then after searching the default database, search the second database based on $in{'SUBJ'}. The data you get from the first database can then be joined with the data obtained from the second database.

Sound simple in theory but I have never really implemented it myself so I am not sure of any pitfalls there may be.

Hope that helps
Sun

------------------
-------------------- Gossamer Threads Inc. ----------------------
Sun Djaja Email: sun@gossamer-threads.com
Internet Consultant
http://www.gossamer-threads.com

So you want to have a link that will show the courses the instructor teaches. Right?

Does each of the courses have a field with the instructor's id in it? That would be the easiest way to do this.

In your instructor db, do you have several SUBJ and TITLE fields, for the ones who teach multiple classes? Is this what the problem is?


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

Ah, Carol! You do make my brain churn!

I didn't think about associating specific courses with instructors and the problems that would ensue.

Would it be possible to simplify this a bit...and just link the SUBJ, not the specific course with Instructors? That would work just fine. What will happen is that when someone clicks on a SUBJ field, such as CIS, they will be taken to the CIS records in Class Schedule. That would be great...for now...(you should know me by now that I like taking the script past its potential! ).

Now, the only tricky part is that Instructors do teach in multiple areas...so, the link would have to be written in such a way that if, let's say...an Instructor teachs CIS, OIS, and HUM...that the links for the Subject areas will go to the correct "page" in the Class Schedules database.

Does this make sense? Sorry to be so complicated.

Regards,

------------------
Eliot Lee
Founder and Editor
Anthro TECH, L.L.C
http://www.anthrotech.com/
info@anthrotech.com
==========================
Coconino Community College
http://www.coco.cc.az.us/
Web Technology
Coordinator
elee@coco.cc.az.us

Now I can understand what you're talking about!

First we'll need to break the field into individual courses. You mentioned that the courses are listed like

CIS 102~~CIS 200~~CIS 232

but that would be how they are in the database. By the time they get to sub html_record, they are like

CIS 102|CIS 200|CIS 232

(or whatever your delimiter is).

First, we'll stick all those into an array:


Now we need to eliminate the extraneous stuff, because all we want are the letters.


We're just left with an array of the department codes. But there are some that have duplications, so we need to get rid of those.

Might as well use the same loop we had above.

after

$course =~ s/ //g; # takes out the spaces

add


Now we have an array of all the unique course fields in which the instructor teaches.

All we have to do is print them out:


So, putting it all together, we have


That gives a link to each of the fields the instructor teaches. Is this what you want?

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

Oui!

Merci Beaucoup!

(No, I am not French...Just in a French mood today.)



I really appreciate it!

Regards,

------------------
Eliot Lee
Founder and Editor
Anthro TECH, L.L.C
http://www.anthrotech.com/
info@anthrotech.com
==========================
Coconino Community College
http://www.coco.cc.az.us/
Web Technology
Coordinator
elee@coco.cc.az.us

There's a problem.

The | delimiter is causing a problem. I hadn't tested the code first, but now that I am, I see that it's not going to work.

I'll work on it for a bit and see what I can come up with.

A few minutes later:
Got it!

Instead of


use


That works on my home system.
------------------
JPD







[This message has been edited by JPDeni (edited August 19, 1999).]

Thanks, Carol... I was just about to open the file...Good thing I checked my email to see that you added another response! Whew! That was a close one!

Regards,

------------------
Eliot Lee
Founder and Editor
Anthro TECH, L.L.C
http://www.anthrotech.com/
info@anthrotech.com
==========================
Coconino Community College
http://www.coco.cc.az.us/
Web Technology
Coordinator
elee@coco.cc.az.us

Carol,

Almost...so close! Thank you so much for providing the codes!

Well, it did link the subjects to the records over in the Class Schedules database. However, duplicate subjects and links appeared. To see an example of the output, go to:

http://www.coco.cc.az.us/_private/results.html

I inserted the first set of codes and then replaced the first line with the new codes you provided.

Regards,

------------------
Eliot Lee
Founder and Editor
Anthro TECH, L.L.C
http://www.anthrotech.com/
info@anthrotech.com
==========================
Coconino Community College
http://www.coco.cc.az.us/
Web Technology
Coordinator
elee@coco.cc.az.us