Gossamer Forum: Products: DBMan: Customization: Importing one field from another db

Novice (25 posts)

Aug 17, 1999, 9:14 AM

Post #1 of 24

Shortcut

Importing one field from another db

Hello!

I´ve been browsing this forum and found great help of modifinig my dbs to where I want it. Thanks to everyone involved!

But, I just got stuck with one thing.. I´m having two DBmans running, they work fine both of them. Using no authentication (one of them might in the future), fields are completely different and they have no common files at all (since I´ve been on to the db.cgi in one of them).

Here´s the problem:
I´d like to import one field from one of the dbs when adding a new record for the other one. To explain it easier, my databases are: nightclubs and events. When adding an event I´d like to have a drop down list of the names of the nightclubs so the adder can select where the events will be held.

Thanks for any help!

nightowl, Stockholm

Aug 17, 1999, 6:40 AM

Veteran / Moderator (8669 posts)

Aug 17, 1999, 6:40 AM

Post #2 of 24

Shortcut

Re: Importing one field from another db In reply to

Let's see if I can remember how to do this. Smile

Assuming that you have only one field that you're going to be pulling from the other database --

in db.cgi, add

Code:
sub build_select_field_from_other_db { 
# -------------------------------------------------------- 
# Builds a SELECT field from the database.  

  my ($column, $value) = @_; 
  my (@fields, $field, @selectfields, @lines, $line, $output); 

  $db_other_file_name = 'name of external database file'; 
  $fieldnum = number of the field that holds the data you want; 

  open (DB, "<$db_other_file_name") or &cgierr("unable to open $db_other_file_name. Reason: $!"); 
    if ($db_use_flock) { flock(DB, 1); } 
    LINE: while (<DB> ) {		 
      next if /^#/; 
      next if /^\s*$/; 
      $line = $_; 
      chomp ($line);		 
      @fields = &split_decode ($line); 
      if (!(grep $_ eq $fields[$fieldnum], @selectfields)) { 
        push (@selectfields, $fields[$fieldnum]); 
      } 
    } 
  close DB; 

  $output = qq|<SELECT NAME="$column"><OPTION>---|; 
  foreach $field (sort @selectfields) { 
    ($field eq $value) ? 
      ($output .= "<OPTION SELECTED>$field") : 
      ($output .= "<OPTION>$field"); 
  } 
  $output .= "</SELECT>"; 

  return $output; 
}

Be sure to fill in the values of
$db_other_file_name

and

$fieldnum

to match your database.

Then, in html.pl, sub html_record_form, use

Code:
<tr><td>Nightclub:</td> 
    <td>|; 
print &build_select_field_from_other_db("Nightclub",$rec{'Nightclub'}); 
print qq|</td></tr>

Be sure to use your own field name instead of Nightclub above.

This should do it.

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

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

Aug 21, 1999, 7:09 AM

Novice (25 posts)

Aug 21, 1999, 7:09 AM

Post #3 of 24

Shortcut

Re: Importing one field from another db In reply to

Yea, works fine.. Thank you very much!!

Just took some time before I figured out to put a ";" after the $fieldnum = 1 ..

Thanks again!

/NightOwl

Aug 21, 1999, 8:14 AM

Veteran / Moderator (8669 posts)

Aug 21, 1999, 8:14 AM

Post #4 of 24

Shortcut

Re: Importing one field from another db In reply to

Sorry 'bout that. Glad it works for you, though.

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

Aug 30, 1999, 10:42 AM

Novice (25 posts)

Aug 30, 1999, 10:42 AM

Post #5 of 24

Shortcut

Re: Importing one field from another db In reply to

Umm.. I´m back! .. Still works fine, but I have a new question on the same topic!

I´d like to link the imported field (in my case the nightclub) to the record in the "other_db".

Any idea of how to do this?

Aug 30, 1999, 11:28 AM

Veteran / Moderator (8669 posts)

Aug 30, 1999, 11:28 AM

Post #6 of 24

Shortcut

Re: Importing one field from another db In reply to

Instead of where you print out your $rec{'Nightclub'} value in html_record, use:

Code:
<a href="$db_script_url?db=name of other database&uid=$db_userid 
&Nightclub=$rec{'Nightclub'}&view_records=1&ww=1">$rec{'Nightclub</a>

This assumes that the field names are the same in each of the databases.

The only thing that might be a problem is that you said earlier:

Quote:

they have no common files at all (since I´ve been on to the db.cgi in one of them).

If your db.cgi files are different, you won't be able to use $db_script_url. You'll need to substitute the full path for the other db.cgi file.

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

Aug 30, 1999, 11:30 AM

Veteran / Moderator (8669 posts)

Aug 30, 1999, 11:30 AM

Post #7 of 24

Shortcut

Re: Importing one field from another db In reply to

I made an error in the previous post and for some reason I can't edit it.

