PHP code to connect multiple MySQL server,multiple database simultaneously

Tags: MySQL , PHP 4
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 i was. As far as i know, this cannot be achieved in one shot!.
Therefore, i would walk you in this post, on,  how to achieve the same using a different approach.
  • You must always have 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 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 its done.
<?php
  
  $dbh1 = mysql_connect('server1', 'username', 'password')or die("Unable to connect to MySQL1"); 
  $dbh2 = mysql_connect('server2', 'username', 'password') or die("Unable to connect to MySQL2");
  
  mysql_select_db('db1', $dbh1);
  mysql_select_db('db2', $dbh2);
  $ar = array();$ar2 = array();
  
  $qry1 = mysql_query("select * from db1.table1 where db1.table1.id='261' and db1.table1.id=db1.table1.id",$dbh1) or die(mysql_error());
  while($row = mysql_fetch_array($qry1))
  {
        $ar[] = $row;
  }
  
  $qry2 = mysql_query("select * from db2.table1 where db2.table1.id='421' and db2.table1.id=db2.table1.id",$dbh2) or die(mysql_error());
  while($row2 = mysql_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 result returned would contain duplicate values and you have to sort it with a different approach using array sort etc…

Please subscribe for more cool codes 🙂

Thats all guys feedbacks are welcome.

Check out the my other links for other cool stuffs!! 🙂
Code Snippets

Helpful articles:

Join Discussion

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

All code will be displayed literally.

Discussion

  • AFAIK that is because php was not originally built to take OOP, this fact could have affected their decision on multiple db connection.

    Reply

  • This has got to be the worst feature of PHP I have ever encountered, and by far the worst decision that PHP has made. What is so hard in allowing a single SQL query to join multiple databases from multiple servers instead of double handling everything. The database connections are open until closed or the code finishes, I cannot understand the reasoning other than stupidity. PHP use the excuse of enforcing standards for removing the feature (a single application should only have one database), stupidest argument I have ever heard, I am pretty sure us coders can make the choice by ourselves.

    Reply