About Me

My photo

one who looks outside, dreams
one who looks inside, awakens!!

Get FREE updates!! subscribe!!

Delivered by FeedBurner

Like it? Follow up!! :)

Oct 12, 2013

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 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!! :)