...&ww=1">$rec{'Nightclub</a>

should be

...&ww=1">$rec{'Nightclub'}</a>

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

Aug 30, 1999, 11:48 PM

Novice (25 posts)

Aug 30, 1999, 11:48 PM

Post #8 of 24

Shortcut

Re: Importing one field from another db In reply to

Thanks Carol!

But, but, but .. That works when the name of the nightclub is only one word. When there´s two ore more words, there´ll be a space in the URL and it´ll return me an Invalid URL.
Is there some way to translate the space to a "+" ? Or maybe it´s better to work on the record ID for the nightclub? Hummmm...

Aug 31, 1999, 8:51 AM

Veteran / Moderator (8669 posts)

Aug 31, 1999, 8:51 AM

Post #9 of 24

Shortcut

Re: Importing one field from another db In reply to

I always forget about that. Smile

If you're only going to be having spaces to worry about, and not any other odd characters, at the beginning of html_record, just after

my (%rec) = @_;

add

Code:
$nightclub = $rec{'Nightclub'}; 
$nightclub =~ s/ /+/g;

If you might have other characters besides letters and numbers, you'll need to add the following to db.cgi:

Code:
sub urlencode { 
# -------------------------------------------------------- 
# Escapes a string to make it suitable for printing as a URL. 
# 
  my($toencode) = shift; 
  $toencode =~ s/([^a-zA-Z0-9_\-.])/uc sprintf("%%%02x",ord($1))/eg; 
  return $toencode; 
}

and, instead of the first code I gave you above, use

Code:
$nightclub = &urlencode($rec{'Nightclub'});

Then, make your link be

Code:
<a href="$db_script_url?db=name of other database&uid=$db_userid 
&Nightclub=$nightclub&view_records=1&ww=1">$rec{'Nightclub'}</a>

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

Sep 1, 1999, 6:19 AM

Novice (25 posts)

Sep 1, 1999, 6:19 AM

Post #10 of 24

Shortcut

Re: Importing one field from another db In reply to

You´re the girl ..
Thank you!!!!!!
Or as we say in Sweden -> tack!

/NightOwl

Sep 1, 1999, 8:47 AM

Veteran / Moderator (8669 posts)

Sep 1, 1999, 8:47 AM

Post #11 of 24

Shortcut

Re: Importing one field from another db In reply to

As we say in America -- "no problemo!" Smile

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

Sep 1, 1999, 11:58 AM

Novice (25 posts)

Sep 1, 1999, 11:58 AM

Post #12 of 24

Shortcut

Re: Importing one field from another db In reply to

Again.. works 99% .. heh!

Seems like names with é (as in café) wont work.. but even swedish characters like åäöÅÄÖ works.

Any idea for the "é" ?

(If this turns out hard, don´t mention puttin any effort in this, I could work around it with my names..)

/NightOwl

Sep 1, 1999, 12:29 PM

Veteran / Moderator (8669 posts)

Sep 1, 1999, 12:29 PM

Post #13 of 24

Shortcut

Re: Importing one field from another db In reply to

Did you try the &urlencode option? It should work.

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

Sep 3, 1999, 6:32 AM

Novice (25 posts)

Sep 3, 1999, 6:32 AM

Post #14 of 24

Shortcut

Re: Importing one field from another db In reply to

Yeah, I use the &urlencode .. It translates the "é" to "%E9", I´m not sure about those codes. Maybe my web server is configured wrong? Haven´t really touched those codes though .. Anyway, nevermind it´s not so important.

Thanks for all help!

/Nite Owl

Sep 3, 1999, 7:15 AM

Veteran / Moderator (8669 posts)

Sep 3, 1999, 7:15 AM

Post #15 of 24

Shortcut

Re: Importing one field from another db In reply to

I understand that it would translate the letters into things like "%E9" for the link. But after you click the link, it should translate them back again and the search should work.

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

Dec 9, 2005, 8:04 PM

Novice (30 posts)

Dec 9, 2005, 8:04 PM

Post #16 of 24

Shortcut

Re: [JPDeni] Importing one field from another db In reply to

Hi JPDeni,

i have implemented this little mod for a new project of mine and was wondering if i wanted to have 4 fields pulled from another
database what would be the necesary changes to db.cgi file ?

thanks
Denny

Dec 10, 2005, 6:42 AM

Veteran / Moderator (8669 posts)

Dec 10, 2005, 6:42 AM

Post #17 of 24

Shortcut

Re: [dstamos] Importing one field from another db In reply to

Last edited by:

JPDeni: Dec 10, 2005, 6:44 AM

Dec 10, 2005, 10:00 AM

Novice (30 posts)

Dec 10, 2005, 10:00 AM

Post #18 of 24

Shortcut

Re: [JPDeni] Importing one field from another db In reply to

In Reply To:

I'm not sure I understand. Let me give you an example of what I think you mean, and you can tell me if I've got it. :-)

