...

View Full Version : Building a Table from multiple databases...



starburst
04-15-2010, 07:50 PM
Hi. I am trying to build a two column table, each column from a different database. (The actual functionality is that a user can search for other users and see what systems they are associated with. I want one column to be 'Users' and the other to be 'Systems'. My first database contains just users and ID numbers...the second has systems and their details [including their unique ID]. That is the common key between the two)

Here is the code for my query and printing:


#Search the database if search was pressed
if ( defined $form{cmd} && $form{cmd} eq "Search" ) {
$sqlcmd = "SELECT * from `$Database`.`$AccessTable` WHERE ";
$div = "";
$ky = $form{SearchFor};
$pattern = SafeStr($form{SearchQuery});

#Get the type of what we are searching for and search differently for different types
foreach $member (@tblentries) {
$mname = $member->{"member"};
$mtype = $member->{"type"};
if ($ky eq $mname){
if ($mtype eq "int"){
$pattern = "\%" if ($pattern eq "");
$sqlcmd .= "$div" . "`$ky` LIKE '$pattern'";
$div = " AND ";
}else{ #assume some sort of string
# The lower makes it case insensitive, the % are wildcards
$sqlcmd .= "$div" . "lower(`$ky`) LIKE lower(\'\%$pattern\%\')";
$div = " AND ";
}
}
}

#evaluate the SQL cmd
eval {
$sth = $dbh->prepare($sqlcmd);
$nr = $sth->execute();
};

if ( $@ ) {
# SQL Error
print STDERR "Search resulted in an SQL error.<p>\n$sqlcmd\n";
print "Search resulted in an error.\n";
}
elsif ( $nr == 0 ) {
print "No match.\n";
}
else {
my $systemKey = GetSystem();

# Display content of search
print "<table width=\"100%\" cellpadding=\"2px\" cellspacing=\"1px\" border=\"0\">\n";
print begintr($hd_clr, $hd_ht, "$fstyle cursor:pointer; ", "");


#Go through and print out these fields in a table
foreach $member (@HeadingsOrdered) {
print " <th id=\"$member\" onclick=\"headingsSort('$member');\">$member</th>\n";
}
print " </tr>\n";
}



In the line that says "my $systemKey" I get the key value that I want to use in order to reference the second database. Is there some way that I can build/print a table with the entries I had searched for in one column with the associated system from the second different database table using the $systemKey (ID number)?

(PS I eliminated some of the code to simplify it for my question...the HeadingsOrdered simply allows you to change how to order the displayed results...either ascending or descending order)



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum