Here is what I had in mind.
1. I have a members.db with only one userid per record with there company information and profile.
2. Have a jobs.db for job posting with the members userid's in each of there records on different job listings.
3. Only members are allowed to add,delete and modify a record in the jobs.db
4. The visitor can only view the jobs.db

Now, to keep it short, I have no problem in creating the data bases (members.cfg/jobs.cfg) and maintain them.

What I am having a problem with is figuring out how to get the information out of the members.db when a visitor views a job listing form in the long view. (my short/long is slightly different than JP's). I need to bring up the one jobs listing record, getting the userid and using it to extract the members profile from the members.db and add it to the long view in the jobs.cfg.

I have some thoughts on this, but do not want to post it so I don't muddle up a response for something not set in stone.

I am not afraid to do radical changes, because this is just in the experimental stage.

I'd need to see more about how you have set it up. If you give a little more detail, I might be able to offer some suggestions.

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

To begin with we are in the jobs.db and jobs.cfg.
When a default visitor clicks to go to the long view, it will bring up the jobs.db info.
But before returning to the view success, on the bottom, or where ever, I would like to access the members.db and extract any information. The problem I see already is even though I can switch to the members.db, I still need to have the members.cfg to go along with it
and I have no clue how you would switch that while in another cfg. and then back again??
I don't mind experimenting with this, because if I can come up with something that works it would be worth it.
The main reason I have a members.db is they will be adding many job postings to the jobs data base and for them to add all there information on every job posting would be nuts. Of course we are trying to do a relational type thing here, just thought we could try it without SQL. I would be able to manage the size of the data base so that would not be a problem.

So the search is in the jobs database, but you want to pull information from the members database, too, right? And you want to have a link within the long display to get the member information.(?)

Maybe if I give you an example of what I think you want, you can tell me where I'm wrong.

I do a search for jobs. I get a list like this, with each job title having a link:

Dog catcher
Rocket scientist
Garbage truck driver

I decide I want to find out more about the Dog catcher position, so I click that link. What appears next is the full info about how to be a dog catcher, except without the information from the members.db. At the bottom of the page there's a link -- "Find out more."

I click on the link and I get a page with the information from the members.db of the person who posted the Dog catcher job.

Where have I got it wrong?

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

That is basically what I had in mind Carol, except, I did not want to put a link to the member database. That I could do. I wanted to pull the information on the same page as the job posting. Thats where we get into trying to do a relational link to another data base, which I do not believe this can be done in dbman unless the .cfg was the same. Unless something else might be feasible?
Also, I do not remember if any one posted a method of archiving a database by date? When a record has been in there for x amount of days, instead of deleting it, create an archive db and append the old records to it. I did a search but did not come up with anything.

Let's worry about one thing at a time, shall we?

So, when I look at the full info for the Dog Catcher job, I would see both the info for the job and the info from the members database, right?

You can do it, but it's tricky and it will work best if you only have one record up at a time. (Otherwise it can get really confusing. Even so, it can be confusing. )

You'll need to set up a couple of variables in your jobs.cfg file that apply to the members database.


You may need to set up similar variables for the jobs database --
@jobs_cols
$jobs_db
$jobs_key
$jobs_key_pos

A lot depends on how you're doing your short and long display and whether you're going to need to have access to the jobs variables after you print out the member information.

Now, I'm assuming that your members database has as its key the userid for the members and that there is a corresponding userid field in the jobs database. It has to be that way in order for this to work. For our purposes, I'll refer to that field as Userid.

In your "long" display, print out the formatting for the jobs info. Then use |; to close off your print statement. Then add the following:


Then print out your display for the members information, using $rec2{'FieldName'} instead of $rec{'FieldName'}.

I don't know if you'll really need to switch back to the jobs database variables or not, but it wouldn't hurt to add those lines.

I have used something similar myself and it works.

I'll see if I can locate the most recent discussion about archiving records.


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



[This message has been edited by JPDeni (edited June 23, 1999).]

Carol, You might have saved the day here. I will work on implementing these changes and get back with you. If this works out, I could incorporate this with other things.
Thanks a bunch, I really do appreciate the time and effort you put into helping us novices on cgi.

Remember, about a month ago I started building a unix box, well I got it running, just need to find the time work with it. Will be installing MySQL soon also. Time permitting.

I think this may help you on the archive part:
http://www.gossamer-threads.com/...um5/HTML/001074.html


JPD worked this out for me a month ago. It moves records x number of days old to another database and then deletes them from the first database.

[This message has been edited by Helpdesk (edited June 23, 1999).]

[This message has been edited by Helpdesk (edited June 23, 1999).]

 



Ok, I must be doing something wrong here. I tried different combinations of the settings, but will not open the data base, or it is just not retrieving the fields?

To make things simple, I am not worried about cosmetics at this point, just have the members.db open, print the selected fields and return to the jobs.db. For now I have even left off the return to jobs.db, but for now it never leaves it.

In my jobs.cfg I have set the variables as:

<---------------snip---------->
@members_cols = (ID,Userid,Company,Name,Address,City,State,Zip,Phone,P_Phone,email);
$members_db = $db_script_path . "/members.db";
$members_key = 'ID';
$members_key_pos = 0;

@jobs_cols = (ID,Userid,Category,Sub_Category_One,Sub_Category_Two,Job_Title,Education,Experience,Date,Close_Date,Open_Close,Salary_Range,Job_Descript,Ot her,Spare_1,Spare_2,Spare_3,Spare_4,Spare_5,Spare_6);
$jobs_db = $db_script_path . "/jobs.db";
$jobs_key = 'ID';
$jobs_key_pos = 0;

and the settings:

# The column name for the database key. Can be any column, but it must be unique!
# You can't have two records with the same key value!
$db_key = 'ID';
# Track the key? Should DBMan keep a counter of the next key to use? This isn't
# neccessary if you can guarantee that your entry in the key field will be unique
# (i.e. a userid).
$db_key_track = 1;
# Database delimeter.
$db_delim = '|';
# Use file locking (1 = Yes, 0 = No). Should be used, but won't work on Win95.
$db_use_flock = 1;
# Auto generate the html forms (1 = Yes, 0 = No).
$db_auto_generate = 0;
# Display Benchmarking Information (1 = Yes, 0 = No).
# use Benchmark; # Uncomment this line if benchmarking is used.
$db_benchmark = 0;
# Display Debugging Information (1 = Yes, 0 = No).
$db_debug = 0;
<----------------snip----------->

In the view long html I have:

<-----------snip---------->
|;
# switch to members database
@db_cols = @members_cols;
$db_key = $members_key;
$db_file_name = $members_db;
$db_key_pos = $members_key_pos;
# get the info from the members database
%rec2 = &get_record($rec{'ID'});

print qq|
Job Title -- $rec2{'Job_Title'}
<br>Company -- $rec2{'Company'}|;

<------------snip------------->

In the members.cfg the field setup:

'ID' => [0, 'numer',6,8,1,''],
'Userid' => [1, 'alpha', -1,15,1,'',''],
'Company' => [2, 'alpha',0, 225, 0, '', ''],
'Name' => [3, 'alpha', 0,80,0,'',''],
'Address' => [4, 'alpha', 0, 80, 0, '', ''],
'City' => [5, 'alpha', 0, 80, 0, '', ''],
'State' => [6, 'alpha', 0, 80, 0, '', ''],
'Zip' => [7, 'alpha', 0, 12, 0, '', ''],
'Phone' => [8, 'alpha', 0, 80, 0, '', ''],
'F_Phone' => [9, 'alpha', 0, 80, 0, '', ''],
'email' => [10, 'alpha', 0, 80, 0, '', '']

and in both *.cfg's:
# Auth user field. This is the field position in the database used for storing
# the userid who owns the record. Set to -1 if not used.
$auth_user_field = 1;

GREAT THREAD.

I am also trying to implement some relational structure. This will help me get started and I will get into the fray!

Please keep this thread going and public!

Lauren

I'm not sure why you used:

Job Title -- $rec2{'Job_Title'}

The job title field is in the jobs database, right?

I see the problem. You need to change the setup so that the $db_key in the members db is the Userid field. You only have one record per userid in that database, right?

One of the basic things you need to understand is that the "one-to-many" relationship between the databases must be based on the key field of "one" side. In this case, the members db is the "one" side, since one member can have many jobs associated with him.

(Note to Lauren: This is the whole crux of the tutorial you're going to be writing. )

Getting really specific, your members .cfg needs to be:


In your sub html_record, when you get the data from the members db, use

%rec2 = &get_record($rec{'Userid'});

I told you it was complicated!

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

 
OK, what do you need Carol? I made all the changes you said and then some.
Still does not either open or not accessing the data base. Trying to figure out how to trouble shoot it,
but haven't come up with anything.
I did make one change in the @members_cols = ('Userid','etc'), by adding the ' ' on the fields because
for some reason I had to add them in my very first dbman before it would work.
I tried both ways and again with other variations.

You say you have this setup working somewhere? What kind of server?

I'm not using it right now, but I did use it in the past for a client and it worked. And there is a guy that I've helped create a database like this who is having very good luck with it. I'm pretty sure it was on a Unix server, but I don't think that should make a difference.

I just realized I made a mistake earlier. I wanted some blank lines and put $nbsp; in the lines instead if & nbsp;. If you have those lines in your script, take them out. I'll go back and fix them after I finish writing this.

I need to run through a couple of things again, just to be sure.

Your members database uses the Userid field as the $db_key.

Your jobs database has a Userid field that matches the value in the members database, but has a numeric key.

Is that correct?

I think I'm going to take this slow for now and just make sure we're on the same page before I go on. I get confused.

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

Tom--

I'm so glad this worked for you. I can beat myself over the head for missing the $auth_modify_own thing, but I have to learn to let it go. (Use the force, JP! )

You gave me quite a chuckle when I went to look at how it worked.

I would really appreciate your creating a mod writeup. (Lauren is doing one, too, but it doesn't hurt to have two of them going. Sometime people understand the way one person writes better than another does.)

I am so pleased -- almost as pleased as you are!!


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

 

Your right Carol, just to make sure we are all on the same page.
In reply to your questions.
<-----------------------------snip--------------------->
I'm not using it right now, but I did use it in the past for a client and it worked. And there is a guy that I've helped create a database like this who is having very good luck with it. I'm pretty sure it was on a Unix server, but I don't think that should make a difference.
<-----------------------------snip--------------------->
I am on a standard Unix/Lunix Virtual server with Apache setup.

<-----------------------------snip--------------------->
I just realized I made a mistake earlier. I wanted some blank lines and put $nbsp; in the lines instead if & nbsp;. If you have those lines in your script, take them out. I'll go back and fix them after I finish writing this.
<-----------------------------snip--------------------->
I did not even give this another thought about not putting the in there, I know what it is. I started writing HTML in notepad 3 years ago, and never used "Frontpage" or anything like it. (Well mabey I get lazy and use "Homesite", but not there wizards)

<-----------------------------snip--------------------->
Your members database uses the Userid field as the $db_key.
<-----------------------------snip--------------------->
That is correct.
'Userid' => [0, 'alpha', -1,15,1,'',''],
$db_key = 'Userid';
$db_key_track = 0;
$auth_user_field = 0;

<-----------------------------snip--------------------->
Your jobs database has a Userid field that matches the value in the members database, but has a numeric key.
<-----------------------------snip--------------------->
This is correct. To expand on this, only the members, and there unique Userid from the members.db will be on any records entered in the Jobs.db.
'Userid' => [0, 'alpha', -1,15,1,'',''],
'ID' => [1, 'numer',6,8,1,''],
$db_key = 'ID';
$db_key_track = 1;
$auth_user_field = 0;


This is in the sub html_record at the bottom of the page before the footer and not in a table.
<-----------------------------snip--------------------->
|;
# switch to members database
$db_file_name = $members_db;
@db_cols = @members_cols;
$db_key = $members_key;
$db_key_pos = $members_key_pos;
# get the info from the members database
%rec2 = &get_record($rec{'Userid'});

print qq|
<center><font size="+2" color="#FF0000"> $rec2{'Name'}<br>$rec2{'Company'}<br>$rec2{'Userid'}<br>$rec2{'Address'}<br>$rec2{'email'}</font></center&g t;<br><br>|;

# switch back to the jobs database
@db_cols = @jobs_cols;
$db_key = $jobs_key;
$db_file_name = $jobs_db;
$db_key_pos = $jobs_key_pos;
print qq|
<-----------------------------snip--------------------->

This is in the jobs.cfg:
<-----------------------------snip--------------------->
@members_cols = ('Userid','Company','Name','Address','City','State','Zip','Phone','F_Phone','email');
$members_db = $db_script_path . "/members.db";
$members_key = 'Userid';
$members_key_pos = 0;
<-----------------------------snip--------------------->
As a note, I even copy/paste the whole %db_def = from the members.cfg to the @members_cols just to see if that worked.
I also used either my modified db.cgi or a fresh un-modified copy of db.cgi.
I do use my own templates, aka - jobs_view_html.pl, jobs_add_html.pl, etc.... And have never had a problem with this.
There are no bells and toots to speak of in this setup yet, just trying to see if we can get this part working.

I know your busy with your clients and moderating this forum, and I do appreciate your help on this mod.
Tom

Thanks for the info. That really does help.

I realized something after I wrote the rest of the stuff below. Try this first:

After

$db_file_name = $members_db;
@db_cols = @members_cols;
$db_key = $members_key;
$db_key_pos = $members_key_pos;

add

$auth_modify_own = 0;

It won't change whether or not people can modify their own records, since the change is just temporary. But this might be the thing getting in the way.

If that doesn't work, proceed with the debugging below.


Okay. Time for a bit of debugging.

Before your lines

print qq|
<center><font size="+2" color="#FF0000">
$rec2{'Name'}<br>$rec2{'Company'}<br>$rec2{'Userid'}...

Add

foreach $column (@db_cols) {
print "$column<BR>";
}

That will tell us if the field names are being changed. What you should get is a list of the field names from the members database.

If you do get the correct list, take out the lines above and, in their place, add

print "$db_key $db_file_name $db_key_pos";

You can see what you should expect from those.

If those come out correct, I'll have to think of something else to debug, because those should be the only things you should need to use &get_record.


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







[This message has been edited by JPDeni (edited June 25, 1999).]

Carol, the only change I just made was add the
$auth_modify_own = 0;
to the jobs_view_html.pl where you said to put it.
Your correct on this, because, we are not going to add/modify/delete the .db, just view it.

Go and check it out and list all jobs.
(only 2 dummy records)
Now, this mod is a start of great things to come.
http://www.jobspostedhere.com/test2.htm

I will remember to put $auth_modify_own = 1; after the print statement and possibly the the return back to the jobs.db if needed.

I also will put together the whole thing like I
have it, so you can use it on your mods for your web site.
Thank you so much Carol, I'm sure you made a lot of people happy with this one, and you sure made mine. Keep up the good work.
I will be in touch.
Tom