Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 1 of 1
  1. #1
    New to the CF scene
    Join Date
    Aug 2009
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Building a Table from multiple databases...

    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:
    Code:
    #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)
    Last edited by starburst; 04-15-2010 at 07:26 PM. Reason: to clarify more


 

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •