Query results from multiple database servers

[yaea04]

Well, wouldn’t you know it… our data lives on two different MySQL servers (different hostname, db, username, password). Let’s call them dbserver1 and dbserver2.

From our user interface, we desire a single INPUT field that will display search results from several of our database tables that live on both dbserver1 and dbserver2.

I know how to connect to two different database servers at the same time. I do it like this.

$connection1 = mysql_connect($dbserver1, $user1, $password1) or die ("Unable to connect!");
mysql_select_db($db1, $connection1) or die ("Unable to select database!");

$connection2 = mysql_connect($dbserver2, $user2, $password2) or die ("Unable to connect!");
mysql_select_db($db2, $connection2) or die ("Unable to select database!");

I also know how to Query across multiple database tables one the SAME server using one Query statement. I can do it using a UNION statement like this:

$query = "(SELECT id AS union_id, field07 AS union_title FROM table01 WHERE field07 REGEXP '$searchtext') UNION 
(SELECT id AS union_id, field55 AS union_title FROM table05 WHERE field55 REGEXP '$searchtext') ORDER BY union_title ASC";

Note that since the various tables might use different field names, I also use the “AS” parameter to bring them all into conformity. This way I can refer to them as “union_title” regardless of which table they came from. But that’s not the issue here.

Also note that the trailing ORDER statement will fling the combined results around and produce a sorted list regardless of which database table the data came from. To the user, it looks like one list of stuff, and he doesn’t need to know it came from multiple sources.

What I have now is a simple, handy way that a user can enter some search text, and see results from multiple database tables.

But now…

Now I need to include tables from a separate database server in the Query. I’ve tried several things without success.

I suspect I want to perform a UNION query that looks at various tables that live on both servers. Maybe I prefix the Query parameters with the unique $dbserver.$db info? If so, how to I get the $connection2 part to be specified?

Remember, a key point here might be that the end result is a single, sorted list of results. Hence I think I need to retain the single Query statement?

Wheww.... Does any of this make sense? Does anyone have any suggestions or pointers?
Thanks.

[Sxooter]

I don't think there's a decent way to do this, and even if there is, once you're dealig with large datasets, the performance will be abysmal.

You need these data sets on the same machine, preferable in the same db instance as well for performance reasons.

I'd suggest using some kind of replication to get the data from one machine to another. It's all gotta get transferred over for the union eventually anyway, may as well move it over in the machine's spare time instead of waiting tapping your fingers while it does it each time you do the query.

[mercrapper]

I don't think you can do that using SQL because you will have 2 different DB connection strings. I am no aware of a way to run a single query that uses 2 different db connections.

What you will have to do is query each database seperately, then put them together in you php script.

The tricky part is geting them sorted properly (mixing the results of both database queries).

[Sxooter]

The poor man's way of doing this, by the way, is to query the db with the smaller set, and then use another connection to insert those rows into the second db, and then do the work in the second db.

Any chance you can get those two sets of data put on one machine, where they belong?

[yaea04]

Thanks for the replies.

I don't think the Airlines, the Hotels or the Government keep all their data on one database server just so they can query it. There must be a way, eh?

Anyway. I agree that I should look for ways to replicate or relocate some of my data, to facilitate my Queries.

With that in mind... three separate question pop into my head.

1) I'm having trouble creating a good Cron job to replicate the desired tables. Any tips?

2) If I perform two separate Queries instead (as suggested), how do I combine the results after the fact? Some kind of array?

3) If I drink more coffee, will all this be easier?

[yaea04]

With this code, I can Query two database servers in order.

First I connect to both database servers.
Then I Query the FIRST database server; putting the results in an array "row1".
Then I Query the SECOND database server; putting the results in an array "row2".

Note the use of the "connection#" in the mysql_query statement that tells it which connection to use.

Now if I could combine the two arrays into one, then RE-SORT it again. Any suggestions how to do this?

// open database connection
$connection1 = mysql_connect($host1, $user1, $pass1) or die ("Unable to connect!");
$connection2 = mysql_connect($host2, $user2, $pass2) or die ("Unable to connect!");

// open database connection
mysql_select_db($db1, $connection1) or die ("Enable to select database!");
mysql_select_db($db2, $connection2) or die ("Unable to select database!");



// Query the FIRST database server
$query1 = "SELECT * FROM table73 WHERE country = '6' ORDER BY title ASC";
$result1 = mysql_query($query1, [B]$connection1[/B]) or die ("Error in query: $query1. " . mysql_error());

