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

04-15-2010, 06: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)