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…

Hit Like if this article was useful to you!

Average rating / 5. Vote count:

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

2 thoughts on “PHP code to connect multiple MySQL server,multiple database simultaneously”

    1. Hey Juan, totally get that, but that’s an old post that i have imported to my new blog here. Hence still, MySQL. But, let me change that. Thanks for pointing out.

Leave a Reply

Your email address will not be published. Required fields are marked *