// if a result is returned
if (mysql_num_rows($result1) > 0)
{

// turn it into an object
$row1 = mysql_fetch_object($result1);

echo $row1->title;

}

// at this point, the row1 array contains results from the FIRST database server



// Query the SECOND database server
$query2 = "SELECT * FROM table28 WHERE product_type = '4' ORDER BY title ASC";
$result2 = mysql_query($query2, [B]$connection2[/B]) or die ("Error in query: $query2. " . mysql_error());

// if a result is returned
if (mysql_num_rows($result2) > 0)
{

// turn it into an object
$row2 = mysql_fetch_object($result2);

echo $row2->title;

}

// at this point, the row2 array contains results from the SECOND database server


// ====== Close database connection
mysql_close($connection1);
mysql_close($connection2);

[Sxooter]

Seeing as I work as a DBA at an airline reservation company, I can tell you that when you go to some place like orbitz or travelocity, you are, in fact, hitting one great big database on the backend.

Now, that database is fed by several other databases, but when the actual search is done, it's done on one big database. And for good reason, the performance is crap if you're trying to mush two discrete databases against one another.

About moving the data about...
Not sure why a cron job to move the data you need should be tough. Are you having a problem using cron in general, or with a script that moves the data you need moved about? I'd start first just getting some kind of script that moves the small bits in the database from one to the other, i.e. the stuff you'd index and search on.

The problem with performing two separate queries, as you were thinking, is that you have to transfer all the data anyway, right? So, every time you want to run this query, you've got to grab two huge datasets at the same time, and then try and match them up with PHP. A database is built to do these things in its sleep. With PHP, or any other language, you have to now pay close attention to how you're joining those data up, and make sure not to run the machine out of memory or disk space while you do it.

So, rather than running two queries, it might actually be more efficient to run one query on the set likely to return the least amount of data (the most selective query) and then use that to make some kind of in() list or a temp table in the other database, and grab the data from the second database with that.

Not sure coffee will help. I find it does, but only when I'm working late. Just don't get too fussed up on it. And be ready to throw a try or two away.

[Sxooter]

There's a whole bunch of array manipulation functions built into PHP. It's one of its best features.
Are these basically the same data formats and all, i.e. no real difference between them but that they're in different places? then you should be able to use the built in array functions to do that. Look through them, there's plenty there, and the names are pretty obvious things, like array_merge, array_intersect, array_sort, and so on. Lots of them, some sort on the key and keep them, some on the value and reorder the keys, some on the value and keep the keys, etc...

[Roger_Ramjet]

Interesting problem. There are several ways you can resolve this depending on your technology.

Some dbs such as oracle and ms sql can handle 'pass through' queries to extract data from multiple servers; you query one server and it in turn connects to and queries another server. This is not a feature of mysql though, so far as I know anyway.

Some db engines even allow for the linking of tables in dbs on other servers, effectively creating one large db spread over several servers. Again not a feature of mysql. The nearest you can get to that, and a whole lot further in another direction, is mysql cluster . The whole lot further is into load balancing and fail-safe data storage.

To do it in code there are many array functions to use. You can push the resultsets into one array and then sort the array as you choose. Likely to be slow if you have large numbers of results returned but the best option unless you can reconfigure your servers for clustering or master/slave replication.

[Sxooter]

Yeah, I'm wondering how big the data sets you're talking about here are, and how big they're likely to get. Also, how often is this query page going to need to run? Most default installs of php can handle a few million very small simple elements in an array. And generally, if you reconfigure it to have a higher maximum memory than the default 8 megs per child, the performance isn't gonna scale as the numbers get bigger and bigger.

Note that PostgreSQL also has the ability to hit one server from another, using the contrib module called dblink.

But if you're mushing two multi-million row datasets against each other, then you need to have them on the same database server, either by replication or something like it, or the performance is going to really suffer.

[mercrapper]

As a matter of fact, I am planning to do this in the near future.
I have written a prototype that connects to 2 seperate databases (on different servers).
I use ADODB so it doesn't matter what type of database it is (I have tested using MySQL and Oracle). I also found that performance was rather bad (at least in terms of waiting for a page to load). Then I thought...wouldn't it be nice to get the page to start loading before all of the results have been retrieved?

