[ SOLVED ] Tune In For.. Cross Database Queries...

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
bcox
Forum Newbie
Posts: 5
Joined: Fri Jun 25, 2004 12:05 pm
Location: Mission, KS

[ SOLVED ] Tune In For.. Cross Database Queries...

Post by bcox »

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:

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 rep
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?
Last edited by bcox on Mon Jun 28, 2004 10:02 am, edited 1 time in total.
Illusionist
Forum Regular
Posts: 903
Joined: Mon Jan 12, 2004 9:32 pm

Post by Illusionist »

I think it might be possible to add the database name infront of the table you wish to query....

Code: Select all

$sql = "SELECT * FROM cities.platform_generator as c, bestof.presence as b WHERE c.state_code LIKE '%$state%' AND c.sub_url = '3' AND b.cityguideid = c.platform_generator_pk";
or if you just want the repid:

Code: Select all

$sql = "SELECT b.repid FROM cities.platform_generator as c, bestof.presence as b WHERE c.state_code LIKE '%$state%' AND c.sub_url = '3' AND b.cityguideid = c.platform_generator_pk";
I think that may work, I didn't test that exact code, but i tried somethign similar on my localhost with select from two databses and it worked, so give it a try. It may need a little tweaking though.
lostboy
Forum Contributor
Posts: 329
Joined: Mon Dec 30, 2002 8:12 pm
Location: toronto,canada

Post by lostboy »

It IS possible to place the db name in front of the table names for the queries. The caveat is that both dbs must exist on the same machine, AFAIK
Illusionist
Forum Regular
Posts: 903
Joined: Mon Jan 12, 2004 9:32 pm

Post by Illusionist »

uhh... well yeah... That's a given.
Post Reply