One database has information about authors. The other has information about books. In the authors db, are the following fields:

AuthorName
AuthorDateOfBirth
AuthorPlaceOfBirth
AuthorDateOfDeath
AuthorPlaceOfDeath
AuthorBiography

The AuthorName field is the $db_key field.

You want to connect the Book database to the Author database, so you have a select field that is built from the AuthorName database. That would be one field. With your suggestion of more fields, you want to import the date of birth, place of birth, date of death and place of death, say. That would be inefficient. It would be better to just use the AuthorName field and then use the relational database structure to print out the information when the record is displayed.

If I am completely misunderstanding what you're wanting to do, please give me an example of what you want and I'll see if I can help you with it.

Edited to add:
The original post here is from six years ago. Since that time, I wrote information about how to make a relational database, which would be much easier and much more efficient than the code I gave above.

Hi JPD,

thanks for the reply, your right on about what i want to do. I implemented the code you wrote 6 years ago to test with one field
and it worked great. So now i want to use 3 more fields from my other db to generate a drop down lists.

Here is what i have, my main db is called inventory.db and holds information about applications (12 fields). The other db is smaller and will contain only 4 fields "name", "group", "service", "location" and this db is called names.db. Names db has separate files but shares the db.cgi & /auth and the inventory.pass file.

Basically i do not want to use the "%db_select_fields" in the inventory.cfg file and would rather pull them from the names.db.

hope that this is well explained and should i be using relational database structure instead?

Denny

Dec 10, 2005, 11:19 AM

Veteran / Moderator (8669 posts)

Dec 10, 2005, 11:19 AM

Post #19 of 24

Shortcut

Re: [dstamos] Importing one field from another db In reply to

It really would be best to use the relational structure. It's much more efficient. For one thing, with the relational structure, if you ever changed anything in the names.db file, you wouldn't have to change it in all of the records in the inventory.db file.

Take a look at the relational structure information at http://www.jpdeni.com/.../Mods/relational.txt . Read it over and see what you think. If you have any questions at all, don't hesitate to ask.

JPD
----------------------------------------------------
JPDeni's DBMan-ual
How to ask questions the smart way.

Dec 12, 2005, 8:05 AM

Novice (30 posts)

Dec 12, 2005, 8:05 AM

Post #20 of 24

Shortcut

Re: [JPDeni] Importing one field from another db In reply to

In Reply To:

When you say that if i change something in names.db, I dont understand how i would have to modify every record in inventory.db? I am only using the names.db to populate my drop down listin inventory.db

i looked at your relational mod text and for myself its too complicated i do not know enough about perl to implement this. The code i used in this thread works great, its only going to be used when adding a record to inventory.db, most of the time i will be searching or browsing it.

let me know if you can help to do this without a relational structure

thanks for your time..
Denny

Dec 12, 2005, 8:11 AM

Veteran / Moderator (8669 posts)

Dec 12, 2005, 8:11 AM

Post #21 of 24

Shortcut

Re: [dstamos] Importing one field from another db In reply to

Can you explain exactly what you intend to do? I obviously have misunderstood what you're trying to accomplish. If you could give examples, it would help a whole lot.

JPD
----------------------------------------------------
JPDeni's DBMan-ual
How to ask questions the smart way.

Dec 13, 2005, 11:13 AM

Novice (30 posts)

Dec 13, 2005, 11:13 AM

Post #22 of 24

Shortcut

Re: [JPDeni] Importing one field from another db In reply to

Hi JPD,

Ok i took a good look at what i am trying to do and put some thought into this and its not really going to work with 2 db's. Thats probably why i am confusing you.

My original idea is that i wanted my "%db_select_fields" in my config file to point to external files as these would be updated from every night by a unix script that is importing data in a comma seperated format. I tried this my way and got it to work to populate the drop down list but when i wanted to modify the record the record info was not there it would blank out all those fields, so i would have to re-enter again those 4 fields.

So probably i was missing some code in my db.cgi and html.pl to make it work properly. Should i just post this request in a new post ?

here was the original post i found it from , was it done correctly by this person? http://www.gossamer-threads.com/perl/gforum/gforum.cgi?post=135920#135920

Last edited by:

dstamos: Dec 13, 2005, 11:48 AM

Dec 13, 2005, 3:05 PM

Veteran / Moderator (8669 posts)

Dec 13, 2005, 3:05 PM

Post #23 of 24

Shortcut

Re: [dstamos] Importing one field from another db In reply to

Try the information in the thread at http://www.gossamer-threads.com/...d%20external;#183234 . It shouldn't make any difference whether there's one or four or 20 of these fields. You may have to do some adjustments to account for a comma-delimited file, though.

JPD
----------------------------------------------------
JPDeni's DBMan-ual
How to ask questions the smart way.

Dec 14, 2005, 2:23 PM