PHP code to connect multiple MySQL server,multiple database simultaneously

Have you ever tried to connect two different servers and tried to query MySQL database located in each server to produce a single result from a single query?. If yes, probably you would be disappointed as much as we were. As far as we know, this cannot be achieved in one shot!. Therefore, we would walk you in this post, on, how to connect two MySQL databases on multiple servers using a different approach.

• You must always have a two-step approach for this.
• open connection to both the servers from PHP script
• query first server with first connection. close the connection.
• query second server with the second server. close the connection.
• fetch the resultant rows
• merge the resultant array using array_merge().

The following PHP code would help you understand completely how it’s done.

<?php
  
  $dbh1 = mysqli_connect('server1', 'username', 'password')or die("Unable to connect to MySQL1"); 
  $dbh2 = mysqli_connect('server2', 'username', 'password') or die("Unable to connect to MySQL2");
  
  mysqli_select_db($dbh1 , 'db1');
  mysqli_select_db($dbh2 , 'db2');
  $ar = array();$ar2 = array();
  
  $qry1 = mysqli_query($dbh1, "select * from db1.table1 where db1.table1.id='261' and db1.table1.id=db1.table1.id",$dbh1) or die(mysqli_error($dbh1));
  while($row = mysqli_fetch_array($qry1))
  {
        $ar[] = $row;
  }
  
  $qry2 = mysqli_query($dbh2, "select * from db2.table1 where db2.table1.id='421' and db2.table1.id=db2.table1.id",$dbh2) or die(mysqli_error($dbh2));
  while($row2 = mysqli_fetch_array($qry2))
  {
        $ar2[] = $row2;
  }
  
  $result = array_merge($ar, $ar2);
  
  // using for loop to retrieve data
  for($i=0;$i<count($result);$i++)
  {
         echo $result[$i]['realname'].'<br>';
  } 
  
  // using for each loop to retrieve data
  foreach($result as $k=>$val)
  {
        echo $val['realname']. '<br>'; 
  } 
  
  // using while loop to retrieve data
  while(list($key, $value) = each($result)){
     echo $value['realname']. '<br>';
  }
  
?>

Disadvantage:

• Of course, if you are using a highly complex query this is not the preferred approach, since, you need more execution and just because of code complexity itself.
• The resultant array of the result returned would contain duplicate values and you have to sort it with a different approach using array sort etc…