Gossamer Forum
Home : Products : DBMan : Customization :

Displaying data from one DB on the same pg as data from another DB.

Quote Reply
Displaying data from one DB on the same pg as data from another DB.
I have two DB, 1 called “Leads” and the other called “Notes”. What I would like to happen is: after you do a search and pick a record to view or modify from the “Lead” DB, the Lead information will appear in the top half of the page and the notes for this company will appear in the lower half of the page as static information.

Can I have the “notes” display in a frame or table inside the Lead form?

I’m attaching and image of what i’m thinking.

I don’t have a problem with each form display, it’s getting them to display on the same form I need help with.

Thank you, Ed.
Quote Reply
Re: [knue] Displaying data from one DB on the same pg as data from another DB. In reply to
i use the relational db hack extensively to do things like this. here's an example. i have a recipe database that has recipe title and description. then i have another db that has the ingredients. each record in ingredients has a recipeID field to relate back to the recipe. in the place in the recipe db where i want to list the ingredients i have the following:
Code:
&switch_to_ingredients("ingredients");
my ($db2_key_pos) = 3; ###
my (%save_in) = %in;
undef %in;
$in{'RecipeID'} = $rec{'RecipeID'};
$in{'sb1'} = 5;
$in{'so1'} = 'ascend';
$in{'mh'} = 100;
my ($sort_type2) = 'numer';

my ($status2,@hits2) = &query2("view");