So what I did was flush the output buffer every x number of recrords retrieved. This gived the end user the illusion that the query only took 1-2 seconds instead of the 30-40 seconds. This does put a little more load on the server, but the illusion of performance increase is significant for the load cost (IMHO). It also uses less memory as you don't need to store as information in the arrays.

Here's how I did it...
1. Turn off mod_gzip
2. Output and flush the header of the page (anything before the data starts)
2. Connection to both databases
3. Query both databases
4. Retreive X number of records from each database into an array (the same array)
5. Sort the array by in the say I want it to display
6. Output (and remove) the first X number of array items from the array
7. Repeat steps 4-7 until there are no more records to get from any of the databases
8. Output the rest of the items on the array (if any).
9. Output the end of page (anything after the data)

Things to note :
1. If you are using a table in IE, each time you want IE to render the table, you need to close the table.
2. Use ob_flush and flush to push the output to the browser.

[yaea04]

I had little time to work on this, but here is my progress.

Our enterprise uses Database Server 1 for some things, and Database Server 2 for other things. These two servers are located in different parts of the world. Upcoming needs require Querying across both. Remembering that barriers in any enterprise are 90% human, organizational and cultural, and only 10% technical, we must live with two database servers until all parties put on their gloves and bash it out under the lights.

Meanwhile, I have a need to implement this cross-server Query as an interim solution.

Also meanwhile, I am learning about Cron jobs, and ways that I can replicate as Sxooter suggested. Any tips here would be appreciated.

Here is a rough picture:

Server1 has 10 tables on it
- Eg: Table1 = 500 records, 20 columns
- Eg: Table2 = 10000 records, 20 columns
- Eg: Table3 = 100 records, 20 columns
- For the purposes of this Query, we are only interested in looking in about 3 of the fields in any of these tables.

Server2 has 2 tables on it
- Eg: Table1 = 5000 records, 4 columns
- Eg: Table2 = 1000 records, 4 columns
- For the purposes of this Query, we are only interested in looking in about 3 of the fields in any of these tables.

The user interface will probably be a simple INPUT field that will perform a text search (maybe using REGEXP as shown) across all 12 tables (2 of which live on a different database server). We are only interested in looking for a text match on about 3 of the fields in each table.

Here is my progress:

The code segments shown below are edited (I have to protect our company secrets, you know).

I can connect to both database servers at the same time just fine.

// open database connection
$connection1 = mysql_connect($host1, $user1, $pass1) or die ("Unable to connect to: $host1 ");
$connection2 = mysql_connect($host2, $user2, $pass2) or die ("Unable to connect to: $host2 ");

// open database connection
mysql_select_db($db1, [COLOR=Blue]$connection1[/COLOR]) or die ("Unable to select database: $db1 ");
mysql_select_db($db2, [COLOR=Blue]$connection2[/COLOR]) or die ("Unable to select database: $db2 ");

First, I perform a Query on a Table that lives on Server1. Then a second Query for a Table that lives on Server2. This currently uses two separate Query statements.

// Query the FIRST database server
$query1 = "SELECT id_m AS union_id, lastname AS union_field1, firstname AS union_field2, company AS union_field3 FROM $table1 WHERE lastname REGEXP '$searchtext' OR company REGEXP '$searchtext' ";
$result1 = mysql_query($query1, [COLOR=Blue]$connection1[/COLOR]) or die ("Error in query: $query1. " . mysql_error());

// if records present
if (mysql_num_rows($result1) > 0)
{

// read each result record into an array
for($i=1; $i<=mysql_num_rows($result1); $i++)
{
$a = mysql_fetch_row($result1);
echo '<font color=red>', $a[0], ': ', $a[1], ', ', $a[2], ' from ', $a[3], '</font><br>';
}

}

echo '<font color=blue>Result1 count = ', count($a), '</font><br>'; 
// at this point, the $a array contains results from the FIRST database server




// ==========================================================================================================
// Query the SECOND database server
$query2 = "SELECT id_m AS union_id, lastname AS union_field1, firstname AS union_field2, company AS union_field3 FROM $table77 WHERE lastname REGEXP '$searchtext' OR company REGEXP '$searchtext' ";
$result2 = mysql_query($query2, [COLOR=Blue]$connection2[/COLOR]) or die ("Error in query: $query2. " . mysql_error());

