join between two databases
Date: 03/18/07
(PHP Community) Keywords: mysql, browser, database, sql
this might be way too much info, but please bear with me! the end result is, i need to do a query that joins tables that exist in different databases on different servers. how do i do that?
anyway, i'm building a site for a real estate guy who's hosted through 1and1. they only allow you to have 100mb of data per database. he's given me ~500mb of property data and ~100mb of property tax data. i split up the property data and kind of spanned it across five db's, then put the tax data in a sixth. each database you create with 1and1 gets dropped on a random server. i'm able to search property data by checking db1, then db2, and so on... and appending the results. i connect to them all like so
//connect to property records 1 db
$props1_link = mysql_connect('some_server1','name','pwd') or die ("failed to connect to server 1.");
$props1_db = mysql_select_db('db_name1',$props1_link) or die("unable to select property records 1 database: ".mysql_error());
//connect to property records 2 db
$props2_link = mysql_connect('some_server2','name','pwd') or die ("failed to connect to server 2.");
$props2_db = mysql_select_db('db_name2',$props2_link) or die("unable to select property records 2 database: ".mysql_error());
//connect to taxes db
$tax_link = mysql_connect('some_server','name','pwd') or die ("failed to connect to taxes server.");
$tax_db = mysql_select_db('tax_db',$tax_link) or die("unable to select tax database: ".mysql_error());
i have one database containing one table on each of the six servers.
i perform a search by constructing the query and looping through the $props... connections, doing this for each
$res = mysql_query($sql,$props1_link) or die($sql.": ".mysql_error());
i dump the results to a csv file, then move on to the next db, append those results, move on, etc., until we have one big csv which i pass to the browser. that all works fine if i only need to query properties.
my problem is that some of his queries need to join the properties table/s with the tax records table. simplified, something like
SELECT p.`name` FROM `properties` AS `p`, `taxes` AS `t` WHERE p.`tax_id` = t.`id`
i've done things like this before with multiple db's on the same server; but here i need to use multiple $links to multiple servers in order perform the query. is that possible? (beyond doing multiple queries to the different db's than comparing results manually.)
Source: http://community.livejournal.com/php/553672.html