Query database from two different server using php

[shaqib]

Dear all,

I have two identical mysql server with same databases and tables structures but with different data.

ServerA                                             ServerB
Database Shaqib                                     Database Shaqib
Table users                                         Table users
ID | Name | Phone                                   ID | Name | Phone
1  |Test1 | 123456                                  1  |Test13 | 1256456
2  |Test2 | 785632                                  2  |Test14 | 8621038
3  |Test3 | 985632                                  3  |Test15 | 7841308

I want to query both tables and out all the data in a single xml file.

Below is my database connection and query

$host1 = "10.0.1.1";
$user1 = "test";
$pass1 = "test";
$database1 = "shaqib";

$linkID1 = mysql_connect($host1, $user1, $pass1) or die("Could not connect to host.");
mysql_select_db($database1, $linkID1) or die("Could not find database.");

$query1 = "SELECT phone, name FROM users ORDER BY name ASC";
//$resultID1 = mysql_query($query1, $linkID1) or die("Data not found.");

//Margarine settings
$host2 = "10.0.1.2";
$user2 = "test";
$pass2 = "test";
$database2 = "shaqib";

$linkID2 = mysql_connect($host2, $user2, $pass2) or die("Could not connect to host.");
mysql_select_db($database2, $linkID2) or die("Could not find database.");

$query2 = "SELECT phone, name FROM users ORDER BY name ASC";
//$resultID2 = mysql_query($query2, $linkID2) or die("Data not found.");

//Merge both phonebook
$query =  $query1." ".UNION." ".$query2;
$resultID = mysql_query($query);

$xml_output = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n";
$xml_output .= "<AddressBook>\n";
$xml_output .= "<version>1</version>\n";
for ($x = 0; $x < mysql_num_rows($resultID); $x++) {
    $row = mysql_fetch_assoc($resultID);

when i run this query, i got a blank xml file..
Can someone help me.

[shaqib]

Edit

[cereal]

There are two problems here:

$query =  $query1." ".UNION." ".$query2;
$resultID = mysql_query($query);

First: UNION will be evaluated by PHP as a constant, not as a string part of a query, the correct syntax would be:

$query = "$query1 UNION $query2";

Second: the last query will connect to the last available connection, i.e. to $link2 and so it will return values only from the last database. You could loop the results you got from the queries (1 and 2) into an array, through PHP:

$results = array();
$i = 0;

while($row = $result1)
{
    $results[$i]['phone'] = $row['phone'];
    $results[$i]['name'] = $row['name'];
    $i++;
}

while($row = $result2)
{
    $results[$i]['phone'] = $row['phone'];
    $results[$i]['name'] = $row['name'];
    $i++;
}

by using PDO or MySQLi it would be even simplier because you can get the full result set and simply use an array_merge() which would be faster than the loops.

Or use federated tables: in this case one of the databases connects the other and reads the data as it would be in local, that way your current code would work fine:

• https://dev.mysql.com/doc/refman/5.0/en/federated-use.html

[shaqib]

thanks. will try your suggestion and revert back. ;)