// if records present
if (mysql_num_rows($result2) > 0)
{

// read each result record into an array
for($i=1; $i<=mysql_num_rows($result2); $i++)
{
$b = mysql_fetch_row($result2);
echo '<font color=red>', $b[0], ': ', $b[1], ', ', $b[2], ' from ', $b[3], '</font><br>';
}

}

echo '<font color=blue>Result2 count = ', count($b), '</font><br>'; 
// at this point, the $b array contains results from the SECOND database server

The I do an “array_merge” statement. Then a “asort” statement to re-sort the newly combined results from Server1 and Server2 into one single, happy array.

$c = array_merge($a, $b);
echo '<font color=blue>Merged count = ', count($c), '</font><br>'; 
asort($c, union_id); // does this actually sort on the union_id column?

I also "echo" (as seen in the code segments above) certain things to monitor my progress.

From looking at my echo statements, the REGEXP properly returns records that match the text-search criteria.
Eg: 14 results from Server1, and 7 results from Server2.
Results from Server1 seem to be properly in the 4-column, x-row Array called $a.
Results from Server2 seem to be properly in the 4-column, n-row Array called $b.

By the way, the overall response time seems acceptable for now.

But today, due to lack of stimulation, I seem to get lost.

I’m not 100% sure about the way I merge and re-sort the Arrays. Am I doing it correctly? You would probably think something like this would be obvious for someone posting a message in this Forum, wouldn’t you?

I did indeed look at the info on php.net about “array_merge” and “asort”, and was able to at least make some progress.

But for some reason I can not seem to properly handle the contents of my merged, re-sorted Array $c. I’m sure it’s straightforward, and I’m just overlooking something.

So, I need to validate that my “merge” and “re-sort” actually work.
Then I would like to display the contents of the resulting Array.
Can anyone give tips on how to handle my post-merge and post-sorted Array?

Meanwhile… yes… I will work on replicating or relocating all this to one database server. I'll also continue learning how to manipulate Arrays.

Does this approach make any sense? Any tips?

Before I close,

// ====== Close database connection
mysql_close($connection1);
mysql_close($connection2);

... I will just say that once all of this works, this little code segment might be a handy thing to keep in our back pocket for future use.

[Sxooter]

look up the implode, array_keys, and array_values functions. You can do some very neat things like:

print implode(":",array_keys($array));

with them.

[mercrapper]

Look at the notes of the array_multisort.
There is a function that somebody posted that may be of use (array_csort).

[yaea04]

Many thanks to everyone who has helped me.

Here is my current progress.

This now seems to work. Edited code segments shown below.

Remember the original goal. Our enterprise data lives in multiple tables on more than one MySQL database server. The servers are located in different parts of the world. We desire a simple form to allow the user to do a text search for certain things across these multiple tables/servers. The results would be displayed in a single, sorted list, regardless of the database table/server they came from.

As mentioned before, the best solution would be to replicate/relocate the data so it lives on one server. While we work on that, we still have the need to perform the cross-server solution.

In the code segment below, we Query 11 tables from server #1 and 2 Tables from server #2.

I can connected to two database servers at the same time (see code segment in previous post).

I can Query multiple Tables (on one database server at a time). I use the UNION statement to include additional Tables from that same database server. Since the source Tables all use different column names, I also use the “AS” statement in the SELECT to make sure that the differently-named columns names are all put into one consistent name.

