Selecting and joining across multiple databases

sublimenal

I have a problem here.

I have 3 databases that sit ont he same server, I can access the databases seperatly find but what I need to do is access all 3 at the same time. All 3 databases have the same schema. Is it possible to do this? Here is my setup:

my mysql username can access all databases but not together

My 3 databases: wcmporg_db , alife_db and puritan_db

Here was my thought process on this but it doesnt work

"SELECT * FROM puritan_db.main,wcmporg_db.main,alife_db.main  ORDER by main.burial_date desc  limit $offset,$limit"

Any help would be appreciated

NogDog

To the best of my knowledge, a single query can only interact with a single database. I'm not enough of a DB guru to know if there might be some way to use stored procedures and temporary tables to kludge something together, though.

sublimenal

jhmm There has to be a way, if not do you know another way i can take data from 3 databases and have the ability to sort it? I thought array but you can only sort one way

NogDog

Well...probably the "best" solution would be to merge the separate databases into a single database, since functionally speaking it appears you want to use them as one.

Outside of that, the only "not nearly as good solution" that I can come up with would be to to query each database and store the results in arrays, then use some queries to create some temporary tables (either under one of those existing databases or a separate one), then execute your query against those temporary tables. This could get pretty ugly performance-wise if the tables being queried have a lot of data that needs to be transferred from DB to PHP then back to DB (and back to PHP again after the final query).

sublimenal

Hmm well i cant make them one database because each database is a different website. But im makign another website which queries the info from all the sub websites and creates a master website. This is my code so far you think theres a way to put it into the array $list?


$query = $database->query("SELECT * FROM wcmporg_db.main ORDER by wcmporg_db.main.burial_date DESC  limit $offset,$limit" );
	$query = $database->query("SELECT * FROM puritan_db.main ORDER by puritan_db.main.burial_date DESC  limit $offset,$limit" );
	$query = $database->query("SELECT * FROM alife_db.main ORDER by alife_db.main.burial_date DESC  limit $offset,$limit" );
	while ($list = mysql_fetch_array($query)) {
		$id = $list['id'];
		$first_name = stripslashes($list['first_name']);
		$middle = stripslashes($list['middle']);
		$last_name = stripslashes($list['last_name']);
		$obituary = stripslashes($list['obituary']);
		$photo = stripslashes($list['photo']);
		$section = $list['section'];
		$vet = $list['vet'];
		}

NogDog

sublimenal wrote:
Hmm well i cant make them one database because each database is a different website....

Why not? If you are worried about site owners somehow accessing the data of others, you can prevent that by setting up different database user accounts for each site that only have permissions to access the tables for their sites. Then your mutli-site reporting application can use a separate account with permissions to acess all of the tables.