PHP code to connect multiple MySQL server,multiple database simultaneously

PHP code to connect multiple MySQL server multiple database simultaneously
Caution: Audio may begin with Music!!

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…
Click to rate this post!
[Total: 2 Average: 5]
Shares

Recommended Articles

4 Comments

  1. Using mysql_* instead of mysqli_* in 2019? really?

    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.

  2. Great post. I used to be checking continuously this weblog and I’m inspired!

    Very helpful information specifically the
    final part 🙂 I deal with such info a lot.
    I used to be looking for this certain info for a very long time.
    Thanks and good luck.

  3. hi!,I like your writing so so much! proportion we keep in touch more approximately your article on AOL?
    I require a specialist on this house to unravel my
    problem. Maybe that is you! Looking ahead to peer you.

Leave a Reply

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

Pin It on Pinterest

Shares