Two mysql queries from two databases on two different servers

[akallenberger]

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

[mac_gyver]

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.

[akallenberger]

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>";
}
			
		

?>	

[akallenberger]

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>";
}

		
		

?>	

[mac_gyver]

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 July 24, 2015 by mac_gyver

[mikosiko]

In case you are willing to explore the alternative Mysql also provide the FEDERATE storage engine to solve your scenario

https://dev.mysql.com/doc/refman/5.5/en/federated-storage-engine.html