MySQL UNION on 2 databases on 2 servers

Is it possible?!

[surfgatinho]

Hi,

I want to be able to pull combined results from 2 different databases on 2 different servers.

I know it's easy enough doing this on 1 server using database.table.field notation, but haven't managed to get anywhere with 2 connections.

Does anyone know if this is even possible.

Thanks,
Chris

[ZydoSEO]

Can do it in SQL Server. If each DB server has a link server name you can select from linkservername1.database1.table1 and union he results with linkservername2.database2.table2.

[ZydoSEO]

Can do it in SQL Server. If each DB server has a link server name you can select from linkservername1.database1.table1 and union he results with linkservername2.database2.table2.

[surfgatinho]

OK, here's what I'm using:

$link = mysql_connect('localhost', 'root', '');

$link2 = mysql_connect('REMOTE IP', 'remote_user', 'password');
mysql_select_db('remote_db', $link2);

$query = "SELECT local_db.coords.name FROM local_db.coords
UNION
SELECT coords.name FROM coords";
$result = mysql_query($query) or die("Query failed : $query " . mysql_error());
$line = mysql_fetch_array($result);

The error I'm getting is:

SELECT command denied to user 'remote_user'@'MY_IP_ADDRESS' for table 'coords'

There shouldn't be a problem with SELECT privilege as I can get onto this DB with a local copy of PHPMyAdmin no problem