I've got 2 db's: data.db, docs.db and I'd like to pull a record out of data.db into docs. There is a perfect example in the FAQ http://webmagic.hypermart.net/dbman/multi12.txt but it just doesn't seem to work. I've got the build_select_from_other_db working.

Here is what I'm trying to do. I add a record to data, then add a record to docs. In docs I select via pull-down a CompanyName that is built from data.db and save the record (this much works). When I go to modify the record in docs I'd like to: open data.db, use the CompanyName from docs find that record, get the Address (for that particular company), close data, and then finish modifying, using $address however necessary.

The above link (hack) looks like it should be exactly what I need, but the script simply returns nothing for me. I've also tried hacking the "switch_to_db" subs and have had the same results.

I'm also confused because I don't see where it would actually OPEN the other db file for reading?

I don't mind some hacking if anyone can point me in the right direction. Logic tells me it should be: open the other db, read lines, find matching line, get the line, stick it in memory, close the db, and then carry on.

I tried searching but the boolean part doesn't seem to be working at the moment, so I'm getting like 20,000+ hits.

Hi,

I read your post and in it you state:


Can you tell me where you found info on how to achieve this? It's exactly what I am looking for. Thanks!

Lex

Yes, there are several options...

http://webmagic.hypermart.net/dbman/multi.htm

I believe I used the fancy_build_select_from_other_db

You'll have to hunt around to find it, but it's there somewhere.

This one looks really easy to install:
"Select fields compiled from second database"
it is in the FAQ as well. - It'll give you a drop-down of userids from users. The one I used has the "company names" as the option and the "userid" as the value.

If I understand you correctly, you want to access information stored in data.db when logged into docs.db: You want to get a record from data.db, based on a company name, and modify it. I'm not sure what you want to modify: just the address or also information which is stored in docs.db?

I guess both docs.db and data.db have a field CompanyName which is unique, so there will be precisely one record for each company name. It's true that you wouldn't need a search routine for that, you could just open the file and pull the record using the field name number for "company name" from %db_def, and so on.

But the tricky part, I think, is coding a modification form which uses all values for data.db while still running within docs.db, like the database field names, html_form, and the database file. Just off the cuff it seems to me that you'd be better off using a search routine, because that might allow you to simply change the database by setting $db_setup to "data" on submitting the search form for the company name. You'd have to make sure that $db_setup is reset to "docs" at the end of the process, though.

Provided that both data.db and docs.db share the same login routines and user ids, that should work.

Then you'll probably get only one search result - if the values of "company name" are unique - , and that you can modify. But you'd have to make sure that after modification, $db_setup is reset to "docs". You could do that with hidden input values, I suppose.

This is just a suggestion, I haven't tried any of that.





kellner

Thanks for the feed back. What I have working now is this:

In docs, I add a record (company name 'Bob'). Then in add_success (html.pl) I have this:

my ($key, %name_list, $rec_to_archive, @lines, $line, @data, $errstr, $succstr, $output);

$db_name_list = '../data/data.db';

open (OTHER, "<$db_name_list") or &cgierr("error!");
if ($db_use_flock) { flock(OTHER, 1); }
@lines = <OTHER>;
close OTHER;

LINE: foreach $line (@lines) {
if ($line =~ /^$/) { next LINE; }
if ($line =~ /^#/) { $output .= $line; next LINE; }
chomp ($line);
@data = &split_decode($line);
}

If I use 'print @data;' then I get the last record in the data.db. If I use 'print @lines' I get all records (woohoo!). So I know that @lines contains the information I need.

Now I need to find out how to retreive only the record for whose line matches $rec{'Company'} - I've tried:

if (@lines =~ /Bob/) {print "anything";}

In real life it'd be something like:
if (@lines =~ /$rec{'Company'}/) {print "@lines[3]";}

I think I'm on the right track, I'm just not sure how to go about it.

You would be better changing that slightly. As you have it, it will be slow as everything is being pushed into an array and then you start another foreach loop. After open ( ... ) you should use a while loop.



You mean $lines[3] :)

I'm not sure how to do the 'matching' part.

So let's say @lines contains:

123|Joe|Smith|ABC Company
456|Bob|Jones|XYZ Company
789|Sue|White|Your Company

If $ID=456 (for this record) then how do I get the right record and the 4th field (position 3)?

Would I use: if (@lines =~ /456/) { };

I'm sure this has been covered a million times, but the forum search function is still not working properly and my (basic) perl book example assumes you only have one line. The multiple lines has me stumped. Of course I'm sure &split_decode has something to do with it.
Example: @lines = ('123','Bob','Smith','ABC Company');
print $lines[3]
(returns ABC Company)

Any help is appreciated -Mike.

If you do it like I mentioned above, each line is split and put into @data so if you have.......

1|Bla|Foo|Bar

Then @data would contain.....

$data[0] = 1
$data[1] = Bla
$data[2] = Foo
$data[3] = Bar

......that would then change on the next loop.

What exactly are you trying to match?.....here are a few examples to put under @data = &split_decode($_);

($data[0] == 1) and print 'Field 1 is 1';

($data[1] =~ /^\w+$/) and print 'Field 2 only contains letters';

Actually, if you have a record stored in array @record,
it's pretty easy to get the value of a particular field - you just
have to use the field number stored in $db_def{'field_name'}[0], set in data.cfg.

my $company_name_number = $db_def{'field_name'}[0];

Note that this assumes $db_setup is set to "data", otherwise %db_def will be populated with values for "doc".
If it isn't, just use the real number as printed in data.cfg,
the first after the square bracket next to 'company_name' under %db_def.
(Or whatever else your company name field is called.)

You'll also need the number of the id field = database key field, if you
want to pull a record (for sub &get_record);
my $record_id = $db_def{'key_field'}[0]; # or just assign the number from data.cfg
my $wanted_record_number; # we just declare this here
Then, open the DB:
open (FILE, "<$db_file_name") || &cgierr("can't open $db_file_name: $!");

while (<FILE>) {
# add whatever code you like for skipping comment and empty lines
my @record = split /\|/;
if ($record[$company_name_number] eq "Bob") {
$wanted_record_number = $record[$record_id];
last }# assuming that there will only be one field where the company is called "Bob"
}# end while

&html_record(&get_record($wanted_record_number)) prints out the record,
&html_record_form(&get_record($wanted_record_number)) would give you the record in a form.
Note: As I said, at this point, you need $db_setup to have the value "data".





kellner

Ok, now I understand the reason for the While loop.

I'd be searching for a unique UserID like 'PW1234' that would have been assigned already.

So, could I use the following?:

if ($data[0] == 'PW1234') {
$Fname = $data[1];
$Lname= $data[2];
$Company = $data[3];
}

and later on...

print "$Fname $Lname, $Company";

Is that the proper syntax?

Paul, OMG, it freaking works! THANKS! THANKS! THANKS!

Thanks for your help.

Kellner, thanks for your help as well. I'll keep your example handy in case I run into any bugs.

Once I understood how it was getting from line to line (ie, the loop) then it became crystal clear.

2 days of hacking solved in a couple of hours. Thanks, again!

Mike.

Thanks!

This one:

"Select fields compiled from second database"

did the trick.

Lex

No worries.

You should change

if ($data[0] == 'PW1234') {

to

if ($data[0] eq 'PW1234') {

though.

== is for numbers and eq is for letters