if ($status2 eq "ok") {
my ($numhits2) = ($#hits2+1) / ($#db2_cols+1);
print qq|<table width="640"><tr><td valign="top"><strong>Ingredients:</strong></td><td align="left" width="150" valign="top">|;

for (0 .. $numhits2 - 1) {
%rec2 = &array_to_hash2($_, @hits2);
print qq|<tr><td valign="top" width="25">$rec2{'Name'}</td>
<td valign="top" width="5">$rec2{'Quantity'} $rec2{'Measure'}|;
if ($rec2{'Process'}) { print qq|, $rec2{'Process'}|; }
print qq|</td></tr>|;
}
print qq|</table>|;
$delete= 1;
}
else {
print qq|<table><tr><td>No Ingredients. </td></tr></table>|;
}
##### End Ingredients
here's the code for the relational
Code:
sub switch_to_ingredients {
#-----------------------------------------------------
undef @db2_cols;
$configfile = "$_[0].def";
&get_fieldnames2($configfile);

$db2_file_name = $db_script_path . "/ingredients.db";
$db2_key_pos = 0;
$db2_delim = '|'; # 3/17/2009
}


sub get_fieldnames2 {
#-----------------------------------------------------
# pulls field names from the def file!
# check def file to be sure field names enclosed in single quotes!

#$configfile = "$_[0].def";

$configfile = $db_script_path . "/" . $configfile;
open(FILE, "<$configfile") || &cgierr("Cannot open $configfile.\n$!");
local $/;
my ($fields);
while (<FILE>) { if (/(%db_def\s+=\s+\(\s+)('.*?)(\);)/s) { $fields = $2;}}
close(FILE);

my @split = split /\n/, $fields;
foreach (@split) {
if ($_ =~ /'(\w+)'/) { push @db2_cols, $1; }
}

sub get_record2 {
# --------------------------------------------------------
# Given an ID as input, get_record returns a hash of the
# requested record or undefined if not found.

my ($key2, $found, $line, @data, $field, $restricted);
my ($notfound);
$key2 = $_[0];
$found = 0;
# spambuster hack delicia comment next line
# ($restricted = 1) if ($auth_modify_own and !$per_admin);

open (DBX, "<$db2_file_name") or &cgierr("error in get_records. unable to open db file: $db2_file_name.\nReason: $!");
flock(DBX, 1);
LINE: while (<DBX>) {
(/^#/) and next LINE;
(/^\s*$/) and next LINE;
$line = $_;
chomp ($line);
@data = &split_decode2($line);
#next LINE if ($restricted and ($db_userid ne $data[$auth_user_field]));
if ($data[$db2_key_pos] eq $key2) {
$found = 1;
for ($j = 0; $j <= $#db2_cols; $j++) { # Map the array columns to a hash.
$rec2{$db2_cols[$j]} = $data[$j];
}
last LINE;
}
$notfound .= $data[$db2_key_pos] ;
}
close DBX;

$found ?
(return %rec2) :
(return undef);
# $found ?
# (return %rec2) :
# (return $notfound);
#

}
##########################################################
sub split_decode2 {
# --------------------------------------------------------
# Takes one line of the database as input and returns an
# array of all the values. It replaces special mark up that
# join_encode makes such as replacing the '``' symbol with a
# newline and the '~~' symbol with a database delimeter.

my ($input) = shift;

$input =~ s/\Q$db2_delim\E$/$db2_delim /o; # Add a space if we have delimiter new line.
my (@array) = split (/\Q$db2_delim\E/o, $input);
for ($j = 0; $j <= $#array; $j++) {
$array[$j] =~ s/~~/$db2_delim/og; # Retrieve Delimiter..
$array[$j] =~ s/``/\n/g; # Change '' back to newlines..
}
return @array;
}
##########

sub query2 {
# --------------------------------------------------------
# First let's get a list of database fields we want to search on and
# store it in @search_fields

my ($i, $column, @search_fields, @search_gt_fields, @search_lt_fields, $maxhits, $numhits, $nh,
$field, @regexp, $line, @values, $key_match, @hits, @sortedhits, $next_url, $next_hit, $prev_hit,
$first, $last, $upper, $lower, $left, $right, $restricted);

my ($numrecs);

# local (%sortby);
local (%sortby1);
local (%sortby2);
local (%sortby3);
$in{'sb1'} = (($in{'sb1'}) ? $in{'sb1'} : $sortfield1 );
$in{'sb2'} = (($in{'sb2'}) ? $in{'sb2'} : $sortfield2 );
$in{'sb3'} = (($in{'sb3'}) ? $in{'sb3'} : $sortfield3 );
$in{'so1'} = (($in{'so1'}) ? $in{'so1'} : $sortorder1 );
$in{'so2'} = (($in{'so2'}) ? $in{'so2'} : $sortorder2 );
$in{'so3'} = (($in{'so3'}) ? $in{'so3'} : $sortorder3 );
########## multiword keyword search
if ($in{'keyword'} =~ / /) {
$in{'keyword'} =~ s/ /|/g;
$in{'re'} = 1;
}
###########

######## delicia hack
### two different possibilities for showing validated records
### first one below shows unvalidated records to admin only
### second one shows to anyone who is logged in
### use admin_only flag in cfg to choose which <----------


######## end validated records hacks

# First thing we do is find out what we are searching for. We build a list of fields
# we want to search on in @search_fields.
if ($in{'keyword'}) { # If this is a keyword search, we are searching the same
$i = 0; # thing in all fields. Make sure "match any" option is
$in{'ma'} = "on"; # on, otherwise this will almost always fail.
foreach $column (@db2_cols) {

# if (($db_sort{$column} eq 'date') or &date_to_unix($in{'keyword'})) { $i++; next; }
# if ($i == $auth_user_field) { $i++; next; } #10/1/2008 comment this line to find users in keyword box

push (@search_fields, $i); # Search every column
$in{$column} = $in{'keyword'}; # Fill %in with keyword we are looking for.
$i++;
}
}
else {
$i = 0; # that match everything the user specified for.
foreach $column (@db2_cols) {
# if ($in{$column} =~ /^\>(.+)$/) { ($db_sort{$column} eq 'date') and (&date_to_unix($1) or return "Invalid date format: '$1'");
# push (@search_gt_fields, $i); $in{"$column-gt"} = $1; $i++; next; }
# if ($in{$column} =~ /^\<(.+)$/) { ($db_sort{$column} eq 'date') and (&date_to_unix($1) or return "Invalid date format: '$1'");
# push (@search_lt_fields, $i); $in{"$column-lt"} = $1; $i++; next; }
if ($in{$column} !~ /^\s*$/) {
# if ($db_sort{$column} eq 'date') {
# if (&date_to_unix($in{$column})) {
# $in{$column} = &get_computed_date(&date_to_unix($in{$column}));
# }
# else {
# return "Invalid date format: '$in{$column}'";
# }
# }
push(@search_fields, $i); $i++; next;

}
if ($in{"$column-gt"} !~ /^\s*$/) { ($db_sort{$column} eq 'date') and (&date_to_unix($in{"$column-gt"}) or return qq|Invalid date format: '$in{"$column-gt"}'|);
push(@search_gt_fields, $i); }
if ($in{"$column-lt"} !~ /^\s*$/) { ($db_sort{$column} eq 'date') and (&date_to_unix($in{"$column-lt"}) or return qq|Invalid date format: '$in{"$column-lt"}'|);
push(@search_lt_fields, $i); }
$i++;
}
}
# If we don't have anything to search on, let's complain.
if (!@search_fields and !@search_gt_fields and !@search_lt_fields) {
return "no search terms specified";
}

# Define the maximum number of hits we will allow, and the next hit counter.
if ($_[0] eq "multimod" || $_[0] eq "mod") { $in{'mh'} = 9999; } #3/4/2008 6/13/2009 mod so delete/mod will list all
$in{'mh'} ? ($maxhits = $in{'mh'}) : ($maxhits = $db_max_hits);
$in{'nh'} ? ($nh = $in{'nh'}) : ($nh = 1);
$numhits = 0;

# Now let's build up all the regexpressions we will use. This saves the program
# from having to recompile the same regular expression every time.
foreach $field (@search_fields) {
my $tmpreg = "$in{$db2_cols[$field]}";
(!$in{'re'}) and ($tmpreg = "\Q$tmpreg\E");
($in{'ww'}) and ($tmpreg = "\\b$tmpreg\\b");
(!$in{'cs'}) and ($tmpreg = "(?i)$tmpreg");
# ($in{$db2_cols[$field]} eq "*") and ($tmpreg = ".*"); # A "*" matches anything.
unless ($db_userid eq "default" && !$searchall) {
($in{$db2_cols[$field]} eq "*") and ($tmpreg = ".*"); # A "*" matches anything.
}
$regexp_func[$field] = eval 'sub { m/$tmpreg/o; }';
$regexp_bold[$field] = $tmpreg;
}

# Now we go through the database and do the actual searching.
# First figure out which records we want:
$first = ($maxhits * ($nh - 1));
$last = $first + $maxhits - 1;

open (DBX, "<$db2_file_name") or &cgierr("error in search. unable to open database: $db2_file_name.\nReason: $!");
if ($db_use_flock) { flock(DBX, 1); }

LINE: while (<DBX>) {
(/^#/) and next LINE; # Skip comment Lines.
(/^\s*$/) and next LINE; # Skip blank lines.
$line = $_; chomp ($line); # Remove trailing new line.
@values = &split_decode2($line);

# Normal searches.
$key_match = 0;
foreach $field (@search_fields) {
$_ = $values[$field]; # Reg function works on $_.
$in{'ma'} ?
($key_match = ($key_match or &{$regexp_func[$field]})) :
(&{$regexp_func[$field]} or next LINE);
}
# Greater then searches.
foreach $field (@search_gt_fields) {
$term = $in{"$db2_cols[$field]-gt"};
if ($db_sort{$db2_cols[$field]} eq "date") { # 12/20/2009 delicia
my ($dd1) = &date_to_unix($values[$field]);
my ($dd2) = &date_to_unix($term); --$dd2;
$in{'ma'} ? ($key_match = ($key_match or ($dd1 > $dd2))) : (($dd1 > $dd2) or next LINE);

}
elsif ($db_sort{$db_cols[$field]} eq 'alpha') {
$in{'ma'} ?
($key_match = ($key_match or ($values[$field] > $term))) :
((lc($values[$field]) gt lc($term)) or next LINE);
}
else {
$in{'ma'} ?
($key_match = ($key_match or ($values[$field] > $term))) :
(($values[$field] > $term) or next LINE);
}
}
# Less then searches.
foreach $field (@search_lt_fields) {
$term = $in{"$db2_cols[$field]-lt"};
if ($db_sort{$db2_cols[$field]} eq "date") { # 12/20/2009 delicia
my ($dd1) = &date_to_unix($values[$field]);
my ($dd2) = &date_to_unix($term); ++$dd2;
$in{'ma'} ? ($key_match = ($key_match or ($dd1 < $dd2))) : (($dd1 < $dd2) or next LINE);
}
elsif ($db_sort{$db2_cols[$field]} eq 'alpha') {
$in{'ma'} ?
($key_match = ($key_match or ($values[$field] < $term))) :
((lc($values[$field]) lt lc($term)) or next LINE);
}
else {
$in{'ma'} ?
($key_match = ($key_match or ($values[$field] < $term))) :
(($values[$field] < $term) or next LINE);
}
}
# Did we find a match? We only add the hit to the @hits array if we need it. We can
# skip it if we are not sorting and it's not in our first < > last range.
if ($key_match || (!($in{'keyword'}) && !($in{'ma'}))) {

##### hack for sorting on 3 fields
if (exists $in{'sb1'} && exists $in{'sb2'} && exists $in{'sb3'}) {
$sortby1{(($#hits+1) / ($#db2_cols+1))} = $values[$in{'sb1'}];
$sortby2{(($#hits+1) / ($#db2_cols+1))} = $values[$in{'sb2'}];
$sortby3{(($#hits+1) / ($#db2_cols+1))} = $values[$in{'sb3'}];
push (@hits, @values);
}
elsif (exists $in{'sb1'} && exists $in{'sb2'}) {
$sortby1{(($#hits+1) / ($#db2_cols+1))} = $values[$in{'sb1'}];
$sortby2{(($#hits+1) / ($#db2_cols+1))} = $values[$in{'sb2'}];
push (@hits, @values);
}
elsif (exists $in{'sb1'}) {
$sortby1{(($#hits+1) / ($#db2_cols+1))} = $values[$in{'sb1'}];
push (@hits, @values);
}
else {
(($numhits >= $first) and ($numhits <= $last)) and push (@hits, @values);
}
$numhits++; # But we always count it!
}
$numrecs++; #8/31/2008
}
close DBX;
# Now we've stored all our hits in @hits, and we've got a sorting values stored in %sortby indexed by their position in @hits.
$numhits ? ($db_total_hits = $numhits) : ($db_total_hits = 0); ($db_total_hits == 0) and return ("no matching records.$numrecs");
# Sort the array @hits in order if we are meant to sort.
if (exists $in{'sb1'}) {
# Sort hits on first field.
my ($sort_func, $tmp_func);
$sort_func = "";
$sort_pos = 1;
$sb_num = "sb" . "$sort_pos";
while (exists ($in{$sb_num})) {
$tmp_func = $sort_func eq "" ? "" : "$sort_func" . " || ";
$sort_func = $tmp_func . &build_sort_func2;
$sort_pos += 1;
$sb_num = "sb" . "$sort_pos";
}
$sort_func =~ tr/!/$/;
# Replace temporary characters with $
foreach $hit (sort { eval($sort_func); } (keys %sortby1)) {
$first = ($hit * $#db2_cols) + $hit;
$last = ($hit * $#db2_cols) + $#db2_cols + $hit;
push (@sortedhits, @hits[$first .. $last]);
}
@hits = @sortedhits;
}
#################

# If we have too many hits, let's build the next toolbar, and return only the hits we want.
if ($numhits > $maxhits) {
# Remove the nh= from the query string.
$next_url = $ENV{'QUERY_STRING'};
$next_url =~ s/\&nh=\d+//;
$next_hit = $nh + 1; $prev_hit = $nh - 1;

# Build the next hits toolbar. It seems really complicated as we have to do
# some number crunching to keep track of where we are on the toolbar, and so
# that the toolbar stays centred.

# First, set how many pages we have on the left and the right.
$left = $nh; $right = int($numhits/$maxhits) - $nh;
# Then work out what page number we can go above and below.
($left > 7) ? ($lower = $left - 7) : ($lower = 1);
($right > 7) ? ($upper = $nh + 7) : ($upper = int($numhits/$maxhits) + 1);
# Finally, adjust those page numbers if we are near an endpoint.
(7 - $nh >= 0) and ($upper = $upper + (8 - $nh));
($nh > ($numhits/$maxhits - 7)) and ($lower = $lower - ($nh - int($numhits/$maxhits - 7) - 1));
$db_next_hits = "";

# Then let's go through the pages and build the HTML.
($nh > 1) and ($db_next_hits .= qq~<a href="$db_script_url?$next_url&nh=$prev_hit">[<<]</a> ~);
for ($i = 1; $i <= int($numhits/$maxhits) + 1; $i++) {
if ($i < $lower) { $db_next_hits .= " ... "; $i = ($lower-1); next; }
if ($i > $upper) { $db_next_hits .= " ... "; last; }
($i == $nh) ?
($db_next_hits .= qq~$i ~) :
($db_next_hits .= qq~<a href="$db_script_url?$next_url&nh=$i">$i</a> ~);
if (($i * $maxhits) >= $numhits) { last; } # Special case if we hit exact.
}
$db_next_hits .= qq~<a href="$db_script_url?$next_url&nh=$next_hit">[>>]</a> ~ unless ($nh == $i);

$db_next_hits =~ s/&/&amp;/g; #8/30/2008 for w3c validation

# Slice the @hits to only return the ones we want, only have to do this if the results are sorted.
if (exists $in{'sb1'}) {
$first = ($maxhits * ($nh - 1)) * ($#db2_cols+1);
$last = $first + (($#db2_cols+1) * $maxhits) - 1;
$last = $#hits if ($last > $#hits);
@hits = @hits[$first .. $last];
}
}


return ("ok", @hits);
}

sub array_to_hash2 {
# --------------------------------------------------------
# Converts an array to a hash using db_cols as the field names.

my($hit, @array) = @_;
my(%hash);

for ($j = 0; $j <= $#db2_cols; $j++) {
$hash{$db2_cols[$j]} = $array[$hit * ($#db2_cols+1) + $j];
}
return %hash;
}

sub join_encode2 {
# --------------------------------------------------------
# Takes a hash (ususally from the form input) and builds one
# line to output into the database. It changes all occurrences
# of the database delimeter to '~~' and all newline chars to '``'.

my (%hash) = @_;
my ($tmp, $col, $output);

foreach $col (@db2_cols) {
$tmp = $hash{$col};
$tmp =~ s/^\s+//g; # Trim leading blanks...
$tmp =~ s/\s+$//g; # Trim trailing blanks...
$tmp =~ s/\Q$db2_delim\E/~~/og; # Change delimeter to ~~ symbol.
$tmp =~ s/\n/``/g; # Change newline to `` symbol.
$tmp =~ s/\r//g; # Remove Windows linefeed character.
$output .= $tmp . $db2_delim; # Build Output.
}
chop $output; # remove extra delimeter.
$output .= "\n"; # add linefeed char.
return $output;
}
that should get you started!
Quote Reply
Re: [delicia] Displaying data from one DB on the same pg as data from another DB. In reply to
Delicia, thank you I knew if anyone had the solution it would be youSmile.

after the install this is where i'm at. i'm getting this error:

CGI ERROR ========================================== Error Message : Cannot open ./notes.def. No such file or directory Script Location : db.cgi Perl Version : 5.008008 Setup File : default.cfg User ID : admin Session ID : admin.147109488975882
below is the code with the changes I made for my system. all of my changes are in Red.

I placed this in my "sub html_modify_form_record { "

##### Start Notes


&switch_to_notes("notes");
my ($db2_key_pos) = 3; ###
my (%save_in) = %in;
undef %in;
$in{'Company'} = $rec{'Company'};
$in{'sb1'} = 5;
$in{'so1'} = 'ascend';
$in{'mh'} = 100;
my ($sort_type2) = 'numer';

my ($status2,@hits2) = &query2("view");

if ($status2 eq "ok") {
my ($numhits2) = ($#hits2+1) / ($#db2_cols+1);
print qq|<table width="640"><tr><td valign="top"><strong>Notes:</strong></td><td align="left" width="150" valign="top">|;

for (0 .. $numhits2 - 1) {
%rec2 = &array_to_hash2($_, @hits2);
print qq|<tr><td valign="top" width="25">$rec2{'Date'}</td>
<td valign="top" width="5">$rec2{'Time'} $rec2{'Company'}|;
if ($rec2{'Contact'}) { print qq|, $rec2{'Notes'}|; }
print qq|</td></tr>|;
}
print qq|</table>|;
$delete= 1;
}
else {
print qq|<table><tr><td>No Notes. </td></tr></table>|;
}
##### End Notes

This code went in my "db.cgi". I'm only showing the code I made change too, the rest is the same as you sent with no change.

#######################################################
### Here's the code for the relational ###
#######################################################


sub switch_to_notes {
#-----------------------------------------------------
undef @db2_cols;
$configfile = "$_[0].def";
&get_fieldnames2($configfile);

$db2_file_name = $db_script_path . "/../db/notes.db"; # This is the path to my db
$db2_key_pos = 0;
$db2_delim = '|'; # 3/17/2009
}

sub get_fieldnames2 {
#-----------------------------------------------------
# pulls field names from the def file!
# check def file to be sure field names enclosed in single quotes!

#$configfile = "$_[0].def";

$configfile = $db_script_path . "/" . $configfile;
open(FILE, "<$configfile") || &cgierr("Cannot open $configfile.\n$!");
local $/;
my ($fields);
while (<FILE>) { if (/(%db_def\s+=\s+\(\s+)('.*?)(\);)/s) { $fields = $2;}}
close(FILE);

my @split = split /\n/, $fields;
foreach (@split) {
if ($_ =~ /'(\w+)'/) { push @db2_cols, $1; }} # This } was missing so I added it.
}

#######################################################
### End of the code for the relational ###
#######################################################


I think it's a path problem, but I cant find it.

Thanks, Ed-
Quote Reply
Re: [knue] Displaying data from one DB on the same pg as data from another DB. In reply to
i'm pretty sure it's a path problem. try removing the /../
if that doesn't work let me know what the script path is and the full path to the notes db.
i keep all my databases in the same folder -- makes it much easier to reference them!
Quote Reply
Re: [delicia] Displaying data from one DB on the same pg as data from another DB. In reply to
oops. i meant take off /.. you need the first slash unless you changed the normal syntax for the script path. also it just occurred to me -- can you just put in the path that you use in the notes.cfg file? if it uses a different script path, you'll need to put the complete path in your switch code.
Quote Reply
Re: [delicia] Displaying data from one DB on the same pg as data from another DB. In reply to
sorry no luck.

this is how my dir's are setup.

cgi-bin/

db/ (all data bases are in here)
lead/ (cgi, config, html.pl and auth.pl in here)
notes/ (cgi, config, html.pl and auth.pl in here)
dbads/ (cgi, config, html.pl and auth.pl in here) this is the main dir.
all other dir are setup the same.

this is how the config's are setup:


###Code###

# File and URL's
# --------------------------------------------------------
# URL of the directory dbman resides in. No Trailing Slash Please.
$db_dir_url = $db_script_www . "/cgi-bin/lead";
# URL of dbman.
$db_script_url = $db_dir_url . "/db.cgi";
# URL of website. No Trailing Slash Please.
# $db_script_www = "http://ase.4mcl.com"; # $db_script_www points to dbads from db.cgi in main sub around line 67. I did this so only dbads config needs the path changed.
# Full Path and File name of the database file.
$db_file_name = $db_script_path . "/../db/lead.db";
# Full path and file name of the counter file.
$db_id_file_name = $db_script_path . "/default.count";
# Full path and file name of the authorization directory.
$auth_dir = $db_script_path . "/../db/auth";
# Full path and file name of the password file.
$auth_pw_file = $db_script_path . "/../db/default.pass";
# Full path and file name of the log file.
$auth_log_file = $db_script_path . "/../db/default.log";
# Full path and file name of the html routines.
require $db_script_path . "/html.pl";
# Call external db "Userid".
$db_external_select_fields = 'Userid';


###End of code###




http://ase.4mcl.com/

Cannot open ./notes.def.


Thanks, Ed
Quote Reply
Re: [knue] Displaying data from one DB on the same pg as data from another DB. In reply to
not sure i understand

Code:
cgi-bin/

db/ (all data bases are in here)
lead/ (cgi, config, html.pl and auth.pl in here)
notes/ (cgi, config, html.pl and auth.pl in here)
dbads/ (cgi, config, html.pl and auth.pl in here) this is the main dir.
the above folders (db, lead, notes, dbads, are all directly off the cgi-bin folder?
does the notes.cfg contain the following?
Code:
# Full Path and File name of the database file.
$db_file_name = $db_script_path . "/../db/notes.db";
it appears you have a different db.cgi for each db. is $db_script_path defined in each db.cgi? is the $db_script_path the same in all of them (or at least in notes and lead)?
Quote Reply
Re: [delicia] Displaying data from one DB on the same pg as data from another DB. In reply to
i think i found it! sub get_fieldnames2

$configfile = $db_script_path . "/" . $configfile;

you need to correct the path there too to agree with your set up!

Post deleted by knue In reply to

Last edited by:

knue: Aug 13, 2016, 6:58 PM
Quote Reply
Re: [delicia] Displaying data from one DB on the same pg as data from another DB. In reply to
 
Code:
cgi-bin/

db/ (all data bases are in here)
lead/ (cgi, config, html.pl and auth.pl in here)
notes/ (cgi, config, html.pl and auth.pl in here)
dbads/ (cgi, config, html.pl and auth.pl in here) this is the main dir.

the above folders (db, lead, notes, dbads, are all directly off the cgi-bin folder? YES
does the notes.cfg contain the following? YES
Code:
# Full Path and File name of the database file.
$db_file_name = $db_script_path . "/../db/notes.db";

it appears you have a different db.cgi for each db. is $db_script_path defined in each db.cgi? YES.
is the $db_script_path the same in all of them (or at least in notes and lead)? YES
Quote Reply
Re: [delicia] Displaying data from one DB on the same pg as data from another DB. In reply to
Here are the changes I made to your code.




#####Code#####


sub switch_to_notes {
#-----------------------------------------------------
undef @db2_cols;
$configfile = "$_[0].def";
&get_fieldnames2($configfile);

$db2_file_name = $db_script_path . "/../db/notes.db"; # path to my db.
$db2_key_pos = 0;
$db2_delim = '|'; # 3/17/2009
}

sub get_fieldnames2 {
#-----------------------------------------------------
# pulls field names from the def file!
# check def file to be sure field names enclosed in single quotes!

$configfile = "default.cfg"; # I know i should not change this but when i did the error went away

$configfile = $db_script_path . "/../notes/" . $configfile; # This is the path to the Notes dir.
open(FILE, "<$configfile") || &cgierr("Cannot open $configfile.\n$!");
local $/;
my ($fields);
while (<FILE>) { if (/(%db_def\s+=\s+\(\s+)('.*?)(\);)/s) { $fields = $2;}}
close(FILE);

my @split = split /\n/, $fields;
foreach (@split) {
if ($_ =~ /'(\w+)'/) { push @db2_cols, $1; }}
}



#####End of code#####




When i run the script the results come back with "No Notes". that is what it should come back with, but I do have notes for this company.[Your user name and password is still active if you want to login. once you login go to the drop down menu pick "New Leads", pick the 1st. record then enter, on the next screen under the table the notes should appear, instead the "No Notes" show.


Thank you,

Last edited by:

knue: Aug 13, 2016, 7:56 PM
Quote Reply
Re: [knue] Displaying data from one DB on the same pg as data from another DB. In reply to
i forgot that i had taken the fieldnames out of cfg and put in a separate def file. take out the call to get_fieldnames and use the following code instead (substituting your fieldnames instead of mine!)
Code:

$cols2 = 'Lock,RecordID,Venue_name,Exhibit_title,Exhibit_open,Exhibit_close,Display,Type,Address,City,State,Zip,Phone,Comment,Updated,Reserved';
@db2_cols = split /,/,$cols2;


this is the line to take out:
Code:
&get_fieldnames2($configfile);

Last edited by:

delicia: Aug 13, 2016, 10:34 PM
Quote Reply
Re: [delicia] Displaying data from one DB on the same pg as data from another DB. In reply to
one more thing

$db2_key_pos = 0;

should be the position of your Company field

Quote Reply
Re: [delicia] Displaying data from one DB on the same pg as data from another DB. In reply to
Delicia,

I made the changes:

################
Code
################

sub switch_to_notes {
#-----------------------------------------------------
undef @db2_cols;
$configfile = "$_[0].def";
$cols2 = 'Lock,ID,Date,Time,Userid,Quick_notes,Company,Contact,Callback,Notes';
@db2_cols = split /,/,$cols2;

$db2_file_name = $db_script_path . "/../db/notes.db";
$db2_key_pos = 5;
$db2_delim = '|'; # 3/17/2009
}

sub get_fieldnames2 {
#-----------------------------------------------------
# pulls field names from the def file!
# check def file to be sure field names enclosed in single quotes!

$configfile = "$_[0].def"; # I put this back to the way it was.

$configfile = $db_script_path . "/../notes/" . $configfile;
open(FILE, "<$configfile") || &cgierr("Cannot open $configfile.\n$!");
local $/;
my ($fields);
while (<FILE>) { if (/(%db_def\s+=\s+\(\s+)('.*?)(\);)/s) { $fields = $2;}}
close(FILE);

my @split = split /\n/, $fields;
foreach (@split) {
if ($_ =~ /'(\w+)'/) { push @db2_cols, $1; }}
}

################
End
################

I think this might be a problem as well:

################
Code html.pl
################

for (0 .. $numhits2 - 1) {
%rec2 = &array_to_hash2($_, @hits2);
print qq|<tr><td valign="top" width="25">$rec2{'Date'}</td>
<td valign="top" width="5">$rec2{'Time'} $rec2{'Company'}|;
if ($rec2{'Contact'}) { print qq|, $rec2{'Notes'}|; } # I did not notice this was and if statment.
print qq|</td></tr>|;
}
print qq|</table>|;
$delete= 1;
}
else {
print qq|<table><tr><td>No Notes. </td></tr></table>|; # this is what prints to the screen.
}


print qq|


################
End
################


Thank you, Ed-
Quote Reply
Re: [knue] Displaying data from one DB on the same pg as data from another DB. In reply to
Code:

$cols2 = 'Lock,ID,Date,Time,Userid,Quick_notes,Company,Contact,Callback,Notes';
@db2_cols = split /,/,$cols2;

$db2_file_name = $db_script_path . "/../db/notes.db";
$db2_key_pos = 5;


are the fieldnames correct above? they must be in same order they appear in your cfg file. Company is 6, not 5 in that list.
in case contact is blank, change:
Code:
if ($rec2{'Contact'}) { print qq|, $rec2{'Notes'}|; } # I did not notice this was and if statment.
to

Code:
print qq|, $rec2{'Notes'}|; # I did not notice this was and if statment.
you probably don't need the sort by and sort type statements either, or your sort by would be by date?
going to play golf now!

Last edited by:

delicia: Aug 14, 2016, 8:46 AM
Quote Reply
Re: [delicia] Displaying data from one DB on the same pg as data from another DB. In reply to
When I made that change I got this error:

Error Message : fatal error: Undefined subroutine &main::build_sort_func2 called at db.cgi line 653.

This is what's at line 653.

####### Code ############

# Sort hits on first field.


my ($sort_func, $tmp_func);
$sort_func = "";
$sort_pos = 1;
$sb_num = "sb" . "$sort_pos";
while (exists ($in{$sb_num})) {
$tmp_func = $sort_func eq "" ? "" : "$sort_func" . " || ";
$sort_func = $tmp_func . &build_sort_func2; # line 653.
$sort_pos += 1;
$sb_num = "sb" . "$sort_pos";
}
$sort_func =~ tr/!/$/;

######### End #########

This is the change that I made. It also has the blank fields not used yet.

$cols2 ='ID,Date,Time,Userid,Quick_notes,Company,Contact,Blank11,Blank10,Blank9,Blank8,Callback,Notes,Blank7,Blank6,Blank5,Blank4,Blank3,Blank2,Blank1';

Thanks, Ed-

Last edited by:

knue: Aug 14, 2016, 9:11 AM
Quote Reply
Re: [knue] Displaying data from one DB on the same pg as data from another DB. In reply to
This is the code in db.cgi:

###code###

sub switch_to_notes {
#-----------------------------------------------------
undef @db2_cols;
$configfile = "$_[0].def";
$cols2 = 'ID,Date,Time,Userid,Quick_notes,Company,Contact,Blank11,Blank10,Blank9,Blank8,Callback,Notes,Blank7,Blank6,Blank5,Blank4,Blank3,Blank2,Blank1';
@db2_cols = split /,/,$cols2;

$db2_file_name = $db_script_path . "/../db/notes.db";
$db2_key_pos = 5;
$db2_delim = '|'; # 3/17/2009
}

sub get_fieldnames2 {
#-----------------------------------------------------
# pulls field names from the def file!
# check def file to be sure field names enclosed in single quotes!

# $configfile = "$_[0].def";

$configfile = $db_script_path . "/../notes/" . $configfile;
open(FILE, "<$configfile") || &cgierr("Cannot open $configfile.\n$!");
local $/;
my ($fields);
while (<FILE>) { if (/(%db_def\s+=\s+\(\s+)('.*?)(\);)/s) { $fields = $2;}}
close(FILE);

my @split = split /\n/, $fields;
foreach (@split) {
if ($_ =~ /'(\w+)'/) { push @db2_cols, $1; }}
}

###end###


And this is the code in the html.pl:

###code###

&switch_to_notes("notes");
my ($db2_key_pos) = 5; ###
my (%save_in) = %in;
undef %in;
$in{'Company'} = $rec{'Company'};
$in{'so1'} = 'ascend';
$in{'mh'} = 32;

my ($status2,@hits2) = &query2("view");

if ($status2 eq "ok") {
my ($numhits2) = ($#hits2+1) / ($#db2_cols+1);
print qq|<table width=1000><tr><td valign="top"><strong>Notes:</strong></td><td align="left" width="150" valign="top">|;

for (0 .. $numhits2 - 1) {
%rec2 = &array_to_hash2($_, @hits2);
print qq|<tr><td valign="top" width="25">$rec2{'Date'}</td>
<td valign="top" width="5">$rec2{'Time'} $rec2{'Company'}|;
print qq|, $rec2{'Notes'}|;
print qq|</td></tr>|;
}
print qq|</table>|;
$delete= 1;
}
else {
print qq|<table border=1 bgcolor="#FFFFFF" cellpadding=5 cellspacing=3 width=1000 align=center valign=top><tr><td>No Notes.</td></tr></table>|;
}

###end###

No errors at this point. Page still displays "No Notes".

Getting closer :-)

Ed-

Last edited by:

knue: Aug 14, 2016, 10:05 AM