[ SOLVED ] Tune In For.. Cross Database Queries...
Posted: Fri Jun 25, 2004 12:05 pm
The blazing obvious strikes me in the eye. Thanks!
-----------------------------------
So here's my tizzy.
Two mysql databases, the first one has a table called platform_generator, which holds the information for all of the cities in America, state, cityname, zip, etc. and an id.
The second has two tables, users and presence. A user can be present in more than one city -- presence has a cityguideid, which correlates to the id in the platform_generator table, and also has a user_id, which correlates to the user_id in users.
Given a state, I need to find all users who have "control" over cities in that state. Note that the tables are in TWO DIFFERENT databases. My boss doesn't want to justify copying them over, else this would be a cinch.
Here's my question: What's the best way to do this? Is there a way to do a cross-database query, or am I stuck with something like this:
My boss suggested that maybe querying once and putting the presence table into an array and then comparing the cityguides array with that array may be faster. I told him that's approximately what mySQL is doing anyhow and he somewhat agreed.
Note that platform_generator will return 2000 or so cities per state. This tool doesn't have to run en masse, but it's on a production server with other queries running all the time.
What do you all suppose I should do?
-----------------------------------
So here's my tizzy.
Two mysql databases, the first one has a table called platform_generator, which holds the information for all of the cities in America, state, cityname, zip, etc. and an id.
The second has two tables, users and presence. A user can be present in more than one city -- presence has a cityguideid, which correlates to the id in the platform_generator table, and also has a user_id, which correlates to the user_id in users.
Given a state, I need to find all users who have "control" over cities in that state. Note that the tables are in TWO DIFFERENT databases. My boss doesn't want to justify copying them over, else this would be a cinch.
Here's my question: What's the best way to do this? Is there a way to do a cross-database query, or am I stuck with something like this:
Code: Select all
mysql_select_db("cities");
$results = mysql_query("SELECT platform_generator_pk as cityguide_id FROM platform_generator WHERE state_code LIKE '%$state%' AND sub_url = '3'");
$x = 0;
while($row = mysql_fetch_row($result))
{
$cityguides[$x] = $row["cityguide_id"];
$x++;
}
mysql_select_db("bestof");
foreach($cityguides as $cityguide)
{
$result = mysql_query("SELECT repid FROM presence WHERE cityguideid = '$cityguide';");
$x = 0;
while($row = mysql_fetch_row($result)
{
$agent_ids[$x] = $row["repid"];
$x++;
}
}
//Root out to get only one rep_id even if assigned to multiple cities
//Query back to get city information for each repNote that platform_generator will return 2000 or so cities per state. This tool doesn't have to run en masse, but it's on a production server with other queries running all the time.
What do you all suppose I should do?