Jump to content
Sign in to follow this  
akallenberger

Two mysql queries from two databases on two different servers

Recommended Posts

I am having trouble sorting and displaying the data from two different databases on two different servers.  They both contain and id field that have matching id's.  They just happen to reside on different servers.  I need to display all the information from both databases based on their id and display it in a table sorted on id.  I've gotten to the point where I can get all the data I need from both tables, but I can't line them up in the table.  The code below starts to put everything in line, but as soon as the loop hits a spot where the id's don't match up, it stops and starts outputting sorry in the else statement.  I need it to pull everything from the first database and then pull from the second database and match all the id's in the first database.  Any help is greatly appreciated.


      $conn = dbfunction1();   
      
      mysql_select_db('db1');
      
      $query = "SELECT * from table1";
                       
      $results = mysql_query($query);   
      
      $conn1 = dbfunction2();
            
      mysql_select_db('db2');
      
      $query1 = "SELECT * from table2;
";
                       
         $results1 = mysql_query($query1);
      
      
         while (($row = mysql_fetch_array($results, MYSQL_ASSOC)) && ($row1 = mysql_fetch_array($results1, MYSQL_ASSOC))) {
            
            $stid = $row['id'] ;
            $cstid = $row1['building_id'];
            
            if ($stid == $cstid){
            
         echo "<div class='Row'>";   
            //results from query 2
            echo "<div class='Cell'>"."<p>".$row['id']."</p>"."</div>";
            echo "<div class='Cell'>"."<p>".$row['code']."</p>"."</div>";
            echo "<div class='Cell'>"."<p>".$row['market']."</p>"."</div>";
            echo "<div class='Cell'>"."<p>".$row['genre']."</p>"."</div>";
            //results from query 1
            echo "<div class='Cell'>"."<p>".$row1['building_id']."</p>"."</div>";
            echo "<div class='Cell'>"."<p>".$row1['callsign']."</p>"."</div>";
            echo "<div class='Cell'>"."<p>".$row1['session']."</p>"."</div>";

            
            }
            
            else {
               
               echo "sorry!";
            }

         echo "</div>";


         
         } //end while

Share this post


Link to post
Share on other sites

you would need to retrieve the data from each query, separately, storing it in a single array, using the id as the array index/key. this will combine the related data together for each id.

 

then just loop over the resulting array of data and display the information the way you want.

Share this post


Link to post
Share on other sites

I've been trying but not very successful.  Not 100% sure what i'm doing with arrays.  Here's what i'v tried next.

$results1 = mysql_query($query1);	

$query_1 = array(); 
while($row_1 = mysql_fetch_array($results))    
{    
    $query_1[$row_1["id"]] = $row_1["code"].", ".$row_1["market"]; 
}
$query_2 = array(); 
while($row_2 = mysql_fetch_array($results1))    
{    
    $query_2[$row_2["building_id"]] = $row_2["callsign"].", ".$row_2["session"]; 
}  

$combined = array_merge($query_1,$query_2); 
asort($combined);

foreach($combined as $key=>$val){
	echo "<div class='Row'>";	
	echo "<div class='Cell'>"."<p>".$val."</p>"."</div>";
	echo "</div>";
}
			
		

?>	

Share this post


Link to post
Share on other sites

ok now i have it all in an array together.  but now i need it to pull all the results from the two arrays based on the matching id's from the two arrays. Here's what I have and this spits out what I ask for but not together.  it will give me the results from the first query and then give me the results from the second query seperately.  im stuck on this part:  using the id as the array index/key

$results1 = mysql_query($query1);	

$data = array();

while($row = mysql_fetch_array($results))    
	
{
 $data[] = $row;

}

while($row = mysql_fetch_array($results1))   

{
 $data[] = $row;
}	

//print_r($data);

foreach($data as $row){
	echo "<div class='Row'>";
        //from query 1	
	echo "<div class='Cell'>"."<p>".$row['id']."</p>"."</div>";
	echo "<div class='Cell'>"."<p>".$row['code']."</p>"."</div>";
	//from query 2
        echo "<div class='Cell'>"."<p>".$row['building_id']."</p>"."</div>";
	echo "<div class='Cell'>"."<p>".$row['callsign']."</p>"."</div>";
echo "</div>";
}

		
		

?>	

Share this post


Link to post
Share on other sites

try this for the code that combines the arrays -

$data = array();
while($row = mysql_fetch_assoc($results))    
{
    $data[$row['id']] = $row;
}
while($row = mysql_fetch_assoc($results1))   
{
    $data[$row['building_id']] = array_merge(isset($data[$row['building_id']])? $data[$row['building_id']]:array(),$row);
}

you will also what to sort the final $data array on the key using ksort()

Edited by mac_gyver

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.