I have searched hard for a solution to this that I can understand, but my novice status is making that tough. I feel like a number of people out there have solved this problem, but I'm just not getting it (or at least, how it applies to my particular setup).

I have two databases: one full of people (one record = one individual) and one full of photographs. The photographs database can have up to 24 numbers entered for each photo, indicating who is pictured in the photo. The numbers are to be record keys for the individuals in the people database.

What I want to do is, when displaying a photo, list the names of the individuals appearing in it. I can list their ID's, and crosslink to the person records, no sweat. The problem is showing the names instead of the ID numbers.

Again, I know this has been addressed, but I'm really having a hard time following the solutions (or potential solutions)... if a fully laid out explanation already exists I'd be happy to just be directed to it.

Thanks for any help.

quote:
I can list their ID's, and crosslink to the person records, no sweat. The problem is showing the names instead of the ID numbers.

Can you provide the link and switch codes you are using to crosslink the databases. This will help us to help you find a solution by perhaps just adjusting the codes.

Are you using the userid as the key in your people database?

Unoffical DBMan FAQ

http://creativecomputingweb.com/dbman/index.shtml/

here is the line I'm using for displaying the ID number and linking to the record for the person pictured:

<a href="$db_script_link_url?db=gene&uid=$db_uid&view_records=1&ww=1&Record_Key=$rec{'Ind_Rec_01'}">$rec{'Ind_Rec_01'}</a>

There would be 24 of these, one for each of 24 fields (Ind_Rec_01 thru Ind_Rec_24).

There is no switching being done yet, as that's what I'm really not understanding.

To answer the other question, yes, the content of these fields is the main db key for the person records.

your DB structure isn't really suited to the relational mod, though you could prolly work around that given enough motivation. A typical relational DB looks like this...

User 1 ==> Picture1, Picture2, Picture3
User 2 ==> Picture4
User 3 ==> Picture5, Picture6

As I understand it, your DB looks like this...
Picture 1 ==> User 1, User 2, ... User 24 (each of these are yes or no, e.g. 1 or 0)
Picture 2 ==> User 1, User 2, ... User 24 (each of these are yes or no, e.g. 1 or 0)
Picture 3 ==> User 1, User 2, ... User 24 (each of these are yes or no, e.g. 1 or 0)

Here's a simple hack... add a new sub to your picture.html
and call the name like this...
print &getName($rec{'Ind_Rec_01'});

Without knowing more about your implemenation that's about as far as I can take it, but should work ok... if this is going to be a big DB, I'd prolly hash the names for quicker lookup, but then you'd have to hire me as a consultant or something.

Actually I think what I'm doing looks more like your first example, but with the types flipped. the 24 input fields are not connected to 24 person records in yes/no fashion, they're just 24 open spaces for any number of person records.

i.e.

this picture has these people in it: (followed by 24 fields that can be filled with any one of the person DB's keys).

does this change what you would suggest?

I'm also beginning to think I should have gone with a single input field, with the keys separated by commas or something.. but that's a question for another day.

If you are saying that your DB looks like this:

Picture 1 ==> User1, User2
Picture 2 ==> User3
Pciture 4 ==> User4, User5, User6

...then it is a good candidate for the relational mod (which will make your life MUCH easier). But somehow I don't think that's what you mean, since I'd guess that User1 could be in picture1 or picture7 or picture127, yes? And picture1 could have User1 and User3 and User 19 in it, right?

A relational DB has to have a one to many relationship... unless I am seriously-coffee deprived this morning and am mising something obvious.

I'm sorry for being vague. Here, using your style, is what mine looks like (your "but somehow..." assessment is correct):

Picture 1 ==> User1, User2
Picture 2 ==> User2, User5
Pciture 4 ==> User1, User2, User5

The people can be in limitless pictures, and the pictures can have up to 24 people.

Is that better?

Thanks for your patience with my weak descriptions.

Perhaps when the record for the group photo is entered, it would be better to enter the 1-24 persons as the persons name rather than the UserID. You could create a pull-down select list of names that can be built from the User(people) database. Then to make the link back to the person's(User) information, the "name" could be part of a search on the user database. Unless of course there are 2 or more people with the same name. Hum?

The 24 needs to be ignored, it's an arbitrary number chosen because it seemed adequate while at the same time being spacially convenient (and if I do eventually go to a single field it will also be fully irrelevant). The list of people is to be limitless, so I can't make a select box with names. and if I could do that, wouldn't I also be solving the problem of getting the names from the IDs? not sure.

Anyway, I feel fairly confident that what I need to be doing here is switching databases in the middle of the view_record sub, for the purpose of grabbing this data, but I can't make sense of how that's done.

Based on what you are saying, I don't see the relational mod working for ya (at least not without some substantial tinkering).

The hack above does effectively switch databases to pull out the name. Yes, it's a one trick pony, but if all you need is the name, it should work...

Ok pardon my density, I'm working on it... many thanks for your help thus far.

Here is what I've put in - I'm unsure of what variables I should be changing to match my stuff. 2 is the number of the name field I'm trying to access, so should that be left as-is on the second line? All the below is doing is making the ID# print again, i.e. this call:

print qq| $rec{'Ind_Rec_01'}|; print &getName($rec{'Ind_Rec_01'});

produces the result "3 3" if the ID is 3.

sub getName {
# --------------------------------------------------------
my ($theID) = $_[0];
my ($db_UserName_field)=2; #<-Change this to fit your DB
open (DB, "<./gene.db") or &cgierr("error in sub getName.... unable to open file $db_file_name.\nReason: $!");
if ($db_use_flock) { flock(DB, 1); }
@lines = <DB>;
close DB;
LINE: foreach $line (@lines) {
if ($line =~ /^$/) { next LINE; }
if ($line =~ /^#/) { next LINE; }
chomp ($line);
@data = &split_decode($line);
if ($data[$db_UserName_field] eq $theID) {
$theID=$data[$db_UserName_field];
last; # <- found stop processing
}
return $theID; }
}

this part seems circular...?

if ($data[$db_UserName_field] eq $theID) {
$theID=$data[$db_UserName_field];

ok, the logic has come together, I get it. And it works, if you stick another '}' after "last;".

Many many thanks!!!!

Glad it works! If this DB becomes larger (say, more than 6 people per picture on average or more than several hundred people in the DB), I'd spend the time writing a hash for faster lookup. This would prevent opening up the people DB and circling through it everytime you need a name, but the overhead of this is not noticiable when your DB is small.