Retrieve data from different mysql databases?

[robj]

I have customer, whom I created 2 private stores at 2 different domains. They use the same shopping cart software. They database structures are the same, and databases reside on the same server.

They now want to be able to view orders from both stores in 1 interface (instead of having to log into d1, logout, then log into d2).

Is is possible to:
A. retrieve data from D1 and D2 to be displayed within 1 php web interface?
B. sync 2 databases on the same server to a 3rd, central db, which could then be accessed for data.

I'm not trying to sync or retrieve the entire database, just 1 table and it's content. Can anyone help with this?

I may be able to hire you to build this interface, if my customer commits to the project.

thanks
rob

 

[nobaloney]

DirectAdmin won't care; the key is that you'll have to write the code, and the code will have to know the logins and usernames for the MySQL databases.

Jeff

 

[robj]

Actually, that's pretty much what i'm asking. By D1, I mean Database 1.

I do not know how to write the php code to do this. Know any good resources?

rob

 

[Randy]

<?php
mysql_connect ("db1.server.tld", "user", "pass");
mysql_select_db ("databasename");
$sql1 = "SELECT `id`, `name`, `data` FROM `table` ORDER BY desc LIMIT 30"
$query1 = mysql_query($sql1) or die(mysql_error());
$result1 = mysql_fetch_object($query1);
mysql_close;

mysql_connect ("db2.server.tld", "user", "pass");
mysql_select_db ("databasename");
$sql2 = "SELECT `id`, `name`, `data` FROM `table` ORDER BY desc LIMIT 30"
$query2 = mysql_query($sql2) or die(mysql_error());
$result2 = mysql_fetch_object($query2);
mysql_close;

echo $result1->id;
echo $result2->name;
?>

To sync all data from db1 and db2 to a third database, use replication. It's included in MySQL5. For howto's check www.howtoforge.net.

 

[robj]

Thanks Randy,

Found the replication tutorial. http://www.howtoforge.net/mysql_database_replication


I'll try to work with multiple databases and tackle the replication on my test server first. Do you know if you have a third (db3)?

[Edit]
Actually, If I only wanted to sync 1 table from one server to another wouldn't the above code do that easier?