// generate query for the FIRST database server
$query1 = "";
$query1 .= "(SELECT id AS union_id, 	term AS union_title, 		definition AS union_description, 	'type01' AS union_type, acronym_en AS union_second 	FROM $dbtable_accounts 	WHERE object_status!='Deleted' AND (term REGEXP '$searchtext' OR definition_en REGEXP '$searchtext') ) "; 
$query1 .= " UNION (SELECT id AS union_id, 	term AS union_title, 		definition AS union_description, 	'type02' AS union_type, acronym_en AS union_second	FROM $dbtable_rivers 	WHERE object_status!='Deleted' AND (term REGEXP '$searchtext' OR definition_en REGEXP '$searchtext') ) "; 
$query1 .= " UNION (SELECT id_k AS union_id, 	title AS union_title, 		description AS union_description, 	'type03' AS union_type, '0000' AS union_second		FROM $dbtable_news 	WHERE kcategory='News' AND utype!='00200' AND (title REGEXP '$searchtext' OR description REGEXP '$searchtext') ) "; 
$query1 .= " UNION (SELECT id_k AS union_id, 	title AS union_title, 		description AS union_description, 	'type04' AS union_type, '0000' AS union_second		FROM $dbtable_countries WHERE object_status='OK' AND ptype!='00200' AND (title REGEXP '$searchtext' OR description REGEXP '$searchtext') ) "; 
$query1 .= " UNION (SELECT id_k AS union_id, 	title AS union_title, 		description AS union_description, 	'type05' AS union_type,	'0000' AS union_second		FROM $dbtable_nodes 	WHERE object_status='ALive' AND ptype='00200' AND (title REGEXP '$searchtext' OR description REGEXP '$searchtext') ) "; 
$query1 .= " UNION (SELECT id_m AS union_id, 	lastname AS union_title, 	company AS union_description, 	'type06' AS union_type, firstname AS union_second	FROM $dbtable_contacts 	WHERE person_status='7EE'  AND (lastname REGEXP '$searchtext' OR firstname REGEXP '$searchtext' OR company REGEXP '$searchtext') ) "; 
$query1 .= " UNION (SELECT id_m AS union_id, 	lastname AS union_title, 	company AS union_description, 	'type07' AS union_type,	firstname AS union_second	FROM $dbtable_employees	WHERE staff_status='D045' AND (lastname REGEXP '$searchtext' OR firstname REGEXP '$searchtext' OR company REGEXP '$searchtext') ) "; 
$query1 .= " UNION (SELECT id AS union_id, 	brane_type AS union_title, 	spin AS union_description, 		'type08' AS union_type, branes AS union_second		FROM $dbtable_particles WHERE brane_type REGEXP '$searchtext' OR comment_en REGEXP '$searchtext') "; 
$query1 .= " UNION (SELECT id AS union_id, 	title AS union_title, 		description AS union_description, 	'type09' AS union_type, '0000' AS union_second		FROM $dbtable_orders 	WHERE title REGEXP '$searchtext' OR description REGEXP '$searchtext') "; 
$query1 .= " UNION (SELECT idj AS union_id, 	title_en AS union_title, 	punchln AS union_description, 	'type10' AS union_type,	funny-rate AS union_second	FROM $dbtable_jokes 	WHERE title_en REGEXP '$searchtext' OR comment_en REGEXP '$searchtext') "; 
$query1 .= " UNION (SELECT idf AS union_id, 	title_en AS union_title, 	philum AS union_description, 		'type11' AS union_type,	'0000' AS union_second		FROM $dbtable_fish 	WHERE title_en REGEXP '$searchtext' OR comment_en REGEXP '$searchtext') "; 

// echo '<br>DEBUG TEST Q1 = <font color=red>', $query1, '</font><br>';
// perform the Query on database server #1
$result1 = mysql_query($query1, $connection1) or die ("SEARCH_QUERY for Connection #1 - Error in query: $query1. " . mysql_error());



// generate query for the SECOND database server
$query2 = "";
// I still have a problem when I UNION two Queries from dbserver#2.  I'll look at that later.  So it's commented out for now, and I only look at ONE table on dbserver#2.
$query2 .= "SELECT idProduct AS union_id, ProductName AS union_title, ProductDescription AS union_description, 'type12' AS union_type, '0000' AS union_second FROM $apples WHERE ProductName REGEXP '$searchtext' OR ProductDescription REGEXP '$searchtext' "; 
// $query2 .= "(SELECT idApple AS union_id, fruitname AS union_title, fruitdescription AS union_description, 'type12' AS union_type, '0000' AS union_second FROM $fruitlist WHERE fruitname REGEXP '$searchtext' OR fruitdescription REGEXP '$searchtext' ) "; 
// $query2 .= " UNION (SELECT idAnimal AS union_id, animalname AS union_title, animaldescription AS union_description, 'type13' AS union_type, '0000' AS union_second FROM $animallist WHERE animalname REGEXP '$searchtext' OR treedescription REGEXP '$searchtext' ) "; 

// echo '<br>DEBUG TEST Q2 = <font color=red>', $query2, '</font><br>';
// perform the Query on database server #2
$result2 = mysql_query($query2, $connection2) or die ("SEARCH_QUERY for Connection #2 - Error in query: $query2. " . mysql_error());

Then I iterate through the Results, from database server#1, then database server #2, and try to put things in the correct Array location.

// Iterate through Result set #1
// Fling the data around and try to put it in the correct array location
if (mysql_num_rows($result1) > 0)
{
	for($i=0; $i<=mysql_num_rows($result1)-1; $i++)
	{
		$a = mysql_fetch_row($result1);
		$myArray1[$i]['union_id'] = $a[0];
		$myArray1[$i]['union_title'] = $a[1];
		$myArray1[$i]['union_description'] = $a[2];
		$myArray1[$i]['union_type'] = $a[3];
		$myArray1[$i]['union_second'] = $a[4];
	}
}

// Iterate through Result set #2
// Fling the data around and try to put it in the correct array location
if (mysql_num_rows($result2) > 0)
{
	for($i=0; $i<=mysql_num_rows($result2)-1; $i++)
	{
		$b = mysql_fetch_row($result2);
		$myArray2[$i]['union_id'] = $b[0];
		$myArray2[$i]['union_title'] = $b[1];
		$myArray2[$i]['union_description'] = $b[2];
		$myArray2[$i]['union_type'] = $b[3];
		$myArray2[$i]['union_second'] = $b[4];
	}
}

Next I merge the two Query results into one Array, and use the handy csort function (mentioned in previous post) to re-sort the final result Array.

function csort($array, $column) {
$s = array();
foreach($array as $row) {
$s[] = $row[$column];
}
array_multisort($s, SORT_ASC, $array);
return $array;

}

// merge the two Result arrays
$finalArray = array_merge($myArray1, $myArray2);

// re-sort the newly combined array by Title
$finalArray = csort( $finalArray, 'union_title');

// count how many results were returned
$temp_count = count($finalArray);
echo '<b>', $temp_count, '</b> results found.';

Then I iterate through and display the final result Array, in one combined list from both database servers, sorted by Title. The user doesn't care where the data comes from. It is displayed in one happy, sorted list.

$rowstyle = 1;

for($i=0; $i<=$temp_count-1; $i++)
{

// Set the existing variables called "row" to the values from the Array
// I'm not smart enough to do this the easy way, so I brute force it
$row->union_id = 		$finalArray[$i]['union_id'];
$row->union_title = 	$finalArray[$i]['union_title'];
$row->union_description = $finalArray[$i]['union_description'];
$row->union_type = 	$finalArray[$i]['union_type'];
$row->union_second = 	$finalArray[$i]['union_second'];

?>
<tr><td width="100%" class="row<? echo $rowstyle; ?>">
<table border="0" width="100%" cellpadding="0" cellspacing="0">
<tr>
<td width="100%">

<?
echo '<span class="textblock_light">Container Type: ', $row->union_type, '</span><br>';
$url_view = "view.php?id=".$row->union_id;
echo '<a href="',$url_view,'"><b>', $row->union_title, '</b></a>';
echo '<br>', text_trim($row->union_description, 200); 
?>

</td>
</tr>
</table>
</td></tr>

<?
$rowstyle++;
if ($rowstyle > 2) { $rowstyle = 1; }

} // End of FOR LOOP

This approach works, and performance is acceptable.

However I am still concerned about my neanderthal style code and brute force methods. I am sure there is a more elegant way. Better solutions are quite welcome, and I would like to invite you to help improve this code.

Meanwhile, thank you very much to those who contributed to this thread. All of your postings and ideas led me down various interesting paths. I learned a lot from you.

[Roger_Ramjet]

Well done, glad to see you carcked it. Just one point, in a UNION query the column names (or aliases) of the first select are used throughout all other selects so you don't need to keep writing those aliases because they are ignored anyway.

[Roger_Ramjet]

On a longer point, and I know that you said performance is acceptable, you are processing the data 3 times: 1 to extract it into arrays, 2 merge the arrays, 3 sort the final array. Not very efficient.

Personally, I would extract sort and merge simultaneously.

You would begin by sorting the individual resultsets in the query itself. You do this by encloseing the individual selects in brackets, as you have, and adding an order by at the end.

$query1 = "";
$query1 .= "(SELECT id AS union_id, 	term AS union_title, 		definition AS union_description, 	'type01' AS union_type, acronym_en AS union_second 	FROM $dbtable_accounts 	WHERE object_status!='Deleted' AND (term REGEXP '$searchtext' OR definition_en REGEXP '$searchtext') ) "; 
$query1 .= " UNION (SELECT id AS union_id, 	term AS union_title, 		definition AS union_description, 	'type02' AS union_type, acronym_en AS union_second	FROM $dbtable_rivers 	WHERE object_status!='Deleted' AND (term REGEXP '$searchtext' OR definition_en REGEXP '$searchtext') ) "; 
$query1 .= " UNION (SELECT id_k AS union_id, 	title AS union_title, 		description AS union_description, 	'type03' AS union_type, '0000' AS union_second		FROM $dbtable_news 	WHERE kcategory='News' AND utype!='00200' AND (title REGEXP '$searchtext' OR description REGEXP '$searchtext') ) "; 
$query1 .= " UNION (SELECT id_k AS union_id, 	title AS union_title, 		description AS union_description, 	'type04' AS union_type, '0000' AS union_second		FROM $dbtable_countries WHERE object_status='OK' AND ptype!='00200' AND (title REGEXP '$searchtext' OR description REGEXP '$searchtext') ) "; 
$query1 .= " UNION (SELECT id_k AS union_id, 	title AS union_title, 		description AS union_description, 	'type05' AS union_type,	'0000' AS union_second		FROM $dbtable_nodes 	WHERE object_status='ALive' AND ptype='00200' AND (title REGEXP '$searchtext' OR description REGEXP '$searchtext') ) "; 
$query1 .= " UNION (SELECT id_m AS union_id, 	lastname AS union_title, 	company AS union_description, 	'type06' AS union_type, firstname AS union_second	FROM $dbtable_contacts 	WHERE person_status='7EE'  AND (lastname REGEXP '$searchtext' OR firstname REGEXP '$searchtext' OR company REGEXP '$searchtext') ) "; 
$query1 .= " UNION (SELECT id_m AS union_id, 	lastname AS union_title, 	company AS union_description, 	'type07' AS union_type,	firstname AS union_second	FROM $dbtable_employees	WHERE staff_status='D045' AND (lastname REGEXP '$searchtext' OR firstname REGEXP '$searchtext' OR company REGEXP '$searchtext') ) "; 
$query1 .= " UNION (SELECT id AS union_id, 	brane_type AS union_title, 	spin AS union_description, 		'type08' AS union_type, branes AS union_second		FROM $dbtable_particles WHERE brane_type REGEXP '$searchtext' OR comment_en REGEXP '$searchtext') "; 
$query1 .= " UNION (SELECT id AS union_id, 	title AS union_title, 		description AS union_description, 	'type09' AS union_type, '0000' AS union_second		FROM $dbtable_orders 	WHERE title REGEXP '$searchtext' OR description REGEXP '$searchtext') "; 
$query1 .= " UNION (SELECT idj AS union_id, 	title_en AS union_title, 	punchln AS union_description, 	'type10' AS union_type,	funny-rate AS union_second	FROM $dbtable_jokes 	WHERE title_en REGEXP '$searchtext' OR comment_en REGEXP '$searchtext') "; 
$query1 .= " UNION (SELECT idf AS union_id, 	title_en AS union_title, 	philum AS union_description, 		'type11' AS union_type,	'0000' AS union_second		FROM $dbtable_fish 	WHERE title_en REGEXP '$searchtext' OR comment_en REGEXP '$searchtext') ";
$query1 .= " ORDER BY union_title"; 

Do the same for query2.

Now you have 2 correctly sorted resultsets that need to be merged. A simple set of functions will do this for you as you extract the query results.

// set up sort and merge
$row1 = mysql_fetch_array($result1);
$row2 = mysql_fetch_array($result2);
$merged = array();

// compare rows from each resultset untill one is finished
while ($row1 && $row2) {
   if ($row1['union_title'] <= $row2['union_title']) {
      $merged[] = $row1;
      $row1 = mysql_fetch_array($result1);
   } else {
      $merged[] = $row2;
      $row2 = mysql_fetch_array($result2);
   }
}

// now find out which resultset is not finished and grab the rest of it
if ($row1) {
   $merged[] = $row1;
   while ($row=mysql_fetch_array($result1)) {
       $merged[] = $row;
   }
} elseif ($row2) {
   $merged[] = $row2;
   while ($row=mysql_fetch_array($result2)) {
       $merged[] = $row;
   }
}

There you are, all results fetched, sorted and merged in one operation. I don't know if it is more efficient really since php is pretty smart, but I think it is more elegent.

[mercrapper]

You can also fetch all of your results (from both servers) into the same array and then just run the csort function.